This discussion is archived
14 Replies Latest reply: Feb 11, 2013 1:52 AM by sql_coder RSS

Foreign Keys

792290 Newbie
Currently Being Moderated
Hello,

I am trying to create forign keys between the following tables and i get errors. here is the scenario:

create table test_a (SID varchar2(20) not null,
pid varchar2(20) not null,
p_code varchar2(3),
p_desc varchar2(2000));

the primary key is a combination of sid, pid at the existing table.

existing index at this table as follows:
create unique index pk_test_a on pk_test_a (sid, pid)
logging
tablespace test_index
pctfree 10
initrans 2
maxtrans 255
storage (initial 1M
next 1M
minextents 1
maxextents unlimited
pctincrease 0
bufffer_pool default)
no parallel;



and second table

create table test_b (SID varchar2(20) not null,
eid varchar2(20) not null,
p_summary varchar2(4000));

in this table sid, eid are the combination of the primary keys.

existing index as follows:
create unique index pk_test_b on pk_test_b (sid,eid)
logging
tablespace test_index
pctfree 10
initrans 2
maxtrans 255
storage (initial 1M
next 1M
minextents 1
maxextents unlimited
pctincrease 0
bufffer_pool default)
no parallel;

my question is i am trying to create the foreign keys between test_a and test_b, but i am unsuccessful.. I get the primary key columns does not match ora-02270 etc..

I have tables like TEST_B another 6 with same combination of the primary keys with unique index.

I have to create foreign keys between TEST_A and TEST_B and and rest of the other tables. but master table would be TEST_A.

any help is appreciated.
  • 1. Re: Foreign Keys
    sql_coder Newbie
    Currently Being Moderated
    I dont see any PRIMARY KEY, just a UNQIUE key and that is not the same. Drop the UNIQUE Index, create a real PRIMARY KEY constraint (implicit unique index or use the existing index) and try to create the foreign key again....

    Ikrischer
  • 2. Re: Foreign Keys
    krissco Newbie
    Currently Being Moderated
    drop index pk_test_a;
    alter table test_a add constraint test_a_pk primary key (sid, pid);
    alter table test_b add constraint test_b_fk foreign key (sid, eid) references test_a (sid, pid);
    An index does not a primary key make. :)
    The NOT NULL specification on your test_a columns is redundant.

    Also, you can format your posts using "
    " blocks for better readability.
    
    One more thought. I realize this is just a test case, but if your real data looks like that then relationally, you (probably) only have one table - not two. I'm making a few assumptions in saying that.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
  • 3. Re: Foreign Keys
    792290 Newbie
    Currently Being Moderated
    as you suggested i dropped the unique indexes and created 1 PK on test_a table. but the test_b table's is combination with sid, eid columns make unique. so i cannot create a foreign key on test_b table. how i can accomplish this? This was the reason why i created unique indexes. but now i have to create primary and foreign keys on these tables.

    anyhelp?
  • 4. Re: Foreign Keys
    John Spencer Oracle ACE
    Currently Being Moderated
    Primary keys versus unique indexes have nothing to do with the problem. All that is required for an FK is declared uniqueness in the parent table, so a PK, unique constraint or simply a unique index is sufficient. However, given that the unique key on test_a is sid and pid any foreign key pointing to that table must have both columns in it. That is, you cannot create an FK constraint on a single column of a multi-column unique key.

    Perhaps if you could explain what the business relationship between the two tables is, someone would be able to help wqith a solution. as it stands, I do not see any real relationship between them.

    John
  • 5. Re: Foreign Keys
    MLBrown Journeyer
    Currently Being Moderated
    I think you are saying that you want to create a unique key not a unique index... is that right?

    If you were trying to create a unique key that would allow you to later create foreign keys against that table you use the following syntax:
    ALTER TABLE test_b add constraint test_b_uk UNIQUE (sid, eid);
  • 6. Re: Foreign Keys
    William Robertson Oracle ACE
    Currently Being Moderated
    This seems to do it (expanding on krissco's example but showing a unique key on a non-unique index in case you have some objection to PKs that I'm not seeing - but a regular PK will do just as well):
    create table test_a
    ( sid varchar2(20) not null
    , pid varchar2(20) not null
    , p_code varchar2(3)
    , p_desc varchar2(2000) );
    
    create index test_a_uix on test_a (sid, pid);
    alter table test_a add constraint test_a_uk unique(sid, pid);
    
    create table test_b
    ( sid varchar2(20) not null
    , eid varchar2(20) not null
    , p_summary varchar2(4000) );
    
    alter table test_b add constraint test_b_a_fk foreign key (sid, eid) references test_a (sid, pid);
    I didn't understand the part about uniqueness on table_b though. If you want a unique key on table_b, just go ahead and add one.
  • 7. Re: Foreign Keys
    792290 Newbie
    Currently Being Moderated
    let me give how the dataflow between these tables.

    TEST_A

    sid pid p_code p_desc
    1 1001 a test
    2 1002 b test
    3 1001 c test

    TEST_B

    sid eid p_summary
    1 201 testing
    1 202 .....
    2 203 test


    and with the rest of the tables also the dataflow like test_B TABLE ONLY. so how do i create the kind of PK->FK relation?
  • 8. Re: Foreign Keys
    William Robertson Oracle ACE
    Currently Being Moderated
    In that example there are no common keys between the two tables - the combination {1, 201} doesn't appear in table_a, and so on. So, the tables are logically unrelated and you can't have a foreign key between them.

    Is there another table whose PK is just sid, though? That would be the parent to both.

    btw you can use
     tags to make your code examples monospaced with random words highlighted.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
  • 9. Re: Foreign Keys
    MLBrown Journeyer
    Currently Being Moderated
    There were some other posts and they were right - what you are doing doesn't work because your data doesn't match . You cannot link EID to PID if they are not the same thing. It looks like you need to just create an primary key on the TEST_A table against the SID column and a FOREIGN_KEY on the TEST_B table that references TEST_A based on the SID column:
    create table test_a 
    (SID    varchar2(20) not null,
     pid    varchar2(20) not null,
     p_code varchar2(3),
     p_desc varchar2(2000));
     
    ALTER TABLE test_a ADD CONSTRAINT test_a_pk PRIMARY KEY (sid);
    
    create table test_b 
    (SID       varchar2(20) not null,
     eid       varchar2(20) not null,
     p_summary varchar2(4000));
     
    ALTER TABLE test_b
    ADD CONSTRAINT test_b_a_fk FOREIGN KEY (sid)
    REFERENCES test_a (sid);
    Once you do that then you will have created a Parent-Child relationshilp between TEST_A and TEST_B. If you need to create children under TEST_B, then you would do the same thing and create either a PRIMARY KEY or a UNIQUE KEY (based on the syntax in the posts your were given) on TEST_B, then you would reference them the same way as you referenced B to A.
  • 10. Re: Foreign Keys
    792290 Newbie
    Currently Being Moderated
    no. only table test_a has SID unique value. rest of all the tables have SID can have many values but with a combination of SID and EID they will be identified as unique on test_b and other tables.
  • 11. Re: Foreign Keys
    mtefft Journeyer
    Currently Being Moderated
    If test_a is unique by SID, then you should not do this:
    create unique index pk_test_a on test_a (sid, pid) ;
    You should instead do something like this:
    alter table test_a add constraint pk_test_a primary key(sid);
  • 12. Re: Foreign Keys
    sql_coder Newbie
    Currently Being Moderated
    John Spencer wrote:
    That is, you cannot create an FK constraint on a single column of a multi-column unique key.
    Not sure if I understand you correct, but there can be a combined UNIQUE KEY and you can still make a foreign key constraint on one of the columns aslong the single column is UNIQUE as well.
    CREATE TABLE t1 (c1 NUMBER PRIMARY KEY, c2 NUMBER, UNIQUE (c1, c2));
    CREATE TABLE t2 (c1 NUMBER, c2 NUMBER, PRIMARY KEY(c1, c2));
    
    ALTER TABLE t2 ADD FOREIGN KEY (c1) REFERENCES t1(c1);
    Ikrischer
  • 13. Re: Foreign Keys
    John Spencer Oracle ACE
    Currently Being Moderated
    Ikrischer wrote:
    John Spencer wrote:
    That is, you cannot create an FK constraint on a single column of a multi-column unique key.
    Not sure if I understand you correct, but there can be a combined UNIQUE KEY and you can still make a foreign key constraint on one of the columns aslong the single column is UNIQUE as well.
    CREATE TABLE t1 (c1 NUMBER PRIMARY KEY, c2 NUMBER, UNIQUE (c1, c2));
    CREATE TABLE t2 (c1 NUMBER, c2 NUMBER, PRIMARY KEY(c1, c2));
    
    ALTER TABLE t2 ADD FOREIGN KEY (c1) REFERENCES t1(c1);
    Ikrischer
    Yes, you can do what you posted, but you are not creating a single column FK on on a multi-column unique key. The FK will be created against the PK on c1, not on the unique key (c1, c2).

    The OP stated they had a multicolumn PK and wanted to enforce an FK on only the leading column of the PK. That cannot be done. They only mentioned (possibly because the whole thing confuses me) that sid was unique about an hour after I posted my comments.

    John
  • 14. Re: Foreign Keys
    sql_coder Newbie
    Currently Being Moderated
    John Spencer wrote:
    The OP stated they had a multicolumn PK and wanted to enforce an FK on only the leading column of the PK. That cannot be done. They only mentioned (possibly because the whole thing confuses me) that sid was unique about an hour after I posted my comments.
    That is a very technical point of view. Actually I would prefer to come from the business logic side. So no matter which primary or unique constraints already exists, that reference can still be possible, aslong it makes sense. I would never reference to a constraint, but to another column(s). And if the business logic is satisfied, after that I would start to handle the technical side also keeping in mind, that different dbms may have different technical needs.

    Ikrischer

Legend

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