Keys & Constraints

1-- Method 1: Column-level constraint
2CREATE TABLE Customer (
3    CustomerID INT PRIMARY KEY,
4    CustomerName NVARCHAR(100),
5    Email NVARCHAR(100)
6);
7
8-- Method 2: Table-level constraint (đặt tên)
9CREATE TABLE Customer (
10    CustomerID INT,
11    CustomerName NVARCHAR(100),
12    Email NVARCHAR(100),
13    CONSTRAINT PK_Customer PRIMARY KEY (CustomerID)
14);
15
16-- Method 3: Với IDENTITY (auto-increment)
17CREATE TABLE Customer (
18    CustomerID INT IDENTITY(1,1),
19    CustomerName NVARCHAR(100),
20    Email NVARCHAR(100),
21    CONSTRAINT PK_Customer PRIMARY KEY (CustomerID)
22);

1-- Composite PK: nhiều columns
2CREATE TABLE OrderDetail (
3    OrderID INT,
4    ProductID INT,
5    Quantity INT,
6    UnitPrice DECIMAL(10,2),
7    CONSTRAINT PK_OrderDetail PRIMARY KEY (OrderID, ProductID)
8);
9
10-- Trong AdventureWorks
11-- Sales.SalesOrderDetail có composite PK
12SELECT 
13    COLUMN_NAME
14FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
15WHERE TABLE_SCHEMA = 'Sales' 
16AND TABLE_NAME = 'SalesOrderDetail'
17AND CONSTRAINT_NAME LIKE 'PK%';
18-- Result: SalesOrderID, SalesOrderDetailID

1-- Natural Key (có thể thay đổi, dài)
2CREATE TABLE Book (
3    ISBN VARCHAR(13) PRIMARY KEY,  -- Natural
4    Title NVARCHAR(200)
5);
6
7-- Surrogate Key (recommended)
8CREATE TABLE Book (
9    BookID INT IDENTITY(1,1) PRIMARY KEY,  -- Surrogate
10    ISBN VARCHAR(13) UNIQUE,               -- Natural as UNIQUE
11    Title NVARCHAR(200)
12);

1-- Xem tất cả Primary Keys
2SELECT 
3    t.name AS TableName,
4    i.name AS PKName,
5    COL_NAME(ic.object_id, ic.column_id) AS ColumnName
6FROM sys.tables t
7JOIN sys.indexes i ON t.object_id = i.object_id
8JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
9WHERE i.is_primary_key = 1
10AND SCHEMA_NAME(t.schema_id) = 'Sales'
11ORDER BY t.name;

1-- Table cha
2CREATE TABLE Category (
3    CategoryID INT IDENTITY(1,1) PRIMARY KEY,
4    CategoryName NVARCHAR(50)
5);
6
7-- Table con với FK
8CREATE TABLE Product (
9    ProductID INT IDENTITY(1,1) PRIMARY KEY,
10    ProductName NVARCHAR(100),
11    CategoryID INT,
12    -- Method 2: Table-level (recommended - đặt tên)
13    CONSTRAINT FK_Product_Category 
14        FOREIGN KEY (CategoryID) REFERENCES Category(CategoryID)
15);

1-- ON DELETE và ON UPDATE options
2CREATE TABLE Product (
3    ProductID INT IDENTITY(1,1) PRIMARY KEY,
4    ProductName NVARCHAR(100),
5    CategoryID INT,
6    CONSTRAINT FK_Product_Category 
7        FOREIGN KEY (CategoryID) REFERENCES Category(CategoryID)
8        ON DELETE SET NULL      -- Khi Category bị xóa, Product.CategoryID = NULL
9        ON UPDATE CASCADE       -- Khi CategoryID thay đổi, auto update
10);

1-- Employee với Manager (cùng table)
2CREATE TABLE Employee (
3    EmployeeID INT IDENTITY(1,1) PRIMARY KEY,
4    EmployeeName NVARCHAR(100),
5    ManagerID INT,
6    CONSTRAINT FK_Employee_Manager 
7        FOREIGN KEY (ManagerID) REFERENCES Employee(EmployeeID)
8);
9
10-- Insert data
11INSERT INTO Employee (EmployeeName, ManagerID) VALUES ('CEO', NULL);
12INSERT INTO Employee (EmployeeName, ManagerID) VALUES ('Director', 1);
13INSERT INTO Employee (EmployeeName, ManagerID) VALUES ('Manager', 2);

