Microsoft Azure Data FundamentalsDescribe how to work with relational data on AzureMedium
A data engineer is working with an Azure SQL Database. They need to create a table that will store employee records, and each employee must have a unique employee ID. This ID should be automatically generated by the database when a new employee record is inserted. Which column property should be used for the Employee ID column?
- ADEFAULT
- BPRIMARY KEY
- CNOT NULL
- DIDENTITY
Show answer & explanationAnswer & explanation
Correct answer: D. IDENTITY
The IDENTITY property (or AUTO_INCREMENT in other SQL dialects) in Azure SQL Database automatically generates unique, sequential numbers for a column when new rows are inserted. This perfectly matches the requirement for an automatically generated, unique employee ID.
Why the other options are wrong
- A. DEFAULT specifies a default value for a column if no value is provided, but it doesn't guarantee uniqueness or automatic sequential generation.
- B. PRIMARY KEY enforces uniqueness and non-nullability, but it does not automatically generate values. An IDENTITY column is often, but not always, a PRIMARY KEY.
- C. NOT NULL ensures a column cannot contain NULL values but does not automatically generate unique IDs.
IDENTITY Property (SQL)
A column property in SQL Server (and Azure SQL DB) that automatically generates unique, sequential numeric values for a column when new rows are inserted.
- Automatically generates unique numbers
- Values are typically sequential
- Often used for primary keys
- Syntax: `IDENTITY(seed, increment)`
Memory trick: Columns have rules: null, default, unique, auto-increment.