Creating tables with SQL
This is part 2 of a four-part article which explains SQL
In the example that follows, three very simple tables are created and content is added to them.
The three tables schema depicted in the image above is created and populated.
DDL for creating the tables
Data Definition Language is the subset of SQL which is used for creating, altering and dropping elements in a DB schema:
- CREATE: creates databases, tables, views, indexes
- ALTER: modifies existing object in a DB
- DROP: destroys DB objects
Here are the statements for creating the tables:
Products
CREATE TABLE products (
id INTEGER NOT NULL,
name VARCHAR(50),
PRIMARY KEY (id)
);
- This table holds the products to be sold
- Notice the names, types and constraints
- id is NOT NULL, that is, it doesn't admit NULL values
- name is of type VARCHAR(50), which means it admits up to 50 characters, and the length is not fixed, and it admits NULL values
- Besides, the PRIMARY KEY constraint identifies this column as the one that can't be repeated in the table
- In fact, the NOT NULL isn't strictly required, as PKs do not allow NULL VALUES
Sellers
CREATE TABLE sellers (
id INTEGER NOT NULL,
name VARCHAR(50),
PRIMARY KEY (id)
);
- The list of employees that sell
products
- Like
products
, id is the Primary Key, which assures there are no more than one row with the same values
Sales
CREATE TABLE sales (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
id_product INTEGER NOT NULL,
id_seller INTEGER NOT NULL,
date DATE NOT NULL,
quantity INTEGER NOT NULL,
amount REAL NOT NULL,
FOREIGN KEY (id_product) REFERENCES products (id),
FOREIGN KEY (id_seller) REFERENCES sellers (id)
);
- Here is the
sales
table, which is related withproducts
andsellers
- The PK is also named id, but notice that it in this case, there is the AUTOINCREMENT keyword; this is the SQLite syntax, AUTO_INCREMENT for MySQL and SERIAL for PostgreSQL, it means that when creating new rows in this table, the DB will automatically populate it with consecutive numbers
- Importance to notice here are the FOREIGN KEY clauses:
- The first one links the possible values in
sales
with the values in the PK forproducts
- The other does the same with table
sellers
- The first one links the possible values in
- What does it mean?
- Values for
id_product
andid_seller
could not be any number; they have to match with existing rows inproducts
andsellers
- Besides, this means that rows in the tables
products
andsellers
cannot be deleted if at least one row insales
point to them ... unless the DDL were different: - REFERENCES ... ON DELETE CASCADE ... ON UPDATE CASCADE
-
ON DELETE CASCADE
would mean that removing a row in the referenced table, would cascade to delete it in this table too -
ON UPDATE CASCADE
would mean that if the PK would be changed in the reference table(s), this would cascade the update to this table - As these are not the case, these clauses are not added to the
CREATE TABLE
sentence
- Values for
With these SQL DDL clauses, the tables will be created and:
- Proper names and data types will be established
- Proper constraints, which will enforce the natural rules which should always be enforced in the DB, such as:
- NOT NULL: NULL values are not permitted in those attributes, or columns
- A
NULL
value does not mean zero, empty string, nor other type of 'natural', so to speak, empty values - It means that for a combination of row and column (in Math, it would be a tuple and an attribute) there is no value present, and in SQL it's treated differently
- PRIMARY KEY: Does not allow duplicate rows
- Remember that Sets, in math, do not allow duplicate elements in it
- For a Table to be a perfect Set, there should not be any duplicate element, and each element in this Set is a tuple
- FOREIGN KEY: Prevents related table to hold invalid values
DML for inserting rows into the tables
Data Manipulation Language is the SQL subset used for modifying the contents of tables in a DB:
- INSERT: inserts new rows into tables 1
- UPDATE: modifies values in existing rows
- DELETE: removes rows
- SELECT: retrieves content from the DB
Here are the statements for inserting rows into the tables created above in the DDL.
Products
INSERT INTO products (id, name) VALUES
(1, 'Sport shoes A'),
(2, 'Sport watch B'),
(3, 'Suit C');
Sellers
INSERT INTO sellers (id, name) VALUES
(234, 'John S.'),
(281, 'Luisa G.'),
(341, 'Mary T.');
Sales
INSERT INTO sales
(id_product, id_seller, date, quantity, amount)
VALUES
(1, 234, '2020-01-23', 2, 142.38),
(2, 234, '2020-02-01', 4, 813.34),
(2, 341, '2020-01-31', 1, 215.48),
(1, 234, '2020-03-01', 1, 75.00),
(3, 341, '2020-02-15', 1, 348.50),
(1, 341, '2020-01-17', 3, 210.00),
(3, 281, '2020-04-15', 1, 350.00),
(2, 281, '2020-02-28', 5, 1000.00),
(3, 281, '2020-05-13', 2, 605.25),
(1, 234, '2020-06-10', 2, 148.34),
(2, 341, '2020-12-01', 2, 448.50),
(2, 234, '2020-12-25', 1, 220.00),
(3, 341, '2020-11-18', 2, 600.00);
- Since the
id
in this table is auto incremental, there is no need to include that column in theINSERT
above - There are other ways to generate unique default values for a column
- In particular, and in a syntax that varies according to the DBMS engine used, an auto-generated UUID may be used
1 Tables and updatable views in DB engines that support them.
Top comments (0)