Foreign Key
A Foreign Key is one table point to a PRIMARY KEY in another table.
Foreign Key is used to describe the column of a given table on the column of another table.
Foreign key can be used to make confirm that the row in one table have corresponding row in another table.
Example : Let's see how to create Foreign key in table, we have two tables- employee and department.
Query :
Output :
CREATE TABLE department
(
did int(10) NOT NULL PRIMARY KEY,
dname varchar(50),
empid int(10) NOT NULL,
CONSTRAINT employee_eid_fk
FOREIGN KEY(empid) REFERENCES employee(eid)
);
In this output, we have employee table eid is the primary key which is set as foreign key in department.
Add Foreign Key Constraint
For example if a FOREIGN key does not go to the customer and wants to give it separately then it is used with the ALTER TABLE statement.
Query :
Output :
ALTER TABLE department
(
ADD COLUMN empid int(10) NOT NULL PRIMARY KEY,
ADD CONSTRAINT employee_eid_fk
FOREIGN KEY(empid) REFERENCES employee(eid)
);
Now, you can used sql query and see the FOREIGN KEY in Table.
Query :
Output :
SELECT * from INFORMATION_SCHEMA. TABLE_CONSTRAINTS
WHERE TABLE_NAME = 'department';
0 Comments