Example 1: mysql add foreign key
-- On Create
CREATE TABLE tableName (
ID INT,
SomeEntityID INT,
PRIMARY KEY (ID),
FOREIGN KEY (SomeEntityID)
REFERENCES SomeEntityTable(ID)
ON DELETE CASCADE
);
-- On Alter, if the column already exists but has no FK
ALTER TABLE
tableName
ADD
FOREIGN KEY (SomeEntityID) REFERENCES SomeEntityTable(ID) ON DELETE CASCADE;
-- Add FK with a specific name
-- On Alter, if the column already exists but has no FK
ALTER TABLE
tableName
ADD CONSTRAINT fk_name
FOREIGN KEY (SomeEntityID) REFERENCES SomeEntityTable(ID) ON DELETE CASCADE;
Example 2: sql foreign key
CREATE TABLE users(
userId INT NOT NULL,
username VARCHAR(64) NOT NULL,
passwd VARCHAR(32) NOT NULL,
PRIMARY KEY(userId);
);
INSERT INTO users VALUES(1000,"Terry","Teabagface$2");
CREATE TABLE orders(
orderId INT NOT NULL,
orderDescription VARCHAR(255),
ordererId INT NOT NULL,
PRIMARY KEY(orderId),
FOREIGN KEY (ordererId) REFERENCES users(userId)
);
INSERT INTO orders VALUES(0001,"Goat p0rn Weekly",1000);
Example 3: sql foreign key
CREATE TABLE orders (
id int NOT NULL,
user_id int,
product_id int,
PRIMARY KEY (id),
FOREIGN KEY (user_id) REFERENCES users(id),
FOREIGN KEY (product_id) REFERENCES products(id)
);
Example 4: foreign key mssql
CREATE TABLE Sales.TempSalesReason
(
TempID int NOT NULL, Name nvarchar(50)
, CONSTRAINT PK_TempSales PRIMARY KEY NONCLUSTERED (TempID)
, CONSTRAINT FK_TempSales_SalesReason FOREIGN KEY (TempID)
REFERENCES Sales.SalesReason (SalesReasonID)
ON DELETE CASCADE
ON UPDATE CASCADE
)
;
Example 5: how to set foreign key in sql server
CREATE TABLE Orders (
OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);