mysql  foreign key constraint

a MySQL foreign key establises a relationship or constraint between two tables.

a MySQL foreign key is a field in a table that matches a field of another table.it is a referential constraint between two tables.

the foreign key identifies a column or a set of columns in one(referencing) table that refers to a column or set of columns in another(referenced) table.

a simple look of foreign key

the "users" table

useridfirstnamelastnamemobilenocountry
1sandeepkumar9549293245india
2ramkumar9549293212usa
3shyamdhaka2323234545jaipur

the "company" table:

C_idsalaryuserid
110,0002
234,0003
31,000003
420,0001

the "userid" column in the "company" table points to the "userid" column in the "users" table.

the "userid" column in the "users" table is the primary key in the 'users' table.

the "userid" column in the "company" table is a foreign key in the 'company' table.


mysql create foreign key syntax

creates a foreign key on the 'userid' column when the "company" table is created:

           create table    company
                         
                         (
                           c_id   int  not null,
                         
                         salary   float    not null,
                         
                         userid int,
                         
                         primary key (c_id),
                         
                         foreign key (userid)     references   users(userid)  
                         
                         );

foreign key constraint on alter table

           alter    table_name

                        add   constraint    constraint_name
                        
                        foreign key(columns)
                        
                        references      parent_table(columns)

to remove foreign key constraint

when you drop a table, any constraints that you defined for the table are also removed.

to remove a foreign key constraint, use the following mysql :

           alter   table    company

                       drop       foreign key userid