SQL Constraints, Primary Key, Foreign Key, Auto Increment

Constraints, Keys and Auto-increment

Constraints are rules a table checks on every change. A row that breaks one is refused with an error, so bad data never gets in.

Syntax

When rules are checked: on every INSERT and UPDATE, and on DELETE for foreign keys. When you add a rule to a table that already has rows, those rows are checked too.

Code
CREATE TABLE table_name (
  id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  column1 TYPE NOT NULL UNIQUE,
  column2 TYPE CHECK (condition),
  column3 INTEGER REFERENCES other_table (other_id) ON DELETE CASCADE
);
 
ALTER TABLE table_name ADD CONSTRAINT constraint_name rule;
ALTER TABLE table_name DROP CONSTRAINT constraint_name;

Rule	Refuses
`NOT NULL`	a missing value
`UNIQUE`	a value another row already has (NULLs don't count; SQL Server allows only one NULL)
`CHECK (condition)`	a row where the condition is false
`PRIMARY KEY`	a missing or repeated key; one per table, and it can span several columns
`REFERENCES` (foreign key)	a value that doesn't exist in the other table
`ON DELETE CASCADE` / `SET NULL`	when the other row is deleted, delete these rows too / clear the column; without it, the delete is refused
`GENERATED ALWAYS AS IDENTITY`	an id you type; it numbers new rows itself

Examples

Let the database say no

Rules stop bad values at the door, with an error that names the rule.

Code
-- Fails: products has CHECK (price > 0)
UPDATE products SET price = -1 WHERE product_id = 1;
 
-- Fails: there is no category 99
INSERT INTO products (product_name, category_id, price) VALUES ('Mystery Item', 99, 100);

Add a rule to a table

Name your rules, so errors and later changes are easy to read.

Code
ALTER TABLE products ADD CONSTRAINT chk_stock_max CHECK (stock <= 10000);
ALTER TABLE accounts ADD CONSTRAINT chk_account_no CHECK (account_no LIKE 'ACC%');

Primary keys

Identify each row by one column, or by a combination of columns.

Code
-- One cart line per customer and product: the pair is the key
CREATE TABLE cart_items (
  customer_id INTEGER,
  product_id INTEGER,
  quantity INTEGER NOT NULL CHECK (quantity > 0),
  PRIMARY KEY (customer_id, product_id)
);

Foreign keys and ON DELETE

Link rows to another table, and decide what happens when the linked row is deleted.

Code
-- Deleting a product also deletes its alerts
CREATE TABLE price_alerts (
  alert_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  product_id INTEGER NOT NULL REFERENCES products (product_id) ON DELETE CASCADE,
  target_price NUMERIC(10, 2) NOT NULL
);
 
-- Fails: orders point to customer 1 and their foreign key refuses the delete
DELETE FROM customers WHERE customer_id = 1;

Auto-increment: IDENTITY and SERIAL

Let the table number new rows for you. GENERATED … AS IDENTITY is the standard form, used by PostgreSQL and Oracle; MySQL writes AUTO_INCREMENT, and SQL Server IDENTITY(1, 1).

Code
CREATE TABLE tickets (
  ticket_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  subject TEXT NOT NULL
);
INSERT INTO tickets (subject) VALUES ('Late delivery'), ('Wrong size');   -- ids 1 and 2
 
-- Fails: GENERATED ALWAYS refuses an id you type
INSERT INTO tickets (ticket_id, subject) VALUES (10, 'Refund');
 
-- SERIAL, PostgreSQL's older style, is only a default from a counter, so typed ids get in
CREATE TABLE old_tickets (ticket_id SERIAL PRIMARY KEY, subject TEXT NOT NULL);

Read a table's rules

List the rules on a table and their definitions. pg_constraint is PostgreSQL's own list; the standard view information_schema.table_constraints works in MySQL and SQL Server too.

Code
-- contype: p = primary key, f = foreign key, u = unique, c = check
SELECT conname, contype, pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE conrelid = 'order_items'::regclass
ORDER BY conname;

Remember

UNIQUE allows several NULLs (SQL Server only one); add NOT NULL when a value is required.
Adding a rule fails if existing rows already break it.
Without an ON DELETE action, a row that others point to can't be deleted.
GENERATED ALWAYS refuses typed ids; SERIAL and GENERATED BY DEFAULT don't.
PostgreSQL doesn't index foreign key columns for you.

Practice

Constraints, Keys and Auto-increment

Constraints are rules a table checks on every change. A row that breaks one is refused with an error, so bad data never gets in.

Syntax

When rules are checked: on every INSERT and UPDATE, and on DELETE for foreign keys. When you add a rule to a table that already has rows, those rows are checked too.

Code
CREATE TABLE table_name (
  id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  column1 TYPE NOT NULL UNIQUE,
  column2 TYPE CHECK (condition),
  column3 INTEGER REFERENCES other_table (other_id) ON DELETE CASCADE
);
 
ALTER TABLE table_name ADD CONSTRAINT constraint_name rule;
ALTER TABLE table_name DROP CONSTRAINT constraint_name;

Rule	Refuses
`NOT NULL`	a missing value
`UNIQUE`	a value another row already has (NULLs don't count; SQL Server allows only one NULL)
`CHECK (condition)`	a row where the condition is false
`PRIMARY KEY`	a missing or repeated key; one per table, and it can span several columns
`REFERENCES` (foreign key)	a value that doesn't exist in the other table
`ON DELETE CASCADE` / `SET NULL`	when the other row is deleted, delete these rows too / clear the column; without it, the delete is refused
`GENERATED ALWAYS AS IDENTITY`	an id you type; it numbers new rows itself

Examples

Let the database say no

Rules stop bad values at the door, with an error that names the rule.

Code
-- Fails: products has CHECK (price > 0)
UPDATE products SET price = -1 WHERE product_id = 1;
 
-- Fails: there is no category 99
INSERT INTO products (product_name, category_id, price) VALUES ('Mystery Item', 99, 100);

Add a rule to a table

Name your rules, so errors and later changes are easy to read.

Code
ALTER TABLE products ADD CONSTRAINT chk_stock_max CHECK (stock <= 10000);
ALTER TABLE accounts ADD CONSTRAINT chk_account_no CHECK (account_no LIKE 'ACC%');

Primary keys

Identify each row by one column, or by a combination of columns.

Code
-- One cart line per customer and product: the pair is the key
CREATE TABLE cart_items (
  customer_id INTEGER,
  product_id INTEGER,
  quantity INTEGER NOT NULL CHECK (quantity > 0),
  PRIMARY KEY (customer_id, product_id)
);

Foreign keys and ON DELETE

Link rows to another table, and decide what happens when the linked row is deleted.

Code
-- Deleting a product also deletes its alerts
CREATE TABLE price_alerts (
  alert_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  product_id INTEGER NOT NULL REFERENCES products (product_id) ON DELETE CASCADE,
  target_price NUMERIC(10, 2) NOT NULL
);
 
-- Fails: orders point to customer 1 and their foreign key refuses the delete
DELETE FROM customers WHERE customer_id = 1;

Auto-increment: IDENTITY and SERIAL

Let the table number new rows for you. GENERATED … AS IDENTITY is the standard form, used by PostgreSQL and Oracle; MySQL writes AUTO_INCREMENT, and SQL Server IDENTITY(1, 1).

Code
CREATE TABLE tickets (
  ticket_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  subject TEXT NOT NULL
);
INSERT INTO tickets (subject) VALUES ('Late delivery'), ('Wrong size');   -- ids 1 and 2
 
-- Fails: GENERATED ALWAYS refuses an id you type
INSERT INTO tickets (ticket_id, subject) VALUES (10, 'Refund');
 
-- SERIAL, PostgreSQL's older style, is only a default from a counter, so typed ids get in
CREATE TABLE old_tickets (ticket_id SERIAL PRIMARY KEY, subject TEXT NOT NULL);

Read a table's rules

List the rules on a table and their definitions. pg_constraint is PostgreSQL's own list; the standard view information_schema.table_constraints works in MySQL and SQL Server too.

Code
-- contype: p = primary key, f = foreign key, u = unique, c = check
SELECT conname, contype, pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE conrelid = 'order_items'::regclass
ORDER BY conname;

Remember

UNIQUE allows several NULLs (SQL Server only one); add NOT NULL when a value is required.
Adding a rule fails if existing rows already break it.
Without an ON DELETE action, a row that others point to can't be deleted.
GENERATED ALWAYS refuses typed ids; SERIAL and GENERATED BY DEFAULT don't.
PostgreSQL doesn't index foreign key columns for you.

SQLKeeping Data Clean with Rules

Syntax

Examples

Let the database say no

Add a rule to a table

Primary keys

Foreign keys and ON DELETE

Auto-increment: IDENTITY and SERIAL

Read a table's rules

Remember

Practice

SQLKeeping Data Clean with Rules

Syntax

Examples

Let the database say no

Add a rule to a table

Primary keys

Foreign keys and ON DELETE

Auto-increment: IDENTITY and SERIAL

Read a table's rules

Remember

Practice