CREATE TABLE and constraints
Make a new table and set the rules its data must follow.
Syntax
CREATE TABLE name (column type [PRIMARY KEY] [NOT NULL] [DEFAULT value] [REFERENCES other (column)]);
- PRIMARY KEY: unique and required.
- REFERENCES: the value must exist in the other table.
Example
CREATE TABLE notes (note_id integer PRIMARY KEY, body text NOT NULL, order_id integer REFERENCES orders (order_id));
A new table; the database refuses a note that refers to a missing order.
Tables used: orders. Try it in a lesson below.
Common mistakes
- A foreign key checks only when rows change. Deleting a customer who still has orders is refused if the orders refer to it.