1-- Xem Foreign Keys của Sales.SalesOrderHeader
2SELECT 
3    fk.name AS FKName,
4    OBJECT_NAME(fk.parent_object_id) AS ChildTable,
5    COL_NAME(fkc.parent_object_id, fkc.parent_column_id) AS ChildColumn,
6    OBJECT_NAME(fk.referenced_object_id) AS ParentTable,
7    COL_NAME(fkc.referenced_object_id, fkc.referenced_column_id) AS ParentColumn,
8    fk.delete_referential_action_desc AS OnDelete,
9    fk.update_referential_action_desc AS OnUpdate
10FROM sys.foreign_keys fk
11JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
12WHERE OBJECT_NAME(fk.parent_object_id) = 'SalesOrderHeader';
13
14-- Query với FK relationships
15SELECT 
16    soh.SalesOrderID,
17    soh.OrderDate,
18    c.CustomerID,
19    p.FirstName + ' ' + p.LastName AS CustomerName,
20    sp.Name AS SalesPerson
21FROM Sales.SalesOrderHeader soh
22JOIN Sales.Customer c ON soh.CustomerID = c.CustomerID
23LEFT JOIN Person.Person p ON c.PersonID = p.BusinessEntityID
24LEFT JOIN Sales.SalesPerson sp2 ON soh.SalesPersonID = sp2.BusinessEntityID
25LEFT JOIN Person.Person sp ON sp2.BusinessEntityID = sp.BusinessEntityID
26WHERE soh.SalesOrderID = 43659;

1-- Single column UNIQUE
2CREATE TABLE Customer (
3    CustomerID INT IDENTITY(1,1) PRIMARY KEY,
4    Email NVARCHAR(100) UNIQUE,
5    Phone NVARCHAR(20)
6);
7
8-- Named UNIQUE constraint
9CREATE TABLE Customer (
10    CustomerID INT IDENTITY(1,1) PRIMARY KEY,
11    Email NVARCHAR(100),
12    Phone NVARCHAR(20),
13    CONSTRAINT UQ_Customer_Email UNIQUE (Email),
14    CONSTRAINT UQ_Customer_Phone UNIQUE (Phone)
15);
16
17-- Composite UNIQUE (combination must be unique)
18CREATE TABLE ProductVariant (
19    VariantID INT IDENTITY(1,1) PRIMARY KEY,
20    ProductID INT,
21    Color NVARCHAR(20),
22    Size NVARCHAR(10),
23    CONSTRAINT UQ_Product_Color_Size UNIQUE (ProductID, Color, Size)
24);

1-- Simple CHECK
2CREATE TABLE Product (
3    ProductID INT IDENTITY(1,1) PRIMARY KEY,
4    ProductName NVARCHAR(100),
5    Price DECIMAL(10,2) CHECK (Price > 0),
6    Quantity INT CHECK (Quantity >= 0)
7);
8
9-- Named CHECK constraint
10CREATE TABLE Employee (
11    EmployeeID INT IDENTITY(1,1) PRIMARY KEY,
12    Salary DECIMAL(12,2),
13    HireDate DATE,
14    BirthDate DATE,
15    CONSTRAINT CK_Employee_Salary CHECK (Salary > 0),
16    CONSTRAINT CK_Employee_BirthDate CHECK (BirthDate < HireDate)
17);
18
19-- CHECK với multiple conditions
20CREATE TABLE Order (
21    OrderID INT IDENTITY(1,1) PRIMARY KEY,
22    OrderDate DATE,
23    ShipDate DATE,
24    Status VARCHAR(20),
25    CONSTRAINT CK_Order_Dates CHECK (ShipDate >= OrderDate),
26    CONSTRAINT CK_Order_Status CHECK (Status IN ('Pending', 'Shipped', 'Delivered', 'Cancelled'))
27);

1-- Xem CHECK constraints
2SELECT 
3    cc.name AS ConstraintName,
4    OBJECT_NAME(cc.parent_object_id) AS TableName,
5    cc.definition AS CheckDefinition
6FROM sys.check_constraints cc
7WHERE SCHEMA_NAME(OBJECTPROPERTY(cc.parent_object_id, 'SchemaId')) = 'Sales';
8
9-- Ví dụ: SalesOrderHeader có CHECK cho Status
10-- CK_SalesOrderHeader_Status: Status >= 0 AND Status <= 8

1-- DEFAULT value
2CREATE TABLE Order (
3    OrderID INT IDENTITY(1,1) PRIMARY KEY,
4    OrderDate DATE DEFAULT GETDATE(),
5    Status VARCHAR(20) DEFAULT 'Pending',
6    Quantity INT DEFAULT 1,
7    IsActive BIT DEFAULT 1
8);
9
10-- Named DEFAULT constraint
11CREATE TABLE Product (
12    ProductID INT IDENTITY(1,1) PRIMARY KEY,
13    CreatedDate DATETIME,
14    ModifiedDate DATETIME,
15    CONSTRAINT DF_Product_CreatedDate DEFAULT GETDATE() FOR CreatedDate,
16    CONSTRAINT DF_Product_ModifiedDate DEFAULT GETDATE() FOR ModifiedDate
17);
18
19-- Insert với DEFAULT
20INSERT INTO Order (Status) VALUES (DEFAULT);  -- Dùng DEFAULT
21INSERT INTO Order DEFAULT VALUES;             -- Tất cả DEFAULT

1-- Xem DEFAULT constraints
2SELECT 
3    dc.name AS ConstraintName,
4    OBJECT_NAME(dc.parent_object_id) AS TableName,
5    COL_NAME(dc.parent_object_id, dc.parent_column_id) AS ColumnName,
6    dc.definition AS DefaultValue
7FROM sys.default_constraints dc
8WHERE SCHEMA_NAME(OBJECTPROPERTY(dc.parent_object_id, 'SchemaId')) = 'Production'
9AND OBJECT_NAME(dc.parent_object_id) = 'Product';
10
11-- Product có nhiều DEFAULT constraints
12-- ModifiedDate = GETDATE()
13-- rowguid = NEWID()

1-- NOT NULL trong CREATE TABLE
2CREATE TABLE Customer (
3    CustomerID INT IDENTITY(1,1) PRIMARY KEY,
4    FirstName NVARCHAR(50) NOT NULL,
5    LastName NVARCHAR(50) NOT NULL,
6    Email NVARCHAR(100),  -- NULLable by default
7    Phone NVARCHAR(20)    -- NULLable
8);
9
10-- Combine NOT NULL với DEFAULT
11CREATE TABLE Order (
12    OrderID INT IDENTITY(1,1) PRIMARY KEY,
13    OrderDate DATE NOT NULL DEFAULT GETDATE(),
14    Status VARCHAR(20) NOT NULL DEFAULT 'Pending'
15);

1-- Add PRIMARY KEY
2ALTER TABLE Customer
3ADD CONSTRAINT PK_Customer PRIMARY KEY (CustomerID);
4
5-- Add FOREIGN KEY
6ALTER TABLE Order
7ADD CONSTRAINT FK_Order_Customer 
8    FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID);
9
10-- Add UNIQUE
11ALTER TABLE Customer
12ADD CONSTRAINT UQ_Customer_Email UNIQUE (Email);
13
14-- Add CHECK
15ALTER TABLE Product
16ADD CONSTRAINT CK_Product_Price CHECK (Price > 0);
17
18-- Add DEFAULT
19ALTER TABLE Order
20ADD CONSTRAINT DF_Order_Status DEFAULT 'Pending' FOR Status;

1-- Drop constraint by name
2ALTER TABLE Customer
3DROP CONSTRAINT UQ_Customer_Email;
4
5ALTER TABLE Order
6DROP CONSTRAINT FK_Order_Customer;
7
8ALTER TABLE Product
9DROP CONSTRAINT CK_Product_Price;

1-- Disable FK check (useful for bulk insert)
2ALTER TABLE Order
3NOCHECK CONSTRAINT FK_Order_Customer;
4
5-- Enable FK check
6ALTER TABLE Order
7CHECK CONSTRAINT FK_Order_Customer;
8
9-- Disable all FK in table
10ALTER TABLE Order
11NOCHECK CONSTRAINT ALL;
12
13-- Enable all FK
14ALTER TABLE Order
15CHECK CONSTRAINT ALL;

1-- All constraints in database
2SELECT 
3    tc.CONSTRAINT_NAME,
4    tc.CONSTRAINT_TYPE,
5    tc.TABLE_SCHEMA,
6    tc.TABLE_NAME
7FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
8WHERE tc.TABLE_SCHEMA = 'Sales'
9ORDER BY tc.TABLE_NAME, tc.CONSTRAINT_TYPE;
10
11-- Chi tiết column constraints
12SELECT 
13    c.TABLE_NAME,
14    c.COLUMN_NAME,
15    c.IS_NULLABLE,
16    c.COLUMN_DEFAULT,
17    tc.CONSTRAINT_TYPE
18FROM INFORMATION_SCHEMA.COLUMNS c
19LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu 
20    ON c.TABLE_NAME = kcu.TABLE_NAME AND c.COLUMN_NAME = kcu.COLUMN_NAME
21LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc 
22    ON kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
23WHERE c.TABLE_SCHEMA = 'Sales' AND c.TABLE_NAME = 'SalesOrderHeader'
24ORDER BY c.ORDINAL_POSITION;

1-- 1. Category table
2CREATE TABLE Category (
3    CategoryID INT IDENTITY(1,1),
4    CategoryName NVARCHAR(50) NOT NULL,
5    Description NVARCHAR(500),
6    IsActive BIT NOT NULL DEFAULT 1,
7    CreatedDate DATETIME NOT NULL DEFAULT GETDATE(),
8    
9    CONSTRAINT PK_Category PRIMARY KEY (CategoryID),
10    CONSTRAINT UQ_Category_Name UNIQUE (CategoryName)
11);
12
13-- 2. Product table
14CREATE TABLE Product (
15    ProductID INT IDENTITY(1,1),
16    ProductName NVARCHAR(100) NOT NULL,
17    SKU VARCHAR(20) NOT NULL,
18    CategoryID INT NOT NULL,
19    Price DECIMAL(10,2) NOT NULL,
20    StockQuantity INT NOT NULL DEFAULT 0,
21    IsActive BIT NOT NULL DEFAULT 1,
22    CreatedDate DATETIME NOT NULL DEFAULT GETDATE(),
23    
24    CONSTRAINT PK_Product PRIMARY KEY (ProductID),
25    CONSTRAINT UQ_Product_SKU UNIQUE (SKU),
26    CONSTRAINT FK_Product_Category FOREIGN KEY (CategoryID) 
27        REFERENCES Category(CategoryID),
28    CONSTRAINT CK_Product_Price CHECK (Price > 0),
29    CONSTRAINT CK_Product_Stock CHECK (StockQuantity >= 0)
30);
31
32-- 3. Customer table
33CREATE TABLE Customer (
34    CustomerID INT IDENTITY(1,1),
35    Email NVARCHAR(100) NOT NULL,
36    FirstName NVARCHAR(50) NOT NULL,
37    LastName NVARCHAR(50) NOT NULL,
38    Phone NVARCHAR(20),
39    CreatedDate DATETIME NOT NULL DEFAULT GETDATE(),
40    
41    CONSTRAINT PK_Customer PRIMARY KEY (CustomerID),
42    CONSTRAINT UQ_Customer_Email UNIQUE (Email),
43    CONSTRAINT CK_Customer_Email CHECK (Email LIKE '%@%.%')
44);
45
46-- 4. Order table
47CREATE TABLE [Order] (
48    OrderID INT IDENTITY(1,1),
49    CustomerID INT NOT NULL,
50    OrderDate DATETIME NOT NULL DEFAULT GETDATE(),
51    Status VARCHAR(20) NOT NULL DEFAULT 'Pending',
52    TotalAmount DECIMAL(12,2) NOT NULL DEFAULT 0,
53    
54    CONSTRAINT PK_Order PRIMARY KEY (OrderID),
55    CONSTRAINT FK_Order_Customer FOREIGN KEY (CustomerID) 
56        REFERENCES Customer(CustomerID),
57    CONSTRAINT CK_Order_Status CHECK (Status IN ('Pending', 'Processing', 'Shipped', 'Delivered', 'Cancelled')),
58    CONSTRAINT CK_Order_TotalAmount CHECK (TotalAmount >= 0)
59);
60
61-- 5. OrderDetail table
62CREATE TABLE OrderDetail (
63    OrderID INT NOT NULL,
64    ProductID INT NOT NULL,
65    Quantity INT NOT NULL,
66    UnitPrice DECIMAL(10,2) NOT NULL,
67    LineTotal AS (Quantity * UnitPrice) PERSISTED,  -- Computed column
68    
69    CONSTRAINT PK_OrderDetail PRIMARY KEY (OrderID, ProductID),
70    CONSTRAINT FK_OrderDetail_Order FOREIGN KEY (OrderID) 
71        REFERENCES [Order](OrderID) ON DELETE CASCADE,
72    CONSTRAINT FK_OrderDetail_Product FOREIGN KEY (ProductID) 
73        REFERENCES Product(ProductID),
74    CONSTRAINT CK_OrderDetail_Quantity CHECK (Quantity > 0),
75    CONSTRAINT CK_OrderDetail_UnitPrice CHECK (UnitPrice > 0)
76);

1## Constraint Best Practices
2 
3### Naming Convention
4- PK_TableName
5- FK_ChildTable_ParentTable
6- UQ_TableName_ColumnName
7- CK_TableName_Description
8- DF_TableName_ColumnName
9 
10### Design Guidelines
11- ✅ Always use PRIMARY KEY
12- ✅ Define FOREIGN KEYs for all relationships
13- ✅ Use UNIQUE for natural keys (Email, SKU)
14- ✅ Use CHECK for business rules
15- ✅ Use DEFAULT for common values
16- ✅ Use NOT NULL where appropriate
17 
18### Performance Considerations
19- PK creates clustered index by default
20- FK columns should have index
21- Too many CHECK constraints can slow INSERTs