This discussion is archived
6 Replies Latest reply: Jan 8, 2013 4:02 AM by 983512 RSS

Primary key

$phinx19 Newbie
Currently Being Moderated
Hi All,

I have created a test table which has 4 columns :
SR_NO, FNAME, LNAME and GRADE.

The grade column has the same value repeating several times.

Then i plan to create a primary key on the 3 columns: FNAME, LNAME and GRADE.
alter table test_tab add constraint mv_testy_pk primary key (FNAME, lname, grade);
and oracle allows me but i need to know as the grade columns already has repeated values in it so what happens to the PK here.

BR
Sphinx
  • 1. Re: Primary key
    793965 Explorer
    Currently Being Moderated
    Read the doumentation to get the concept of primary key.

    When you are creating a composite primary key i.e primary key consists of more than one columns, then it will check whether the combination of datas in the three columns are unique or not. for example:

    lname: 1 Fname: abc grade: A
    lname: 2 Fname: def grade: A

    Here, though the grade is repeating, but the combination of three columns are unique...isnt it??


    thats why oracle allows you to create primary key.
  • 2. Re: Primary key
    Girish Sharma Guru
    Currently Being Moderated
    And if you are in search of documentation page for Primary Key here it is :

    http://docs.oracle.com/cd/E11882_01/server.112/e25789/datainte.htm#CHDDBJBB

    and it is saying :

    The Oracle Database implementation of the primary key constraint guarantees that the following statements are true:

    No two rows have duplicate values in the specified column or set of columns.

    The primary key columns do not allow nulls.

    Regards
    Girish Sharma
  • 3. Re: Primary key
    EdStevens Guru
    Currently Being Moderated
    $phinx19 wrote:
    Hi All,

    I have created a test table which has 4 columns :
    SR_NO, FNAME, LNAME and GRADE.

    The grade column has the same value repeating several times.
    Already a violation of normalization rules. A serious design flaw.

    >
    Then i plan to create a primary key on the 3 columns: FNAME, LNAME and GRADE.
    alter table test_tab add constraint mv_testy_pk primary key (FNAME, lname, grade);
    and oracle allows me but i need to know as the grade columns already has repeated values in it so what happens to the PK here.

    BR
    Sphinx
  • 4. Re: Primary key
    Mark D Powell Guru
    Currently Being Moderated
    BR, others have provided you with information on the PK but I have a few considerations for you to consider. The purpose of a PK is to uniquely identify a single specific row in a table. To do that you need a column or set of columns whose combined values do not repeat.

    You did not identify the usage of your table or the time period over which rows exist in it. A grade would seem to me to apply to a test score, class participation, an experiment, or completion of an assignment. Since a student could have several of these I question if 'grade' is the most appropriate choice of columns to be part of the PK. Perhaps you should instead include the date the grade was earned?

    My point is that the choice of appropriate PK columns depends on the data and the fact the data is properly normalized into tables with known relationships to each other.

    HTH -- Mark D Powell --
  • 5. Re: Primary key
    $phinx19 Newbie
    Currently Being Moderated
    Hi All,

    Thanks.

    BR
    Sphinx
  • 6. Re: Primary key
    983512 Newbie
    Currently Being Moderated
    Primary key =not null+ unique

    means if u create a primary constraint for a column in table,that columns should be insert a unique value else its give a error

    here u create a primary for 3 columns at a time ,it is called composite primary key constraints

    here if the data like fname lname grade
    bhaskr red A
    madhu sudan A

    it wont be give any error

    here if the data like fname lname grade
    bhaskr red A
    bhaskr red A

    it should be give error because we are applying constraing for 3 column here uniquly data will be match ,pk should be raised


    for u for 4 th column can not be happen ,ur wish,u can give no of duplicated data and null values for the 4 th columns but 1 st 3 columns we should be give diferen data else its problem to u
    by raising pk error


    NOTE:when we create primary key ,automatically index will be created

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points