SQL Keys & Constraints Interview Questions

A primary key is a column or combination of columns that uniquely identifies each row in a table. It enforces two constraints automatically: uniqueness and NOT NULL, so no two rows can share the same primary key value and no row can have a null one. Every table should have a primary key because it gives you a reliable way to reference individual records, and it's the foundation for setting up foreign key relationships with other tables.

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  total DECIMAL(10,2)
);

A foreign key is a column in one table that references the primary key of another table, creating a link between the two. It enforces referential integrity, meaning you can't insert a value in the foreign key column that doesn't exist in the referenced table, and depending on the setup, the database can restrict or cascade deletes and updates. For example, an orders table might have a customer_id column that references the id column in a customers table, ensuring every order belongs to a real customer.

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
    ON DELETE CASCADE
);

A foreign key is a column in one table that points to the primary key in another table, establishing a relationship between them. The database uses this constraint to enforce referential integrity, so you can't have an orphaned record that references a non-existent row. For instance, if an orders table has a customer_id foreign key referencing the customers table, the database will reject any insert where that customer_id doesn't exist in customers. You can also configure behavior for cascading deletes or updates so child records are automatically handled when the parent changes.

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
);

Both primary keys and unique keys enforce uniqueness, but they differ in key ways. A primary key does not allow NULL values and there can only be one per table. It creates a clustered index by default in SQL Server. A unique key allows one NULL value (in SQL Server; PostgreSQL allows multiple NULLs) and you can have multiple unique keys per table. It creates a non-clustered index by default.

Without a primary key, you need another way to uniquely identify duplicate rows. The cleanest approach in SQL Server is using a CTE with ROW_NUMBER() partitioned by all the columns that define a duplicate, then deleting rows where the row number is greater than 1. This keeps exactly one copy of each duplicate group.

WITH CTE AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY col1, col2, col3  -- all columns that define a duplicate
               ORDER BY (SELECT NULL)
           ) AS rn
    FROM employees
)
DELETE FROM CTE WHERE rn > 1;

A primary key uniquely identifies each row in a table and cannot contain NULL values. There can only be one primary key per table. A foreign key is a column in one table that references the primary key of another table, enforcing referential integrity between the two. The key practical difference is that a primary key defines identity within its own table, while a foreign key establishes a relationship between tables and can contain NULLs (meaning the relationship is optional) unless you explicitly add a NOT NULL constraint.

CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(100)
);

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(100),
    dept_id INT REFERENCES departments(dept_id)  -- foreign key
);

Auto increment is a column property that automatically generates a unique integer value for each new row inserted, typically used for primary keys so you don't have to manually supply an ID. The syntax varies by database: MySQL uses AUTO_INCREMENT, PostgreSQL uses SERIAL or the GENERATED ALWAYS AS IDENTITY syntax, and SQL Server uses IDENTITY(seed, increment). The seed is the starting value and the increment is how much it increases with each row.

-- MySQL
CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100)
);

-- SQL Server
CREATE TABLE users (
    user_id INT IDENTITY(1,1) PRIMARY KEY,
    username VARCHAR(100)
);

-- PostgreSQL
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(100)
);

NOT NULL is a column constraint that prevents any row from storing a NULL value in that column, meaning a value must always be provided. Without it, a column accepts NULL by default, which represents the absence of a value and can cause issues in calculations, comparisons, and joins since NULL does not equal anything, including itself. You apply it at column definition time, and if you try to insert a row without providing a value for a NOT NULL column (and there's no default), the database will throw an error.

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(100) NOT NULL,  -- must always have a value
    email VARCHAR(100)               -- NULL is allowed here
);

A candidate key is any column or combination of columns that can uniquely identify every row in a table. A table can have multiple candidate keys, and the one you choose to be the official identifier becomes the primary key. The rest remain as alternate keys. For example, in an employees table, both employee_id and email could be candidate keys since both are unique per employee.

A unique key enforces that all values in a column or combination of columns are distinct across rows. Unlike a primary key, a unique key can accept NULL values, and most databases allow multiple NULLs in a unique column since NULL is considered distinct from every other value. A table can have multiple unique keys but only one primary key. Unique keys are often used on fields like email addresses or usernames where you need uniqueness but the column isn't the primary identifier.

CREATE TABLE users (
  user_id   INT PRIMARY KEY,
  email     VARCHAR(100) UNIQUE,
  username  VARCHAR(50) UNIQUE
);

A constraint is a rule enforced on a column or table to ensure data integrity. Common types are PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, and DEFAULT. They prevent invalid data from entering the database and maintain relationships between tables. Constraints can be defined at column level inline during CREATE TABLE or at table level as a named constraint, which makes it easier to reference and drop them later.

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,           -- PRIMARY KEY constraint
    email VARCHAR(100) UNIQUE NOT NULL,    -- UNIQUE + NOT NULL
    department_id INT REFERENCES departments(id), -- FOREIGN KEY
    salary DECIMAL(10,2) CHECK (salary > 0)       -- CHECK
);

Data integrity refers to the accuracy, consistency, and reliability of data stored in a database. It ensures that data remains correct throughout its lifecycle, from insertion to deletion. There are four main types: entity integrity (primary keys are unique and not null), referential integrity (foreign keys point to valid rows), domain integrity (column values fall within acceptable ranges or types), and user-defined integrity (custom business rules). Constraints like PRIMARY KEY, FOREIGN KEY, CHECK, and NOT NULL are the primary mechanisms that enforce data integrity in SQL.

Constraints can be defined at column level inline with the column definition, or at the table level after all columns are listed. You can also add constraints to an existing table using ALTER TABLE. The main constraint types are PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, and DEFAULT.

-- Column-level constraint
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    amount   DECIMAL(10,2) CHECK (amount > 0),
    status   VARCHAR(20) NOT NULL DEFAULT 'pending'
);

-- Adding a constraint after table creation
ALTER TABLE orders
ADD CONSTRAINT chk_status CHECK (status IN ('pending','shipped','delivered'));