Constraints

Primary key

sql

CREATE TABLE employee (
  eid INTEGER PRIMARY KEY,
  ename CHAR(20)
);

Marks a column as the unique identifier for every row. No two rows can share a PRIMARY KEY value, and it can never be NULL, one column, one guaranteed way to point at exactly one row.

Behaviorally, that's the same guarantee UNIQUE + NOT NULL gives, but they're not interchangeable: a table can only have one PRIMARY KEY, while it can have many UNIQUE NOT NULL columns side by side. PRIMARY KEY is really "the one column reserved for identifying a row"; UNIQUE + NOT NULL is "just another column that happens to enforce the same rule."

Composite key

sql

CREATE TABLE enrollment (
  studentId INTEGER,
  courseId INTEGER,
  PRIMARY KEY (studentId, courseId)
);

A primary key made of more than one column together, used when no single column is unique on its own, but the combination is. Here, one student can enroll in many courses and one course can have many students, but the same student can't enroll in the same course twice, it's the (studentId, courseId) pair that has to be unique, not either column alone.

Unique key

sql

CREATE TABLE employee (
  eid INTEGER PRIMARY KEY,
  email CHAR(50) UNIQUE
);

Same "no duplicates" rule as a primary key, but a table can only have one primary key while it can have several UNIQUE columns, and unlike a primary key, a UNIQUE column is allowed to be NULL.

Default

sql

CREATE TABLE employee (
  eid INTEGER PRIMARY KEY,
  grade CHAR(2) DEFAULT 'C'
);

If an INSERT doesn't specify a value for that column, DEFAULT supplies one automatically instead of leaving it NULL. Insert an employee without mentioning grade, and it lands on 'C' without needing to say so explicitly.

Check

sql

CREATE TABLE employee (
  eid INTEGER PRIMARY KEY,
  gross DECIMAL CHECK (gross > 0)
);

Validates every value going into that column against a condition, before the row's allowed in. Try to insert gross = -500, and the INSERT fails outright, CHECK is what enforces business rules like "this number can't be negative" at the database level instead of trusting every piece of application code to get it right.