Primary Keys, Foreign Keys, and Common Constraints

Columns and types are only part of table design. User IDs must not repeat, an email may be required, and an order's user_id should point to a real user. A database uses constraints to check these rules during writes. Because the database enforces them, it can reject invalid data even when an application sends incorrect SQL.

PRIMARY KEY — identifies a row

NOT NULL — requires a value

UNIQUE — prevents duplicates

DEFAULT — fills omitted values

FOREIGN KEY — protects references

PRIMARY KEY: one row's identity

A primary key uniquely identifies a row. A database normally rejects duplicate primary-key IDs, and the key cannot be NULL because the row needs a stable identity. Many tables use id.

NOT NULL: a value is required

Business-critical fields such as a name, product price, or order date may be NOT NULL. An optional phone should probably remain nullable. Constraints should express real requirements rather than applying the same rule mechanically.

UNIQUE: a business value cannot repeat

UNIQUE also enforces distinct values, but commonly protects business fields such as email, username, or order_number. The primary key identifies the row itself.

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(200) NOT NULL UNIQUE
);

DEFAULT: use an initial value when omitted

When INSERT omits a field, DEFAULT can supply a common initial state such as TRUE for is_active, pending for order status, or 1 for quantity. It reduces repetition, but the default must still match the business meaning.

FOREIGN KEY: a reference must exist

A foreign key ensures that a reference points to an existing row in another table. Once orders.user_id references users.id, the database can reject user_id 999 when that user does not exist, preventing an orphaned order.

CREATE TABLE orders (
  id INT PRIMARY KEY,
  user_id INT NOT NULL,
  quantity INT NOT NULL DEFAULT 1,
  status VARCHAR(50) DEFAULT 'pending',
  FOREIGN KEY (user_id) REFERENCES users(id)
);

Constraints are not all business logic. “A VIP user may place at most ten orders per day” cannot be expressed by NOT NULL or UNIQUE alone. It usually needs application logic or advanced database mechanisms. Constraints are the baseline defense for identity, required values, defaults, and references.

CREATE TABLE products (
  id INTEGER PRIMARY KEY
);