Keys, Constraints, and Referential Integrity in SQL DDL
Objective. Students will write DDL statements (CREATE TABLE, ALTER TABLE) that implement primary keys, foreign keys, unique constraints, check constraints, and NOT NULL constraints in PostgreSQL, and verify that the database engine enforces each constraint.
This lesson, its worked examples and its practice questions has not yet been verified by an industry-certified professional or by a certification or approval body. It is a study draft and may contain errors. Do not use it as instructions for work on a job site or on live equipment. Always follow your instructor, your supervising professional, and current official references and regulations.
VoTechWorks program approval and accreditation are still in progress. Credentials are issued only by the certifying bodies named in each program, after their own exams.
Keys, Constraints, and Referential Integrity in SQL DDL
Constraints are rules the database engine enforces automatically to keep data accurate and consistent. Writing them into your DDL is far more reliable than depending on application code alone. This lesson covers the five constraint types you will use most often in PostgreSQL, how to write them, and how the engine reports violations.
Primary Keys
A primary key uniquely identifies every row in a table. It combines two implicit rules: the column(s) must be NOT NULL, and the value(s) must be unique across all rows. Each table may have only one primary key.
```sql
CREATE TABLE customer (
customer_id SERIAL PRIMARY KEY,
last_name VARCHAR(60) NOT NULL,
email VARCHAR(120) NOT NULL
);
```
For a composite key, list all columns in a table-level constraint:
```sql
CREATE TABLE order_line (
order_id INT NOT NULL,
line_number INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL,
PRIMARY KEY (order_id, line_number)
);
```
Foreign Keys and Referential Integrity
A foreign key links a column in one table (the child) to the primary key of another table (the parent). PostgreSQL will reject any INSERT or UPDATE that would create an orphan row — a child row with no matching parent.
```sql
CREATE TABLE "order" (
order_id SERIAL PRIMARY KEY,
customer_id INT NOT NULL
REFERENCES customer(customer_id)
ON DELETE RESTRICT
ON UPDATE CASCADE,
order_date DATE NOT NULL
);
```
`ON DELETE RESTRICT` prevents deleting a customer who still has orders. `ON UPDATE CASCADE` automatically updates the child column if the parent key value changes. Choose these actions deliberately — they are part of your design.
You can also add a foreign key after the table exists using ALTER TABLE:
```sql
ALTER TABLE order_line
ADD CONSTRAINT fk_order_line_order
FOREIGN KEY (order_id)
REFERENCES "order"(order_id)
ON DELETE CASCADE;
```
UNIQUE Constraints
A UNIQUE constraint lets columns other than the primary key serve as alternate keys. Unlike a primary key, a unique column may contain NULL (PostgreSQL treats each NULL as distinct from every other NULL).
```sql
ALTER TABLE customer
ADD CONSTRAINT uq_customer_email UNIQUE (email);
```
NOT NULL Constraints
A NOT NULL constraint prevents a column from ever holding a NULL value. Apply it to any attribute that must always have a value. It is written inline with the column definition and cannot be added at the table level.
```sql
last_name VARCHAR(60) NOT NULL
```
CHECK Constraints
A CHECK constraint evaluates a Boolean expression before every INSERT or UPDATE. If the expression returns FALSE the operation is rejected; if it returns TRUE or NULL the row is accepted.
```sql
CREATE TABLE order_line (
order_id INT NOT NULL,
line_number INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL CHECK (quantity > 0),
unit_price NUMERIC(10,2) NOT NULL CHECK (unit_price >= 0),
PRIMARY KEY (order_id, line_number)
);
```
Name your check constraints so error messages identify them clearly:
```sql
CONSTRAINT chk_quantity_positive CHECK (quantity > 0)
```
Verifying Constraints in psql
Use `\d tablename` in psql to see the column definitions and all constraints for a table. To query the catalog directly:
```sql
SELECT constraint_name, constraint_type
FROM information_schema.table_constraints
WHERE table_name = 'order_line';
```
This is the same query your instructor will use to grade your work.
Reading Error Messages
When a constraint fires, PostgreSQL returns a structured error. Learn to read it:
- duplicate key value violates unique constraint — you tried to insert a duplicate primary or unique key.
- violates foreign key constraint — you referenced a parent row that does not exist, or tried to delete a parent with dependent children.
- violates not-null constraint — a required column was left empty.
- violates check constraint — a column value failed a CHECK expression.
Record the full error text in your lab log exactly as it appears. The DETAIL line usually names the conflicting value.
Safety and Best Practices in the Lab
Data integrity failures in production systems can cause serious business harm — lost orders, double-billing, or orphaned financial records. Treat constraint design as a safety practice, not just a syntax exercise.
In the lab environment:
- Work in your own named database; never modify shared system databases.
- If you are using a shared cloud sandbox, drop only objects you created.
- Save your DDL scripts to a file before testing so you can recreate the schema quickly if you need to start over.
- Never store real personal data (real names, real email addresses, real payment information) in practice schemas.
These habits apply equally to professional environments where mishandling data may violate privacy regulations.
Key terms10
Materials: PostgreSQL installed on lab workstations or cloud sandbox, psql command-line client, DDL reference (PostgreSQL documentation Chapter 5), lab log template
Check: Instructor queries information_schema.table_constraints and information_schema.columns to verify each required constraint exists. Pass: all primary keys, at least one foreign key per relationship, and at least one check constraint present; all constraint-test errors correctly recorded in lab log.
Practice check8
Answer each question, then check it. Nothing is recorded; this is your own practice.
