MySQL FOREIGN KEY Constraint
In MySQL, FOREIGN KEY constraint is used to link-up two or more tables. The table in which the foreign key is defined is called the "child table" and it (often) refers to the primary key in the parent table.
MySQL FOREIGN KEY Constraint example
The following MySQL, creates a FOREIGN KEY constraint on the "bankId" column when the "tblbankdemataccountcharges" table is created:
Example
//Primary key table
CREATE TABLE tblbankheadquartersandaddress (
pk int NOT NULL PRIMARY KEY,
firstName varchar(255) NOT NULL,
lastName varchar(255) NOT NULL,
address varchar(255) NOT NULL,
);
//Foreign key table
CREATE TABLE tblbankdemataccountcharges (
bankId int NOT NULL,
firstName varchar(255) NOT NULL,
lastName varchar(255) NOT NULL,
address varchar(255) NOT NULL,
FOREIGN KEY (bankId) REFERENCES tblbankheadquartersandaddress(pk)
);
The "pk" column in the "tblbankheadquartersandaddress" table is the PRIMARY KEY in the "tblbankheadquartersandaddress" table.
The "bankId" column in the "tblbankdemataccountcharges" table is a FOREIGN KEY in the "tblbankdemataccountcharges" table.
MySQL FOREIGN KEY Constraint example on ALTER table
The following MySQL, creates a
FOREIGN KEY constraint on the "custId" columns when the "tblbankdemataccountcharges" table already exists:
Example
ALTER TABLE tblbankdemataccountcharges
ADD FOREIGN KEY (custId) REFERENCES tblbankheadquartersandaddress(pk);
MySQL DROP a FOREIGN KEY Constraint example
The following MySQL, dropping
FOREIGN KEY constraint "FK_BankID" from "tblbankdemataccountcharges" table:
Example
ALTER TABLE tblbankdemataccountcharges
DROP FOREIGN KEY FK_BankID;