Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2008
    Posts
    6

    Unanswered: Primary Key from foreign keys

    I have a table which has 3 foreign keys, two of which form the primary key.
    I have: ID_Product, ID_Sale, ID_Buy, and also an attribute which tells if its a buy or a sale.
    So, if a sale is made, the primry key should consist of ID_Product && ID_Sale, otherwise, it's ID_Product && ID_Buy.
    Is there a way to do this in Oracle, and if there is, can you point me to where I can read about this?
    thank you in advance

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    One table - one primary key. So - you're out of luck.

    However, why do you need both ID_SALE and ID_BUY when there's another attribute which determines whether it is a sale or a buy? Why wouldn't you get rid of one of them (so that you'd have a primary key consisted of ID_PRODUCT + ID_SALE_BUY)?

    Or, if you still need three ID columns, why wouldn't this primary key be composed of all three columns? Nothing references these columns (but they reference other primary keys; at least, that's what I understood). If row that has ID_SALE filled with a value and, at the same time ID_BUY is empty (i.e. NULL), primary key is out of question - but you'd probably be able to create UNIQUE key instead.

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    No, you can't do that. A primary key must consist of a fixed set of NOT NULL columns.

    You could use a UNIQUE constraint like this:
    Code:
    alter table t1 add constraint t1_u unique (id_Product, id_sale, id_buy);
    You then also need this check constraint:
    Code:
    alter table t1 add constraint t1_chk check 
    ( (id_sale is null and id_buy is not null)
    or (id_sale is not null and id_buy is null)
    );
    Otherwise you could create rows with id_sale and id_buy both null or both not null.

    You might want a "surrogate" primary key as well, e.g. if this table is referenced by the foreign keys of other tables:
    Code:
    alter table t1 add
    (t1_id integer,
     constraint t1_pk primary key(t1_id)
    );

  4. #4
    Join Date
    Apr 2008
    Posts
    6
    Thanks guys.
    I separated the table into two columns as it looks much easier.
    thanks for the lines

Posting Permissions

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