Microsoft SQL Server 2012 Bible

The Security Table

The Security table serves as a many-to-many associative table (junction table) between the Person and Address tables. The security levels determine the level of access:

0 or no row: 0 access

1: Read access

2: Write access

3: Admin access

Alternatively, three-bit columns could be used for read, write, and administer rights, but the privileges are cumulative, so an integer column seems appropriate.

The security table has two logical foreign keys. The foreign key to the address table is handled by a standard foreign-key constraint; however, the reference to the person table should allow only contacts who are flagged as employees, so a trigger is used to enforce that complex referential-integrity requirement. The security assignment is meaningless without its contact or location, so both foreign keys are cascading deletes. A constraint is applied to the security-level column to restrict any entry to the valid security codes (0–3), and a unique constraint ensures that a person may have only one security code per address:

USE Adventureworks2012;

CREATE TABLE dbo.Security (
SecurityID INT IDENTITY(1,1) NOT NULL
  PRIMARY KEY NONCLUSTERED,
PersonID INT NOT NULL 
  REFERENCES Person.Person(BusinessEntityID) ON DELETE CASCADE, 
AddressID INT NOT NULL 
  REFERENCES Person.Address(AddressID) ON DELETE CASCADE,
SecurityLevel INT NOT NULL DEFAULT 0 
 );

The following three commands add the constraints to the Security table:

CREATE TRIGGER PersonID_RI ON dbo.Security AFTER INSERT, ...