| ☰ See All Chapters |
Foreign key constraint in MySQL
A foreign key is a column in a child table that references a primary key in the parent table. A foreign key constraint is the main mechanism that enforces referential integrity between tables in a relational database. A column defined as foreign key references a column defined as a primary key in another table. To add foreign key there must be primary key constrain in parent table.
We can give different name for column in parent and in child table, but it is a convention to give same names for columns with PRIMARY KET and FOREIGN KEY.
The parent table is the CUSTOMER table, and the child table is the PHONE table. CUSTOMER_ID column of PHONE table references CUSTOMER_ID table of CUSTOMER table.
For a value to be inserted for CUSTOMER_ID in the child table, a value for CUSTOMER_ID in the parent table must exist.
For a value to be removed for CUSTOMER_ID in the parent table, all corresponding first values for CUSTOMER_ID must be removed from the child table.
For dropping parent table, first child table must be dropped.
Syntax to add Primary Key constraint in MySQL
------------------Syntax/Approach 1------------ CREATE TABLE <table-name> ( <column-name1> <data-type(size)> <constraint>, <column-name2> <data-type(size)> <constraint> );
ALTER TABLE <table-name> ADD ( CONSTRAINT <foreign-key name> FOREIGN KEY (<column-name>) REFERENCES <primary-key-table-name> (<column-name>) [ON DELETE CASCADE/ON UPDATE CASCADE] );
------------------Syntax/Approach 2------------
CREATE TABLE <table-name> ( <column-name1> <data-type(size)> <constraint>, <column-name2> <data-type(size)> <constraint>, CONSTRAINT <foreign-key name> FOREIGN KEY (<column-name>) REFERENCES <primary-key-table-name> (<column-name>) ); |
Example to add Primary Key constraint in MySQL
------Create Parent table with primary key-----------
CREATE TABLE CUSTOMER ( CUSTOMER_ID NUMERIC , PHONE_NO NUMERIC(10), EMAIL VARCHAR(30) , PRIMARY KEY (CUSTOMER_ID) );
------------------Syntax/Approach 1--------------- CREATE TABLE PHONE ( CUSTOMER_ID NUMERIC, PHONE_NO NUMERIC(10) );
ALTER TABLE PHONE ADD ( CONSTRAINT FK_PHONE FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER (CUSTOMER_ID) ON DELETE CASCADE );
------------------Syntax/Approach 2---------------
CREATE TABLE PHONE ( CUSTOMER_ID NUMERIC, PHONE_NO NUMERIC(10), CONSTRAINT FK_PHONE FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER (CUSTOMER_ID) );
|
All Chapters
