Tables & Constraints — SQL Questions with Answers

CREATE TABLE students (
  id INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL
);

Answer: A — CREATE TABLE

CREATE TABLE name (column definitions) defines a table's structure - its columns, types and constraints.

CREATE TABLEDDL

Answer: A — DROP TABLE t;

DROP TABLE removes the table structure, data, indexes and constraints entirely. TRUNCATE only empties the rows; DELETE removes rows selectively.

DROP TABLE

Answer: A — The column(s) are UNIQUE and NOT NULL - uniquely identifying each row

A primary key combines UNIQUE + NOT NULL and is automatically indexed. Every table should normally have one to identify rows unambiguously.

PRIMARY KEYConstraints

FOREIGN KEY (customer_id) REFERENCES customers(id)

Answer: A — That the value must exist as a key in the referenced table (referential integrity)

A foreign key prevents orphan rows: you cannot insert an order for a customer_id that does not exist, and (with RESTRICT) cannot delete a referenced customer.

FOREIGN KEYReferential integrity

Answer: A — Ensures no two rows have the same value in that column (but allows NULLs, usually)

UNIQUE enforces distinct values - ideal for emails or usernames. Unlike PRIMARY KEY, a table can have several UNIQUE constraints, and they typically permit one (or more) NULLs.

UNIQUEConstraints

Answer: A — The column must always have a value - inserts/updates cannot leave it NULL

NOT NULL rejects missing values, guaranteeing the data is present. Pair it with a DEFAULT so inserts that omit the column still succeed.

NOT NULLConstraints

price DECIMAL(10,2) CHECK (price >= 0)

Answer: A — Enforces a custom condition on values, e.g. CHECK (age >= 0)

CHECK validates data at write time: prices non-negative, status in a set, end date after start date. The database rejects any row that fails the condition.

CHECKValidation

created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

Answer: A — Supplies a value automatically when an insert omits the column

DEFAULT 'pending' or DEFAULT CURRENT_TIMESTAMP fills in a value so callers need not specify it. Combined with NOT NULL, it guarantees a usable value in every row.

DEFAULT

Answer: A — ALTER TABLE t ADD COLUMN email VARCHAR(100);

ALTER TABLE changes structure. You can add, drop or modify columns and constraints. On large tables, adding a column can lock or rewrite the table, so plan migrations carefully.

ALTER TABLEMigrations

Answer: A — ALTER TABLE t DROP COLUMN email;

DROP COLUMN permanently removes the column and its data. This is irreversible without a backup, so confirm it is truly unused first.

ALTER TABLEDROP COLUMN

id INT AUTO_INCREMENT PRIMARY KEY   -- MySQL

Answer: A — AUTO_INCREMENT (MySQL), SERIAL / GENERATED AS IDENTITY (PostgreSQL), or IDENTITY (SQL Server)

These generate a unique increasing value for each new row, typically for surrogate primary keys. Syntax varies by database, but the concept is universal.

Auto-incrementSurrogate key

Answer: A — Yes - CONSTRAINT chk_price CHECK (price >= 0) - named constraints are easier to reference and drop

Naming constraints gives clearer error messages and lets you drop or alter a specific one later: ALTER TABLE ... DROP CONSTRAINT chk_price.

Named constraints

Answer: A — Creates the table only if it does not already exist - avoiding an error on re-run

Useful in setup scripts and migrations so re-running them does not fail. Similarly, DROP TABLE IF EXISTS avoids errors when the table is absent.

IF NOT EXISTSIdempotency

Answer: A — ALTER TABLE old_name RENAME TO new_name; (syntax varies slightly)

Most databases support renaming via ALTER TABLE ... RENAME TO (MySQL also has RENAME TABLE). Be aware that views and code referencing the old name will break.

ALTER TABLERENAME

Answer: A — The insert is rejected with a constraint-violation error

Constraints reject invalid data rather than silently accepting it - exactly the protection you want. Handle the error gracefully in the application.

CHECKErrors

Answer: A — A foreign key that references the same table - e.g. employees.manager_id -> employees.id

