SQL Fundamentals for Interviews: Keys and Constraints

These are the first questions in almost every SQL interview. They sound simple, but interviewers listen for precise wording, so each section ends with an answer you can say out loud.

Database, table, row and column

A database is an organized collection of data stored electronically. Inside it, a table holds data about one kind of thing, such as employees. A row (also called a record or tuple) is one instance of that thing, and a column (a field or attribute) is one property of it.

id	name	salary
1	Asha	90000
2	Ben	75000

Each horizontal line is a row. id, name and salary are columns.

DBMS versus RDBMS

A DBMS (database management system) is software that stores, retrieves and manages data. An RDBMS is a DBMS built on the relational model proposed by E.F. Codd in 1970: data lives in tables, tables are connected through keys, constraints protect integrity, and you query the data with SQL. SQL Server, PostgreSQL, MySQL and Oracle are all RDBMSs, and they provide transactions with ACID guarantees.

Common wrong answer: "A DBMS stores small data and an RDBMS stores big data." Size is not the difference. The difference is the relational model: tables, relationships, constraints and SQL.

The five groups of SQL commands

Group	Name	Purpose	Examples
DDL	Data Definition Language	Define or change structure	CREATE, ALTER, DROP, TRUNCATE
DML	Data Manipulation Language	Change data	INSERT, UPDATE, DELETE, MERGE
DQL	Data Query Language	Read data	SELECT
DCL	Data Control Language	Manage permissions	GRANT, REVOKE, DENY (SQL Server)
TCL	Transaction Control Language	Control transactions	BEGIN TRANSACTION, COMMIT, ROLLBACK, SAVE TRANSACTION

Some textbooks count SELECT as part of DML. If the interviewer does, say so and explain that you separate reading from changing data.

Constraints

Constraints are rules the database enforces for you, so bad data never gets in.

Constraint	What it guarantees
	The column always has a value
	No two rows share the same value in the column(s)
	Uniquely identifies each row. Unique and not null
	The value must exist in the referenced table, so no orphan rows
	The value satisfies a condition, such as
	Supplies a value when none is given

Primary key versus unique key

	Primary key	Unique key
How many per table	One	Many
allowed	No	Yes. SQL Server allows a single ; PostgreSQL and MySQL allow several
Default index in SQL Server	Clustered	Non-clustered
Purpose	Identify each row	Prevent duplicates in a column that is not the row identifier

A composite primary key uses two or more columns together, for example : the pair must be unique, even though each column alone can repeat.

Foreign keys and cascading rules

A foreign key links a column to the primary key (or any unique key) of another table. It prevents orphans: you cannot create an order for a customer who does not exist, and you cannot delete a customer who still has orders, unless you tell the database what to do.

Rule on delete or update	Effect on the child rows
(default)	The change is rejected
	The same change is applied to the child rows
	The foreign key column is set to
	The foreign key column is set to its default value

An RDBMS does not require foreign keys to exist. They are integrity constraints, not a prerequisite for having tables.

DELETE versus TRUNCATE versus DROP

This is the most common first question of all.

Category	DML	DDL	DDL
Removes	The rows you choose, or all rows	All rows	The whole table: rows, structure and indexes
clause	Yes	No	No
Identity counter	Not reset	Reset to the seed	Gone with the table
Triggers	triggers fire	Do not fire	Not applicable
Foreign keys	Allowed if no rows break a constraint	Blocked if another table references this one	Blocked if another table references it
Speed on a big table	Slower: every row is logged	Fast: whole data pages are deallocated	Fast

Two details that earn extra credit:

• is minimally logged, not "unlogged". In SQL Server and PostgreSQL it can still be rolled back inside a transaction. In MySQL and Oracle, and commit immediately and cannot be rolled back.
• fails on a table that is referenced by a foreign key, even if the referencing table is empty.

Choosing data types

Type	Use it for	Notes
,	Whole numbers	for ids that may pass about 2 billion
	Money and exact numbers	Never store money in : it cannot represent values like 0.1 exactly
	Fixed-length text	Padded with spaces to characters
	Variable-length text	Non-Unicode (one byte per character in most code pages)
	Variable-length Unicode text	Two bytes per character in SQL Server; use it for names and text in many languages
,	Dates and timestamps	Prefer these over the older
	True or false	SQL Server's boolean type

Identity columns

An column in SQL Server generates increasing numbers automatically. Gaps are normal: if an insert fails or a transaction rolls back, the numbers it used are not reused. resets the counter, does not.

Typical follow-up questions

• Can a table exist without a primary key? Yes, but it is poor design: you cannot reliably identify, update or reference one row.
• Can a primary key have more than one column? Yes, that is a composite key.
• Can a foreign key point to a column that is not a primary key? Yes, if that column has a unique constraint or unique index.

Practise

1. Create the tables from the roadmap, then try to delete a department that has employees. What happens with ? What changes if you add ?
2. Insert three rows into a table with an identity column, delete them, insert again, and note the ids. Repeat with .
3. Take the SQL Basics quiz to check what stuck.