alter foreign key

1ALTER TABLE orders
2ADD 
3FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE; 

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);

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);

1ALTER TABLE Employee
2ADD FOREIGN KEY (DeptNo) REFERENCES Department(DeptNo);
3

1Here is the basic syntax of defining a foreign key constraint in the CREATE TABLE or ALTER TABLE statement:
2
3[CONSTRAINT constraint_name]
4FOREIGN KEY [foreign_key_name] (column_name, ...)
5REFERENCES parent_table(colunm_name,...)
6[ON DELETE reference_option]
7[ON UPDATE reference_option]
8In this syntax:
9
10First, specify the name of foreign key constraint that you want to create after the CONSTRAINT keyword. If you omit the constraint name, MySQL automatically generates a name for the foreign key constraint.
11
12Second, specify a list of comma-separated foreign key columns after the FOREIGN KEY keywords. The foreign key name is also optional and is generated automatically if you skip it.
13
14Third, specify the parent table followed by a list of comma-separated columns to which the foreign key columns reference.
15
16Finally, specify how foreign key maintains the referential integrity between the child and parent tables by using the ON DELETE and ON UPDATE clauses.  The reference_option determines action which MySQL will take when values in the parent key columns are deleted (ON DELETE) or updated (ON UPDATE).
17
18MySQL has five reference options: CASCADE, SET NULL, NO ACTION, RESTRICT, and SET DEFAULT.
19
20CASCADE: if a row from the parent table is deleted or updated, the values of the matching rows in the child table automatically deleted or updated.
21SET NULL:  if a row from the parent table is deleted or updated, the values of the foreign key column (or columns) in the child table are set to NULL.
22RESTRICT:  if a row from the parent table has a matching row in the child table, MySQL rejects deleting or updating rows in the parent table.
23NO ACTION: is the same as RESTRICT.
24SET DEFAULT: is recognized by the MySQL parser. However, this action is rejected by both InnoDB and NDB tables.
25In fact, MySQL fully supports three actions: RESTRICT, CASCADE and SET NULL.
26
27If you don’t specify the ON DELETE and ON UPDATE clause, the default action is RESTRICT.
28
29MySQL FOREIGN KEY examples
30Let’s create a new database called fkdemo for the demonstration.
31
32CREATE DATABASE fkdemo;
33
34USE fkdemo;
35RESTRICT & NO ACTION actions
36Inside the fkdemo database, create two tables categories and products:
37
38CREATE TABLE categories(
39    categoryId INT AUTO_INCREMENT PRIMARY KEY,
40    categoryName VARCHAR(100) NOT NULL
41) ENGINE=INNODB;
42
43CREATE TABLE products(
44    productId INT AUTO_INCREMENT PRIMARY KEY,
45    productName varchar(100) not null,
46    categoryId INT,
47    CONSTRAINT fk_category
48    FOREIGN KEY (categoryId) 
49        REFERENCES categories(categoryId)
50) ENGINE=INNODB;
51The categoryId in the products table is the foreign key column that refers to the categoryId column in the  categories table.
52
53Because we don’t specify any ON UPDATE and ON DELETE clauses, the default action is RESTRICT for both update and delete operation.