15 Sep 2020

SQL Constraints

Constraints

→ UNIQUE: prevents 2 records from identical values in the same column

CREATE TABLE Table1 (
    id INT PRIMARY KEY NOT NULL,
    age INT NOT NULL UNIQUE
) 

→ PRIMARY KEY:

• Identifies a record
• One per table
• PKs are unique IDs
• Can consist of multiple columns ← COMPOSITE KEY

Note: PKs can be null in SQLite (coding oversight)

CREATE TABLE Table1 (
    name INT NOT NULL,
    id INT NOT NULL,
    PRIMARY KEY(name, id)
);

→ FOREIGN KEY:

• maintains referential integrity between tables
• aka REFERENCING KEY

Employee table:

name	id (PK)
Nancy	345
Lana	132

CREATE TABLE Department(
    id INT PRIMARY KEY NOT NULL,
    dept CHAR(50) NOT NULL,
    employee_id INT REFERENCES Employee(id)
);

→ EXCLUDE:

• evaluates a comparison of two rows in the table,
• kinda like a generalized UNIQUE constraint: instead of "no two rows can be equal", you can say things like "no two rows overlap", or even "no two rows can be different".

→ DISTINCT:

SELECT name FROM company1;

name
Paula
Teddy
Allen
Teddy
Allen
Allen

SELECT DISTINCT name FROM company1;

name
Paula
Teddy
Allen

Drop Constraints

ALTER TABLE table1 
DROP CONSTRAINT PK_Person;