Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004

    Red face Unanswered: how to reference part of a primary key

    there is a table named table1 with these columns:
    id_1 number(10)
    id_2 number(10)
    status varchar2(40)

    the primary key of this table are id_1 and id_2,

    the table2 has these columns:
    id number(10)
    id_1 number(10)
    exp_date date

    the primary key of this table are id and id_1
    the foreign key is id_1

    The table1 has been created successfully,
    but there is a problem when I created the table2.
    The way I created it is:

    create table table2(
    id number(10),
    id_1 number(10) constraint table2_id_1_fk references table1(id_1),
    exp_date date,
    constraint table2_id_id_1_pk primary key(id,id_1));

    It seems the reference column "id_1" is part of the primary key of table1, so it doesn't match. Do anyone know how to fix it? I appreciated it!

  2. #2
    Join Date
    Jan 2004
    Croatia, Europe
    Provided Answers: 5
    I believe that columns contained in a foreign key MUST match those in primary key.

    Perhaps you could drop primary key on Table_1 and recreate it so that it contains ONLY id_1.
    To ensure uniqueness of combination (id_1, id_2) in table_1, you could create UNIQUE INDEX on those columns and make them NOT NULL (because unique indexes allow nulls, while primary keys don't).

    Doing so, you'll have primary key on table_1(id_1) which can be used to enforce referential integrity of table_2(id_1).

Posting Permissions

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