CompTIA DataSys+ (DS0-001)Database Management and MaintenanceMedium
A data engineer is designing a new database for a global e-commerce application. To ensure data integrity, they need to prevent duplicate entries in the 'customer_email' column, while also allowing some historical records to exist where the email might be NULL if it was not collected. Which constraint is the most appropriate for the 'customer_email' column?
- APRIMARY KEY
- BUNIQUE
- CNOT NULL
- DCHECK
Show answer & explanationAnswer & explanation
Correct answer: B. UNIQUE
A UNIQUE constraint ensures that all values in the 'customer_email' column are distinct. Crucially, a UNIQUE constraint typically allows one NULL value, which satisfies the requirement for historical records where the email might not have been collected.
Why the other options are wrong
- A. A PRIMARY KEY constraint enforces uniqueness and disallows NULL values, which contradicts the requirement to allow NULL emails.
- C. A NOT NULL constraint only prevents NULL values but does not enforce uniqueness for non-NULL email addresses.
- D. A CHECK constraint validates data against a specified condition but does not enforce uniqueness.
UNIQUE Constraint (with NULLs)
A database constraint that ensures all values in a specified column or set of columns are distinct, with the exception that most database systems allow for one (or sometimes multiple) NULL values.
- Enforces uniqueness for non-NULL values.
- Typically allows one NULL value (SQL Standard, PostgreSQL, MySQL).
- SQL Server allows multiple NULL values in a UNIQUE constraint.
Memory trick: Unique emails, but some can be 'null' and still be unique.