CompTIA DataSys+ (DS0-001)Database FundamentalsEasy
A database developer is creating a new `Products` table. They want to ensure that every product has a unique identifier and that this identifier is automatically generated when a new product is added, without manual intervention. Which SQL DDL statement component best achieves this?
- ADEFAULT value
- BPRIMARY KEY constraint
- CUNIQUE constraint
- DAUTO_INCREMENT (or IDENTITY/SERIAL)
Show answer & explanationAnswer & explanation
Correct answer: D. AUTO_INCREMENT (or IDENTITY/SERIAL)
The AUTO_INCREMENT (or IDENTITY/SERIAL, depending on the SQL dialect) attribute automatically generates a unique, sequential number for a column when a new row is inserted, fulfilling the requirement for an automatically generated unique identifier.
Why the other options are wrong
- A. DEFAULT sets a value if none is provided, but doesn't guarantee uniqueness or automatic sequencing.
- B. PRIMARY KEY ensures uniqueness and non-nullability, but doesn't automatically generate the value on its own.
- C. UNIQUE ensures uniqueness but doesn't automatically generate the value.
AUTO_INCREMENT
A column attribute in SQL databases that automatically generates a unique, sequential number for new rows.
- Often used for primary keys.
- Ensures unique identifiers without manual input.
- Syntax varies slightly by database system (e.g., IDENTITY, SERIAL).
Memory trick: Auto-Increment: 'Auto'matically 'Increments' the ID.