| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Help: Cross-Referenced Foreign Keys
A copy of this was sent to c_price_at_interaccess.com (Chuck Price)
(if that email address didn't require changing)
On Mon, 14 Dec 1998 14:31:25 GMT, you wrote:
>On 13 Dec 1998 23:02:11 GMT, markp28665_at_aol.com (MarkP28665) wrote:
>
>>Chuck is having trouble trying to create two tables each of which has a foreign
>>key, FK, reference to the other. Your problem is that you are trying to create
>>a FK reference to a non-existent table so create the tables first and then use
>>the alter table statement to add the FK's.
>>
>>Please check the SQL manual for the syntax but it is something like:
>>alter table table_1
>> add constraint foreign_key_name
>> references talbe_2(col1,col2,...N) ;
>
>Thanks. I had tried this previously -- and again to be sure, but this
>is an invaild alter table option. The "Complete Reference" offers no
>example for adding a FK constraint -- only check constraints.
>
>I suppose an update trigger could be added in place of one of the FK
>restraints, but that doesn't seem like the most elegant solution.
Declaritive constraints, including foreign keys, may be altered in after the fact. Here is an example:
SQL> create table t1 ( x int ); Table created.
SQL> create table t2 ( y int );
Table created.
SQL> alter table t1 add constraint t1_pk primary key(x); Table altered.
SQL> alter table t2 add constraint t2_pk primary key(y); Table altered.
SQL> alter table t1 add constraint t1_ref_t2 foreign key(x) references t2(y); Table altered.
SQL> alter table t2 add constraint t2_ref_t1 foreign key(y) references t1(x); Table altered.
You are right that there isn't a direct example of adding a foreign key via the alter table command outright in the book. Rather, the SQL ref manual shows the wire diagram for:
alter table add ( column_constraint );
And then refers you to subsequent documentation on how to specify a column_constraint. The docs on column_constraint show the "constraint <constraint_name> foreign key(<column_list>) references <another_table>(<column_list>); example with foreign keys.
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Mon Dec 14 1998 - 12:11:02 CST
 |
 |