A primary key is a column or set of columns that uniquely identifies each row in a table. It provides a stable target for relationships between tables. section 4.1.1
Common mistakes and safer choices:
- Mistake: define every column as NVARCHAR(MAX). This prevents a primary key on EmployeeID and permits unsuitable values throughout the table. section 3.0
- Safer practice: choose a suitable data type and constrain the identifier, such as INT NOT NULL PRIMARY KEY for EmployeeID. section 3.0
- Keep composite keys to a minimum when possible; keys with business meaning are natural keys, while arbitrary incrementing identifiers are artificial keys. section 4.1.1
Employee and office records
An Offices table can use OfficeID as its primary key. Employees can store an OfficeID foreign key instead of repeating office names, addresses, and contact details. The foreign key then refers to an existing primary-key value and enforces referential integrity. section 4.1.1
Listing 3.2 Updating the employee table column types
DROP TABLE dbo.Employees ; GO CREATE TABLE dbo.Employees ( EmployeeID INT NOT NULL PRIMARY KEY, FirstName NVARCHAR(32) NOT NULL, LastName NVARCHAR(32) NOT NULL, DateOfBirth DATE NOT NULL, EmployeeStartDate DATE NOT NULL, ManagerID INT NULL, Salary MONEY NOT NULL, Department NVARCHAR(64) NOT NULL, DepartmentCode NVARCHAR(4) NOT NULL, Role NVARCHAR(64) NOT NULL, WeeklyContractedHours INT NOT NULL, StaffOrContract INT NOT NULL, ContractEndDate DATE NULL ) ;
Inspect the executable table definition that assigns INT NOT NULL PRIMARY KEY to EmployeeID alongside more appropriate column types. section 3.0
