C. SQL Keys
1. What is a Primary Key?
The PRIMARY KEY is a constraint that uniquely identifies each row in a table. It must contain UNIQUE values and has an implicit NOT NULL constraint. Tables in SQL can have one and only one primary key, which is comprised of single or multiple fields (columns).
When creating a table:
TEXT/X-SQL
1-- Create table with single field as PK
2CREATE TABLE customers(
3 id INT NOT NULL,
4 name VARCHAR(255),
5 PRIMARY KEY (id)
6);
7
8-- Create table with multiple fields as PK
9CREATE TABLE customers(
10 id INT NOT NULL,
11 surname VARCHAR(255),
12 name VARCHAR(255) NOT NULL,
13 CONSTRAINT PK_Customer,
14 PRIMARY KEY (id, name)
15 );When modifying a table:
TEXT/X-SQL
1-- Set single field as PK
2ALTER TABLE customers
3ADD PRIMARY KEY (id);
4
5-- Set multiple fields as PK
6ALTER TABLE customers
7ADD CONSTRAINT PK_customer
8PRIMARY KEY (id, name);2. What is a Foreign Key?
A FOREIGN KEY comprises of single or collection of fields in a table that essentially refers to the PRIMARY KEY in another table. Foreign key constraint ensures referential integrity in the relation between two tables.
The table with the FOREIGN KEY constraint is labelled as the child table, and the table containing the candidate key is labelled as the referenced or parent table.
When creating a table:
TEXT/X-SQL
1-- Create table with a single field as unique
2CREATE TABLE Students (
3 ID INT NOT NULL,
4 Name VARCHAR(255),
5 LibraryID INT,
6 PRIMARY KEY (ID)self
7 FOREIGN KEY (Library_ID) REFERENCES Library(LibraryID)
8);When modifying a table:
TEXT/X-SQL
1ALTER TABLE Students
2ADD FOREIGN KEY (LibraryID)
3REFERENCES Library (LibraryID);