Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2004
    Posts
    105

    Unhappy Unanswered: error on building constraints :(

    hi everybody i have a question
    i did build my tables in a oracle database and now started to add constraints
    i have some tables who have more than one primarykey in it
    so if i for example have a table with the colums a and b and both have a primary key
    Now if i create a constraint by doing it on a and b and relate it to a table2.a table2.b then it works fine
    but i also have to do a constraint on only table1.a and relate it to table3.a
    if i try that i get a errormessage saying:

    ORA-02270: no matching unique or primary key for this column-list

    does somebody know whats wrong here and how i can solve that problem?

    thank you so much

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I think you mixed things up ... one table can have only one primary key (that's why it is called "primary"). BUT, the primary key can contain one or several columns.
    Your example would show this:
    Code:
    SQL> create table my_table
      2  (a number(2),
      3   b number(2));
    
    Table created.
    
    SQL> alter table my_table add constraint c1 primary key (a);
    
    Table altered.
    
    SQL> alter table my_table add constraint c1 primary key (b);
    alter table my_table add constraint c1 primary key (b)
                                           *
    ERROR at line 1:
    ORA-02260: table can have only one primary key
    
    
    SQL> alter table my_table drop constraint c1;
    
    Table altered.
    
    SQL> alter table my_table add constraint c1 primary key (a, b);
    
    Table altered.
    
    SQL> insert into my_table (a, b) values (1, 2);
    
    1 row created.
    
    SQL> insert into my_table (a, b) values (1, 2);
    insert into my_table (a, b) values (1, 2)
    *
    ERROR at line 1:
    ORA-00001: unique constraint (PLSQL.C1) violated
    Everything's just like it should be.

    Now, you have another table and want to relate those two:
    Code:
    SQL> create table your_table
      2  (a number(2),
      3   b number(2));
    
    Table created.
    
    SQL> alter table your_table add constraint fk1 foreign key (a, b)
      2  references my_table (a, b);
    
    Table altered.
    That's OK too.

    But, if you try to relate only columns "a", it won't work:
    Code:
    SQL> alter table your_table drop constraint fk1;
    
    Table altered.
    
    SQL> alter table your_table add constraint fk2 foreign key (a)
      2  references my_table (a);
    references my_table (a)
                         *
    ERROR at line 2:
    ORA-02270: no matching unique or primary key for this column-list
    That's OK too ... you simply can NOT relate only a part of a primary key.

    Read more about foreign keys here, and also check "sticky" topic on this forum; it contains many useful links.

  3. #3
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    Read this link:

    http://www.techonthenet.com/oracle/primary_keys.htm

    & pay attention to the bit that says:

    "A primary key is a single field or combination of fields that uniquely defines a record. None of the fields that are part of the primary key can contain a null value. A table can have only one primary key."

    Therefore you cannot have a table with more than one primary key in it, although you can have a table where more than one col is used to make up the primary key (is this what you meant to say?).
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  4. #4
    Join Date
    Oct 2004
    Posts
    105
    Therefore you cannot have a table with more than one primary key in it, although you can have a table where more than one col is used to make up the primary key (is this what you meant to say?).
    __________________
    yes i think thats the thing here i have the database originalli in access and there i can make a primary key of more columns by adding a key to each of the colums i need
    how do i do that in oracle then?
    thank you

  5. #5
    Join Date
    Oct 2004
    Posts
    105
    oh so even if i have a key thats made out of more columns, i can not relate
    only a part of a primary key to another key?
    do i have to do that by adding another table? because i would need such a thing

  6. #6
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    You can't just add columns to the primary key in Oracle (unfortunately!) - if you look at the link I posted in my previous reply, you'll see that the way to get round this is to drop the existing primary key & then create a new primary key (examples are included in the link).
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  7. #7
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    Also, if you stop & think about it, if your primary key is made up of, for example 1st name & surname, you can't then just relate to 1st name - consider that you have a table full of people called either "Joe" or "Fred", with surnames of either "Smith", "Jones", or "Baggins". You can't find the record you want ("Joe Baggins") just by looking for "Joe", nor can you do it just by looking for "Baggins"; you have to use both parts of the key. If you want to look at only part of the key, which is what you are saying, then I think you might want to sit down & look at the design of the database, as you may be using the wrong column/s for the primary key.
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  8. #8
    Join Date
    Mar 2004
    Posts
    370
    You can not ADD any other column to PK because a PK is minimal by definitoin.Refer to the definiton of "Candidate Key","Super Key","Primary key" in the references like C.J.Dates book.
    If you can add a column to a PK it implicitly means that you did not have a PK before!
    Last edited by Ach; 01-26-05 at 07:04.

  9. #9
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    I think what you want to do is define one primary key and then setup a unique index on the other "primary key field".
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •