Foreign Key

[Unna]

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.

[gpsingh]

put the name of the column in place of ?????

[Unna]

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?

[Queyon]

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.

[gpsingh]

Use triggers to implement the referential integrity.

[chrisrlong]

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