Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • What is FOREIGN KEY Constraint in MySQL?

    • 0
    • 1
    • 0
    • 1
    • 0
    • 0
    • 0
    • 0
    • 563
    Comment on it

    Note that the "P_Id" column in the "Orders" table points to the "P_Id" column in the "Persons" table.

    In MySQL, the FOREIGN KEY on one table is used to point a PRIMARY KEY in another table.

    We have two tables with the following values.

        user
    
    id  first_name                  last_name
    .......................................
    1   John                        Simp                 
    2   Chris                       Hely                   
    3   Joy                         Roy                      
    4   Jenny                       Mill                                           
    
        user_address
    
    d       place        user_id
    ............................
    1       Canada        
    2       Ottawa
    3       London
    5       India
    

    In the above tables "user_id" in the "user_address" table points to the "id" column in the "user" table, where the "id" column in the "user" table is the PRIMARY KEY in the "user" table and the "user_id" column in the "user_address" table is a FOREIGN KEY in the "user_address" table.

    We cannot insert invalid data into the foreign key column as the FOREIN KEY constraint prevents that because the data must be exist in the primary key column on the another table it points to.

    FOREIGN KEY Constraint on CREATE TABLE

    The following statement creates a FOREIGN KEY constraint on the "user_id" column when the "user_address" table is created:

    Example:

    CREATE TABLE user_address
    (
    id int NOT NULL,
    place int NOT NULL,
    user_id int,
    PRIMARY KEY (id),
    FOREIGN KEY (user_id) REFERENCES user(id)
    );
    

    We can also define FOREIGN KEY constraint on multiple column, for this use the below statement:

    CREATE TABLE user_address
    (
    id int NOT NULL,
    place int NOT NULL,
    user_id int,
    PRIMARY KEY (id),
    CONSTRAINT fk_user_address FOREIGN KEY (user_id) REFERENCES user(id)
    );
    

    FOREIGN KEY Constraint on ALTER TABLE

    We can also create FOREIGN KEY constraint on a column when the table is already created, for that we use ALTER command. The below statement creates a FOREIGN KEY constraint on the "user_id" column when the "user_address" table is already created

    ALTER TABLE user_address
    ADD FOREIGN KEY (user_id)
    REFERENCES user(id);
    

    Similarly we can define FOREIGN KEY constraint on multiple columns when the table is already created as below:

        ALTER TABLE employee
        ADD CONSTRAINT fk_user_address 
        FOREIGN KEY (user_id)
        REFERENCES user(id);
    

    To DROP a FOREIGN KEY Constraint

    We can also drop a FOREIGN KEY constraint. To drop a FOREIGN KEY constraint, use the below statement:

        ALTER TABLE Persons
        DROP FOREIGN KEY fk_user_address;
    

    Hope this will help you :)

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: