SQL Data Definition Language (DDL) 3 — Questions and Answers
Question 1: Which constraint ensures a column cannot contain duplicate values but allows one NULL in many systems?
- PRIMARY KEY
- UNIQUE (Correct answer)
- CHECK
- DEFAULT
Correct answer: UNIQUE
A UNIQUE constraint forbids duplicate values while typically permitting a single NULL.
Question 2: What does a FOREIGN KEY constraint enforce?
- A column must be unique
- Referential integrity between two tables (Correct answer)
- A column has a default value
- A column is auto-incremented
Correct answer: Referential integrity between two tables
A FOREIGN KEY ensures values in one table match values in a referenced table, maintaining referential integrity.
Question 3: Which clause defines a column that automatically gets a value when none is supplied on INSERT?
- AUTO VALUE
- DEFAULT (Correct answer)
- CHECK
- NOT NULL
Correct answer: DEFAULT
The DEFAULT clause provides a value automatically when an INSERT omits that column.
Question 4: A CHECK constraint is used to do what?
- Verify a foreign key exists
- Limit the range of values allowed in a column (Correct answer)
- Create an index
- Rename a column
Correct answer: Limit the range of values allowed in a column
A CHECK constraint restricts column values to those satisfying a specified Boolean condition.
Question 5: How can you add a constraint to an existing table?
- CREATE CONSTRAINT
- ALTER TABLE ... ADD CONSTRAINT (Correct answer)
- INSERT CONSTRAINT
- SET CONSTRAINT
Correct answer: ALTER TABLE ... ADD CONSTRAINT
ALTER TABLE ... ADD CONSTRAINT attaches a new constraint to an already-existing table.
Question 6: What happens by default when you try to insert a NULL into a column defined as NOT NULL?
- The NULL is converted to zero
- The statement fails with an error (Correct answer)
- The default value is silently used
- The row is skipped quietly
Correct answer: The statement fails with an error
A NOT NULL column rejects NULL values, causing the insert to fail unless a value is provided.
Question 7: Which combination of properties best describes a PRIMARY KEY?
- Allows duplicates and NULLs
- Unique and NOT NULL (Correct answer)
- Unique but allows NULLs
- Allows duplicates but NOT NULL
Correct answer: Unique and NOT NULL
A PRIMARY KEY is both unique and NOT NULL, uniquely identifying each row.
SQL Data Definition Language (DDL) 3
Which constraint ensures a column cannot contain duplicate values but allows one NULL in many systems?