locked
Two Primary key for table??? RRS feed

  • Question

  • i HAVE FOLLOWING SCRIPT TO CREATE TABLE.

    create table Grade_report (STUDENT_NUMBER SMALLINT NOT NULL, SECTION_ID SMALLINT NOT NULL, GRADE CHAR(1),

    CONSTRAINT stno_secid PRIMARY KEY (STUDENT_NUMBER, SECTION_ID))

    tHE BOLD line shows that it has two primary key. would that be possible to create two primary key for a single table??  If not then what does it for?? may be unique key???

    anyone can explain me please.

     

     

    Thanks,

     


     

     

    Sunday, September 12, 2010 4:44 PM

Answers

  • U cannot create 2 primary keys for one table. But you can create a primary key on 1 or more number of columns. This is called composite primary key.

     

    Once, you create a primary key on 2 columns, the combination of that columns will be treated as a primary key.

    see this small example. first insert one row with studentNumber and SectionId as 1,1

    insert into Grade_report values (1,1,'A')

    Now try to insert one more record with same student number and section id , but with different grade. 

    insert into Grade_report values (1,1,'B')

     

    When you run above statement, u will get an error. 

    Now try to insert same student number, but different section id.

    insert into Grade_report values (1,2,'B')   

    when you run above statement, u won't get any error.

    So, it means, When you create a primary key on 2 cols, it will ensure the same comination will not come again..

     

    A Primary key is the combination of One or more Columns, which uniquely identifies the rows.  Observe the word "one or more columns". That is the key.

    • Proposed as answer by Naomi N Sunday, September 12, 2010 9:04 PM
    • Marked as answer by Incredible15 Monday, September 13, 2010 10:37 AM
    Sunday, September 12, 2010 4:54 PM

All replies

  • U cannot create 2 primary keys for one table. But you can create a primary key on 1 or more number of columns. This is called composite primary key.

     

    Once, you create a primary key on 2 columns, the combination of that columns will be treated as a primary key.

    see this small example. first insert one row with studentNumber and SectionId as 1,1

    insert into Grade_report values (1,1,'A')

    Now try to insert one more record with same student number and section id , but with different grade. 

    insert into Grade_report values (1,1,'B')

     

    When you run above statement, u will get an error. 

    Now try to insert same student number, but different section id.

    insert into Grade_report values (1,2,'B')   

    when you run above statement, u won't get any error.

    So, it means, When you create a primary key on 2 cols, it will ensure the same comination will not come again..

     

    A Primary key is the combination of One or more Columns, which uniquely identifies the rows.  Observe the word "one or more columns". That is the key.

    • Proposed as answer by Naomi N Sunday, September 12, 2010 9:04 PM
    • Marked as answer by Incredible15 Monday, September 13, 2010 10:37 AM
    Sunday, September 12, 2010 4:54 PM
  • > tHE BOLD line shows that it has two primary key

    The bold line shows that you have a primary key composed by two columns. As ramireddy stated, this is known as a composite priamry key.

    If you have more than one candidate key in your table, you need to pick one as the primary key. You can also use a unique constraint or a unique index to force uniqueness on the rest of candidate keys. This is the approach used when you have a surrogate key that is used as the primary key, using a unique index or constraint to force uniqueness on the natural key.

     


    AMB

    Some guidelines for posting questions...

    Sunday, September 12, 2010 5:06 PM