您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 建立資料庫、資料表與約束完整實作指南

編輯：關於SqlServer

建立資料庫

CREATE DATABASE [TestDB] 
ON (NAME = N'TestDB_Data', FILENAME = N'D:\SQLData\TestDB.mdf', SIZE = 3MB, MAXSIZE = UNLIMITED, FILEGROWTH = 1MB)
LOG ON (NAME = N'TestDB_Log', FILENAME = N'D:\SQLLog\TestDB_Log.ldf', SIZE = 1MB, MAXSIZE = 2048MB, FILEGROWTH = 10%);
GO

約束類型與語法速查

約束	用途	建立語法範例
主鍵 (PK)	唯一識別列，非空	`CONSTRAINT PK_Table PRIMARY KEY CLUSTERED (Id)`
外鍵 (FK)	參考父表主鍵/唯一鍵	`CONSTRAINT FK_Child_Parent FOREIGN KEY (ParentId) REFERENCES Parent(Id) ON UPDATE CASCADE ON DELETE CASCADE`
唯一 (UQ)	值唯一，允許單一 NULL	`CONSTRAINT UQ_Email UNIQUE NONCLUSTERED (Email)`
檢查 (CK)	值域/格式限制	`CONSTRAINT CK_Age CHECK (Age BETWEEN 18 AND 65)`
預設 (DF)	未指定值時自動填入	`CONSTRAINT DF_Status DEFAULT (1) FOR Status`
非空 (NN)	禁止 NULL	`ALTER TABLE T ALTER COLUMN Name NVARCHAR(50) NOT NULL`

實戰範例：完整建表腳本

-- 1. 建立父表：部門
CREATE TABLE dbo.Department (
  DeptID     INT IDENTITY(1,1) NOT NULL,
  DeptName   NVARCHAR(50) NOT NULL,
  CONSTRAINT PK_Department PRIMARY KEY CLUSTERED (DeptID),
  CONSTRAINT UQ_DeptName UNIQUE NONCLUSTERED (DeptName)
);

-- 2. 建立子表：員工
CREATE TABLE dbo.Employee (
  EmpID      INT IDENTITY(100,1) NOT NULL,
  EmpName    NVARCHAR(50) NOT NULL,
  Gender     CHAR(1) NOT NULL CONSTRAINT CK_Gender CHECK (Gender IN ('M','F')),
  DeptID     INT NOT NULL,
  HireDate   DATE NOT NULL CONSTRAINT DF_HireDate DEFAULT (GETDATE()),
  Email      NVARCHAR(100) NULL CONSTRAINT UQ_Email UNIQUE NONCLUSTERED (Email),
  CONSTRAINT PK_Employee PRIMARY KEY CLUSTERED (EmpID),
  CONSTRAINT FK_Employee_Department FOREIGN KEY (DeptID) REFERENCES dbo.Department(DeptID)
    ON UPDATE CASCADE ON DELETE NO ACTION
);

-- 3. 事後新增/移除約束
ALTER TABLE dbo.Employee 
  ADD CONSTRAINT CK_Salary CHECK (Salary > 0);  -- 新增薪資檢查

ALTER TABLE dbo.Employee 
  DROP CONSTRAINT CK_Salary;                    -- 移除約束

-- 4. 建立檢視表
CREATE VIEW dbo.V_EmployeeDept AS
SELECT e.EmpID, e.EmpName, d.DeptName
FROM dbo.Employee e
JOIN dbo.Department d ON e.DeptID = d.DeptID
WHERE e.HireDate <= GETDATE();

關鍵細節提醒

約束命名慣例：PK_Table, FK_Child_Parent, UQ_Column, CK_Rule, DF_Column，利於錯誤訊息識別與維護。
CLUSTERED vs NONCLUSTERED：PK 預設 CLUSTERED；唯一約束預設 NONCLUSTERED。一張表僅能有一個叢集索引。
外鍵動作：CASCADE 級聯更新/刪除需謹慎，避免誤刪大量資料；NO ACTION (預設) 阻止違反參考完整性的操作。
檢查約束 WITH NOCHECK：ALTER TABLE ... WITH NOCHECK ADD CONSTRAINT ... 跳過現有資料驗證，僅約束新資料；生產環境慎用，易留下髒資料。
預設約束綁定：sp_bindefault/sp_unbindefault 為舊版相容語法，新版直接用 DEFAULT 關鍵字。

驗證腳本

-- 查看所有約束
SELECT 
  OBJECT_SCHEMA_NAME(parent_object_id) AS SchemaName,
  OBJECT_NAME(parent_object_id) AS TableName,
  name AS ConstraintName,
  type_desc AS ConstraintType,
  OBJECT_NAME(referenced_object_id) AS ReferencedTable
FROM sys.objects 
WHERE type IN ('PK','F','UQ','C','D')
ORDER BY SchemaName, TableName, ConstraintType;