1-- On Create
2CREATE TABLE tableName (
3 ID INT,
4 SomeEntityID INT,
5 PRIMARY KEY (ID),
6 FOREIGN KEY (SomeEntityID)
7 REFERENCES SomeEntityTable(ID)
8 ON DELETE CASCADE
9);
10
11-- On Alter, if the column already exists but has no FK
12ALTER TABLE
13 tableName
14ADD
15 FOREIGN KEY (SomeEntityID) REFERENCES SomeEntityTable(ID) ON DELETE CASCADE;
16
17 -- Add FK with a specific name
18 -- On Alter, if the column already exists but has no FK
19ALTER TABLE
20 tableName
21ADD CONSTRAINT fk_name
22 FOREIGN KEY (SomeEntityID) REFERENCES SomeEntityTable(ID) ON DELETE CASCADE;
1# A foreign key is essentially a reference to a primary
2# key in another table.
3
4# A Simple table of Users,
5CREATE TABLE users(
6 userId INT NOT NULL,
7 username VARCHAR(64) NOT NULL,
8 passwd VARCHAR(32) NOT NULL,
9 PRIMARY KEY(userId);
10);
11# Lets add a LEGIT user!
12INSERT INTO users VALUES(1000,"Terry","Teabagface$2");
13
14# We will create an order table that holds a reference
15# to an order made by our Terry
16CREATE TABLE orders(
17 orderId INT NOT NULL,
18 orderDescription VARCHAR(255),
19 ordererId INT NOT NULL,
20 PRIMARY KEY(orderId),
21 FOREIGN KEY (ordererId) REFERENCES users(userId)
22);
23# Now we can add an order from Terry
24INSERT INTO orders VALUES(0001,"Goat p0rn Weekly",1000);
25
26# Want to know more about the plight of Goats?
27# See the link below
1ALTER TABLE database.table
2 ADD COLUMN columnname INT DEFAULT(1),
3 ADD FOREIGN KEY fk_name(fk_column) REFERENCES reftable(refcolumn) ON DELETE CASCADE;