Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • SQL Foreign Key

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 186
    Comment on it

    Foreign Key is used to establish a link between two tables . It always define in reference of Primary key ( another table ) . It act as a Primary key in its own table and foreign key for another table .

    Difference between Primary key and Foreign key in SQL :
    1) Primary key can't be null while Foreign key can be null .
    2) Primary key is always unique on other hand Foreign can be duplicate in a table .
    3) Primary key can onlye be one in a table while foreign key can be more than one or more .

    Example :
    First Table name : Employee_Info

      Id        Employee_name          Employee_Age               Employee _Salary
      1            Mukesh                  23                           100000
      2            Ayush                   24                           200000
      3            Ishan                   20                           400000
      4            Pranav                  35                           700000
      5            Abhishek                26                           800000
      6            Ravi                    25                           300000
      7            David                   40                           800000
    

    Second Table name : student

           Id    Student_name            Student_Age          Student_Id
           1        Mukesh                  23                    1
           3        Ayush                   24                    2
           4        Ishan                   20                    4
           16        Pranav                  35                   7
           15        Abhishek                26                   8
           12        Ravi                    25                   3
    

    Note : In Employee_Info table " Id " is a primary key and in student table " Id " act as a Foreign key for Employee_Info table and for own table it act as a Primary key

    Advantage :
    It prevent from the action that destroy the link between the tables i.e we can make a link between the tables .
    Syntax In MySQL for single column :

    create table Employee_Info ( Id int not null , Employee_Name not null , Employee_Age , Employee_Salary , Primary key ( Employee_Id ) , Foreign key ( Id ) references student ( Id ) ) ;
    

    Syntax In SQL/Oracle/MS Access for single column :

    create table Employee_Info ( Id int not null Primary key , Employee_Name not null , Employee_Age , Employee_Salary ,  Foreign key references student ( Id ) ) ;
    

    How to Alter Foreign key ?
    Syntax In MySQL/SQL/Oracle/MS Access for single column :

    Alter table Employee_Info add constraint fk_employeeId Foreign key ( Id ) references student ( Id ) ) ;
    

    How to Drop Foreign key ?
    Syntax In MySQL for single column :

    Alter table Employee_Info ROP Foreign key fk_employeeId  ;
    

    Syntax In SQL/Oracle/MS Access for single column :

    Alter table Employee_Info drop constraint fk_employeeId  ;
    

 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: