Lesson 10 of 12

Creating tables and constraints

When you design a new system, tables come first. Each column has a data type, and constraints protect the data from mistakes.

whole number	INT
exact decimal (money)	DECIMAL(10,2)
variable-length text	VARCHAR(100)
date	DATE

SQL

CREATE TABLE suppliers (
  supplier_id INT PRIMARY KEY,
  name        VARCHAR(80) NOT NULL,
  email       VARCHAR(120) UNIQUE,
  country     VARCHAR(50) DEFAULT 'Saudi Arabia'
);

NOT NULL forbids empty values, UNIQUE forbids duplicates and DEFAULT sets a default. A foreign key links to another table:

SQL

CREATE TABLE supplier_products (
  supplier_id INT,
  product_id  INT,
  cost        DECIMAL(10,2),
  PRIMARY KEY (supplier_id, product_id),
  FOREIGN KEY (supplier_id) REFERENCES suppliers(supplier_id),
  FOREIGN KEY (product_id)  REFERENCES products(product_id)
);

In MySQLIn MySQL an auto-numbered key is id INT AUTO_INCREMENT PRIMARY KEY. The editor here (SQLite) uses INTEGER PRIMARY KEY AUTOINCREMENT. MySQL tables should use ENGINE=InnoDB so foreign keys are enforced.

Creating tables and constraints

Exercises