Answer

Constraints are integrity restrictions imposed on columns or tables to automatically control the validity of values. They allow:

• To protect data from incorrect input;
• To move part of the application logic into the database (more reliable and faster);
• To automatically check conditions during inserts, updates, or deletes.

Types of main constraints:

• PRIMARY KEY — unique identifier, prohibits duplication and NULL.
• UNIQUE — prohibits duplicate values in one column.
• FOREIGN KEY — referential integrity, relation to another table.
• CHECK — checks any arbitrary expression.
• NOT NULL — prohibition of NULL values in a column.

Example:

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INT REFERENCES users(id), -- foreign key
  price DECIMAL(10,2) CHECK (price > 0),
  created_at TIMESTAMP NOT NULL
);

Trick question

"Is a unique key (UNIQUE) automatically NULL-SAFE, excluding any number of NULL values in the column?"

In fact, in most DBMSs (for example, PostgreSQL, MySQL) the UNIQUE constraint allows several rows with the value NULL, as NULL is considered "unknown". This often leads to unnoticed duplication of empty values.

Example:

CREATE TABLE test (
  id INT PRIMARY KEY,
  code VARCHAR(10) UNIQUE
);

INSERT INTO test (id, code) VALUES (1, NULL), (2, NULL); -- will succeed

Examples of real errors due to lack of understanding of the nuances

---

Story

The application allowed registration via email, the column was UNIQUE but NOT NOT NULL — the table ended up with a dozen users with NULL-email, which caused issues when integrating with external services.

---

Story

In the ticket ordering system, a FOREIGN KEY was forgotten for the payments table — as a result, there were payments not linked to any order, making it impossible to process refunds for users.

---

Story

**CHECK constraint for discounts: discount > 0. The edge case for 0 was overlooked. The result: the system only accepted discounts greater than 0, while the absence of a discount (0) broke the business logic.