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

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 183
    Comment on it

    Sometime in a table we need two or more column to uniquely identify each row . Composite key is the combination of two or more Primary key . Primary key or candidate key come from the composite key .

    Syntax in SQL :

    create table table_name col_name1 data_type1 , col_name1 data_type2 , ???? Primary key ( col_name1 , col_name2 , ?.));
    

    Syntax in MySQL :

    create table table_name col_name1 data_type1 , col_name1 data_type2 , ......... Primary key ( col_name1 , col_name2));
    

    Example :
    Table name : student

    Name        Branch        Section         Roll_Id
    Mukesh        CS               A            15
    Ravi          EE               A             2
    Ayush         ME               B            15
    

    Second table : student_report

    Branch      Section           Roll_Id        Grade     Semester    Attendance 
    CS              A               15            A++       1st           90
    EE              A                2            A+        2nd           70
    CS              B               15            AA        1st           85
    EE              A                2            A+        1st           70 
    CS              A               15            AA        2nd           85
    

    Note :
    class , section , and roll_id combination is primary key in student table .
    class , section , and roll_id combination is foreign key in student_report table and this act as a primary key in student table .
    class , section , and roll_id of student table must be same as student_report table .
    Query :

    select a.name , a.class , a.section , a.roll_id , b.grade , b.semester from student a , student_report b where a.class = b.class and a.section = b.section and a.roll_id = b.roll_id ;
    

    Output :

    Name      Class      Section      Roll_Id          Grade           Semester
    Ravi       EE           A           2                A+               2nd
    Ayush      CS           B          15               AA                1st 
    Ravi       EE           A           2               A+                1st
    Mukesh     CS           A          15               AA                2nd
    Mukesh     CS           A          15               A++               1st
    

 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: