Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2003
    Posts
    2,296

    Unanswered: FK against non-PK??

    I assume this is not possible, but I need perhaps another solution.

    I have table_A with a PK of:
    Col_1
    Col_2
    Col_3


    I have Table_B with a PK of:
    Col_1
    Col_2
    Col_4
    Col_5

    I need a FK for Table_B that references Col_1, Col_2 against Table_A.
    Obviously, Oracle is not letting me do this since it keep saying that those two columns are not unique or a PK.

    Any suggestions?

    actual code:
    PHP Code:
    09:55:26 kod:integrationALTER TABLE FURNACE_EVENT ADD (
    09:55:27   2      FOREIGN KEY
    09
    :55:27   3         (ORG_IDSVC_TYPE_CDCUST_IDPREM_SEQ_NBRFURNACE_SEQ_NBR)
    09:55:27   4         REFERENCES FURNACE
    09
    :55:27   5         (ORG_IDSVC_TYPE_CDCUST_IDPREM_SEQ_NBRFURNACE_SEQ_NBR)) ;
           (
    ORG_IDSVC_TYPE_CDCUST_IDPREM_SEQ_NBRFURNACE_SEQ_NBR))
            *
    ERROR at line 5:
    ORA-02270no matching unique or primary key for this column-list


    Elapsed00:00:00.00
    09
    :55:27 kod:integration
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  2. #2
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    Yes, You have 2 options.

    (1) Create another table C such that C(Col1, Col2) which contains Distinct value so that you can create PK and referece it into Table B.

    or

    (2) Create a Trigger that satisfy your requirement. You need atleast two triggers, one on table A and another on table B which functions like a FK-PK relationship between these two tables.
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  3. #3
    Join Date
    Nov 2003
    Location
    Rotterdam, Netherlands
    Posts
    127
    Hello Mr Duck,
    Like you said it is not possible to set up like this,

    But, logically if you are relating the tables, TABLE_A (col1 , col2) should be unique.

    Anyways, i don't know the functional constraints!!!

    One suggestion could be that, A BEFORE INSERT trigger could be written on TABLE_B to check the validity of the data OR the validity should be checked from the front end.

    Regards

Posting Permissions

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