foreign key constraint

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;

1ALTER TABLE Orders
2ADD CONSTRAINT FK_PersonOrder
3FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

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

1A FOREIGN KEY is a key used to link two tables together.
2A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table.
3The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent table.
4
5Example:
6# creating table users
7CREATE TABLE users(
8	user_id INT NOT NULL,
9  	user_name VARCHAR(64) NOT NULL,
10  	user_pass VARCHAR(32) NOT NULL,
11  	PRIMARY KEY(user_id);
12);
13# adding user data
14INSERT INTO users VALUES(1,"Raj","raj@123");
15
16# creating table orders
17CREATE TABLE orders(
18	order_id INT NOT NULL,
19  	order_description VARCHAR(255),
20  	orderer_id INT NOT NULL,
21  	PRIMARY KEY(order_id),
22  	FOREIGN KEY (orderer_id) REFERENCES users(user_id)
23);
24# adding order data
25INSERT INTO orders VALUES(1,"Daily groceries",1);

1CREATE TABLE orders (
2id int NOT NULL,
3user_id int,
4product_id int,
5PRIMARY KEY (id),
6FOREIGN KEY (user_id) REFERENCES users(id),
7FOREIGN KEY (product_id) REFERENCES products(id)
8);

1CREATE TABLE Students ( 	 /* Create table with foreign key - Way 1 */
2    ID INT NOT NULL
3    Name VARCHAR(255)
4    LibraryID INT
5    PRIMARY KEY (ID)
6    FOREIGN KEY (Library_ID) REFERENCES Library(LibraryID)
7);
8
9CREATE TABLE Students ( 	 /* Create table with foreign key - Way 2 */
10    ID INT NOT NULL PRIMARY KEY
11    Name VARCHAR(255)
12    LibraryID INT FOREIGN KEY (Library_ID) REFERENCES Library(LibraryID)
13);
14
15
16ALTER TABLE Students 	 /* Add a new foreign key */
17ADD FOREIGN KEY (LibraryID)
18REFERENCES Library (LibraryID);
19