Foreign Key
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Foreign Key

  1. #1
    Join Date
    Nov 2000
    Posts
    162
    Hi all, I am new to DBA, hope you can help me. Thanks!

    I try to create 2 tables as following,

    Create table Table1
    (
    constraint table1_col12_pk primary key (col1, col2),
    ad varchar2(10),
    col1 number,
    col2 varchar2(20)
    );


    Create table Table2
    (
    t_date date,
    col2 varchar2(20)
    constraint table2_col2_fk references table1(?????)
    );


    I try to set a foreign key, table2_col2_fk, references to table1_col12_pk. However, the primary key is a combination of columns, and the foreign key only need to link to col2 in Table1. How do I set this up? Do I need to add another column in Table2? If so, how is the foreign key beeing set up? Thanks for the help.



  2. #2
    Join Date
    Feb 2001
    Posts
    389
    put the name of the column in place of ?????

  3. #3
    Join Date
    Nov 2000
    Posts
    162
    Create table Table2
    (
    t_date date,
    col2 varchar2(20)
    constraint table2_col2_fk references table1(col2)
    );


    How is this fk references to the pk which contains two fields?


  4. #4

    Wink FOREIGN KEY

    Create table Table1
    (
    constraint table1_col12_pk primary key (col1, col2),
    ad varchar2(10),
    col1 number,
    col2 varchar2(20)
    );


    Create table Table2
    (
    t_date date,
    col2 varchar2(20)
    constraint table2_col2_fk references table1(?????)
    );

    You have 2 choices:

    1. Add another column "col1 number" into Table2 and "CONSTRAINT table2_col1_col2_fk FOREIGN KEY (col1, col2) REFERENCES table1"
    2. Change the primary key of table1 to "col2" only.

    Anyway, the combination of foreign key must be exactly same as the primary key.
    Queyon Zeng

  5. #5
    Join Date
    Feb 2001
    Posts
    389
    Use triggers to implement the referential integrity.

  6. #6
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Although I'm assuming you know this, I must comment nonetheless. DRI cannot do what you are asking because *you should not be doing* what you you are asking. Step back and re-look at your logical design and take another pass at normalization, because it appears you missed something.

    If you want, feel free to provide more information about your particular circumstance and maybe we can help you create a better design.

    - Chris

Posting Permissions

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


Click Here to Expand Forum to Full Width