SQL Server Relationship Reference
2023-02-22
筆記 SQL Server 使用 TSQL 設計資料表關聯的方式。
說明
兩張資料表，並且保持零到一對一關係，欄位命名規範要依照 SQL Server 慣例，CREATE TABLE 的時候指定 Foreign Key 名稱
One To One
Zero Or One To One In EF Database First 😉
CREATE TABLE Users (
User_ID INT PRIMARY KEY,
First_Name VARCHAR(50),
Last_Name VARCHAR(50),
Email_Address VARCHAR(100),
Username VARCHAR(50),
Password VARCHAR(50)
);
CREATE TABLE UserDetails (
User_ID INT PRIMARY KEY,
Age INT,
Gender VARCHAR(10),
Address VARCHAR(100),
Phone_Number VARCHAR(20),
FOREIGN KEY (User_ID) REFERENCES Users(User_ID)
);Zero Or One To Many In EF Database First 🤔
CREATE TABLE Customer (
CustomerID INT PRIMARY KEY,
FirstName NVARCHAR(50),
LastName NVARCHAR(50),
Email NVARCHAR(100)
)
CREATE TABLE CustomerAddress (
AddressID INT PRIMARY KEY,
CustomerID INT,
AddressLine1 NVARCHAR(100),
AddressLine2 NVARCHAR(100),
City NVARCHAR(50),
State NVARCHAR(50),
ZipCode NVARCHAR(10),
CONSTRAINT FK_CustomerAddress_Customer
FOREIGN KEY (CustomerID)
REFERENCES Customer(CustomerID)
ON DELETE CASCADE
)One To Many
CREATE TABLE Customers (
CustomerID int PRIMARY KEY,
FirstName nvarchar(50),
LastName nvarchar(50),
Email nvarchar(50),
Phone nvarchar(20)
);
CREATE TABLE Orders (
OrderID int PRIMARY KEY,
OrderDate datetime,
CustomerID int,
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) ON DELETE CASCADE,
OrderTotal decimal(10,2)
);CREATE TABLE Department (
ID INT PRIMARY KEY,
Name VARCHAR(50)
);
CREATE TABLE Employee (
ID INT PRIMARY KEY,
Name VARCHAR(50),
DepartmentID INT,
CONSTRAINT FK_Employee_Department
FOREIGN KEY (DepartmentID)
REFERENCES Department(ID)
ON DELETE CASCADE
);除了在資料表建立的當下設定 CONSTRAINT & REFERENCES 外，也可以透過 ALTER TABLE 的方式設定。
ALTER TABLE Employee
ADD CONSTRAINT FK_Employee_Department
FOREIGN KEY (DepartmentID) REFERENCES Department(ID)
ON DELETE CASCADE;Many To Many
CREATE TABLE Products (
ProductID int PRIMARY KEY,
ProductName nvarchar(50)
);
CREATE TABLE Categories (
CategoryID int PRIMARY KEY,
CategoryName nvarchar(50)
);
CREATE TABLE ProductCategory (
ProductID int,
CategoryID int,
CONSTRAINT FK_ProductCategory_Products FOREIGN KEY (ProductID) REFERENCES Products(ProductID),
CONSTRAINT FK_ProductCategory_Categories FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID),
CONSTRAINT PK_ProductCategory PRIMARY KEY (ProductID, CategoryID)
);