It models hierarchies within one table. The top of the hierarchy (a CEO) has a NULL in the self-referencing column since they have no parent.

Self-referenceHierarchies

Answer: A — ALTER TABLE t ALTER COLUMN c SET/DROP NOT NULL (or MODIFY in MySQL)

Adding NOT NULL requires that no existing rows are NULL, or the change fails. Syntax differs: PostgreSQL uses SET/DROP NOT NULL; MySQL uses MODIFY COLUMN.

ALTER TABLENOT NULL

Answer: A — UNIQUE combined with NOT NULL

UNIQUE stops duplicates and NOT NULL stops missing values. Together they guarantee every row has a distinct, present email - without making it the primary key.

UNIQUENOT NULL

Answer: A — In most databases multiple NULLs are allowed (NULL is 'unknown', not equal to another NULL)

Because NULL != NULL, a UNIQUE column usually permits many NULLs (SQL Server allows only one). If you need every row to have a distinct real value, combine UNIQUE with NOT NULL.

UNIQUENULL

Answer: A — Automatically deletes child rows when the referenced parent row is deleted

CASCADE keeps data consistent by removing dependents (deleting an order deletes its order_items). Use carefully - it can remove more than you expect. Alternatives: RESTRICT (block) and SET NULL.

ON DELETE CASCADEForeign keys

Answer: A — RESTRICT blocks deleting a parent that has children; CASCADE deletes the children too

RESTRICT (and NO ACTION) protect data by refusing the delete. CASCADE propagates it. SET NULL orphans children by nulling their foreign key. Choose based on business rules.

Referential actions

Answer: A — PRIMARY KEY (col1, col2) - the combination is unique, though neither column need be alone

Common in junction tables: PRIMARY KEY (student_id, course_id) means a student can enrol in a course only once, though each id repeats across rows.

Composite keyJunction table

Answer: A — A natural key uses meaningful data (email, ISBN); a surrogate key is a system-generated id with no business meaning

Surrogate keys (auto-increment ids) are stable and simple, so they are the common default. Natural keys carry meaning but can change, which complicates foreign keys.

Surrogate keyNatural key

Answer: A — ALTER TABLE orders ADD CONSTRAINT fk_cust FOREIGN KEY (customer_id) REFERENCES customers(id);

ALTER TABLE ... ADD CONSTRAINT adds it after the fact. The database first checks that all existing values satisfy the constraint, or the ALTER fails.

ALTER TABLEFOREIGN KEY

Answer: A — The database enforces them for EVERY client and script - a last line of defence that never gets bypassed

Multiple apps, admin scripts and manual edits all touch a database. Constraints guarantee integrity regardless of how the data arrives, whereas app validation can be skipped.

Data integrityDefence in depth

Answer: A — A relationship between them, e.g. CHECK (end_date >= start_date)

Multi-column CHECKs validate cross-field rules at the database level - ensuring, for instance, that a booking's end is never before its start.

CHECKCross-column

Answer: A — Joins and cascade checks scan them frequently; without an index those operations are slow

Primary keys are indexed automatically, but foreign keys often are not (except in some databases). Indexing them speeds joins and makes ON DELETE checks efficient.

IndexesForeign keys

Answer: A — Its check can be postponed until the transaction commits, allowing temporary violations mid-transaction

Deferrable foreign keys (PostgreSQL, Oracle) let you insert rows in an order that would momentarily violate the constraint, as long as everything is consistent at COMMIT - useful for circular references.

Deferrable constraintsTransactions

total DECIMAL(12,2) GENERATED ALWAYS AS (price * qty) STORED

Answer: A — A column whose value is derived from other columns by a formula, maintained automatically

GENERATED ALWAYS AS (price * quantity) STORED computes and stores a value from other columns. Great for search or indexing on derived data without app-side maintenance.

Generated columns

Answer: A — The table starts valid, and adding constraints later can fail on existing bad data or require costly full-table validation

Constraints defined upfront prevent bad data from ever entering. Adding them to a populated table forces a validation scan and may fail if pre-existing rows violate the rule.

DesignMigrations