6 Replies Latest reply on Jan 8, 2013 12:02 PM by 983512

    Primary key

    $phinx19
      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
          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
            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
              $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
                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
                  Hi All,

                  Thanks.

                  BR
                  Sphinx
                  • 6. Re: Primary key
                    983512
                    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