| Create relationship with a table...... [message #371307] |
Mon, 02 October 2000 08:41  |
Samuel
Messages: 17
Registered: September 2000
|
Junior Member |
|
|
Hi,
i have two tables, the first have two fields that i did must declare: alter table tab1 add (constraint pk_tab1 primary key (x,y));
the second table had three fields, the first is a primary key and the second is a foreign key: i must declare so: alter table tab2 add (constraint fk_tab2 foreign key (tab2) references tab1(x,y));
But this give me an error, i tried: tab1(x), but error;
tab1(y), but error; tab1(fuck you), but i didn't press return :)
how can I do?
|
|
|
|
| Re: Create relationship with a table...... [message #371308 is a reply to message #371307] |
Mon, 02 October 2000 10:02  |
Prem
Messages: 79
Registered: August 1998
|
Member |
|
|
Samual,
Since the first table has a composite primary key i.e. two keys together form the primary key, the table which is referencing this primary key set should also contain two fields. In simple, the tab2 where you are creating the foreign key should consists of two fields. Let me put it in a simpler way.
I have an orders table containing Product_id and Supplier_id. For our example, we define a composite primary key i.e. these two values together form uniqueness.
Alter table orders add constraint Pk_Orders primary key (Product_id, Supplier_id);
Now pk_orders = Product_id, Supplier_id.
Lets create the second table Shipments which should contains a primary key shipment_id generated from a sequence, a shipment destination and a reference to orders table. Since the primary key of orders is made up of two fields, we'll have to have two fields in the shipment too to refer to orders. So shipment table will be
Shipment_id
Product_id
Supplier_id
Shipment_location
Alter table Shipment add constraint Pk_shipment primary key (Shipment_id);
Alter table Shipment add constraint Fk_shipment
Foreign key (Product_id, Supplier_id) References Order (Product_id, Supplier_id);
So, you first decide if your tab1 should have both the keys defined as primary keys. If yes, then you should have two similar fields in tab2 pointing to the tab1 pair.
The above example may not make sense, but it was used only for explaining.
hth
Prem :)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
