CompTIA DataSys+ (DS0-001)Database FundamentalsMedium
A database developer is designing a new `Employees` table for a human resources application. Each employee must have a unique `EmployeeID`, and the database should automatically assign a new sequential `EmployeeID` whenever a new employee record is added. Which SQL keyword or property should be used for the `EmployeeID` column to achieve this automatic sequential numbering?
- AAUTO_INCREMENT
- BUNIQUE
- CDEFAULT
- DIDENTITY
Show answer & explanationAnswer & explanation
Correct answer: A. AUTO_INCREMENT
`AUTO_INCREMENT` (or `IDENTITY` in some SQL dialects like SQL Server) is specifically designed to automatically generate unique, sequential integer values for a column, typically used for primary keys, meeting the requirement for automatic sequential numbering.
Why the other options are wrong
- B. `UNIQUE` ensures that all values in a column are distinct, but it does not automatically generate them.
- C. `DEFAULT` assigns a default value if no value is explicitly provided, but it doesn't automatically generate sequential unique numbers.
- D. `IDENTITY` is the SQL Server equivalent of `AUTO_INCREMENT`; while correct for SQL Server, `AUTO_INCREMENT` is the more generic and widely recognized term across various SQL databases (e.g., MySQL, PostgreSQL with `SERIAL`). Given the options, `AUTO_INCREMENT` is a common and appropriate answer for this general scenario.
AUTO_INCREMENT (or IDENTITY/SERIAL)
A column property in SQL databases that automatically generates a unique, sequential integer number for each new row inserted into a table.
- Ensures unique values for primary keys.
- Simplifies data entry by eliminating manual ID assignment.
- Automatically increments the value for each new record.
- Syntax varies slightly across different database systems (e.g., `SERIAL` in PostgreSQL, `IDENTITY` in SQL Server).
Memory trick: AUTO_INCREMENT: Automatically number your records, easy and unique!