Home » RDBMS Server » Server Administration » Re: primary/foreign key pleeeeeease help
| Re: primary/foreign key pleeeeeease help [message #375091] |
Wed, 25 July 2001 06:49 |
Sudhakar Atmakuru
Messages: 58
Registered: May 2001
|
Member |
|
|
I could be of more help to you if you were more specific with some code. Any way, try like this:
a. create your first table with a primary key, by adding either PRIMARY KEY at key level or CONSTRAINT your_pk_constraint_name PRIMARY KEY (your_pk_name) at table level (ie., at the end of table creation/definition).
b. create your second table with the foreign keys specifying the references at key level or table level. As you say, it is composite, then it would be better if you give it at table level at the end of table definition/creation, like
CONSTRAINT your_fk_name FOREIGN KEY (first_fk_name,second_fk_name) REFERENCES first_table_name(first_pk_name,second_pk_name) ON DELETE CASCADE;
Give the option ON DELETE CASCADE only at your requirement. Otherwise, by default it takes, ON DELETE RESTRICT.
In simple, when you define a foreign key constraint, you could specify a list of foregin keys those which reference primary/keys of other tables in a pair of paranthesis ().
Example:
EMPLOYEE - EMP_ID, EMP_NAME, BSALARY, and STATUS
EMP_DETL - DETL_ID, EMP_ID, DEPARTMENT, HIRE_DATE, SUPERVISOR, and LAST_PROMOTED_DATE
CREATE TABLE EMPLOYEE (
EMP_ID NUMBER NOT NULL PRIMARY KEY,
EMP_NAME VARCHAR2(20),
BSALARY NUMBER,
STATUS CHAR(1));
CREATE TABLE EMP_DETL (
DETL_ID NUMBER NOT NULL PRIMARY KEY,
EMP_ID NUMBER NOT NULL REFERENCES EMPLOYEE(EMP_ID),
DEPARTMENT VARCHAR2(20),
HIRE_DATE DATE,
SUPERVISOR NUMBER NOT NULL REFERENCES EMPLOYEE(EMP_ID),
LAST_PROMOTED_DATE DATE,
/*CONSTRAINT EMP_DETL_FK FOREIGN KEY (EMP_ID,SUPERVISOR) REFERENCES EMPLOYEE(EMP_ID,EMP_ID) ON DELETE CASCADE
the EMPLOYEE(EMP_ID,EMP_ID) can not be given as they are identical in the same table. This form of composite (ie. list of key items with in the () ) can only be given when they are referencing different key items, but not same key item*/);
I could not understand your point, that you want to create two foreign keys refering to same primary key in the first table. But again, you are saying the composite foreign key. It is some what confusing. A composite foreign key references a list of different key items from another table, but not same. In the above example, Empl_Detl table stores the detailed information of employees while the source information of them stored in Employee table. The SUPERVISOR is another foreign key references same primary key in the EMPLOYEE table, as supervisor itself an employee of the same company. I hope you understand and it helps to resolve your problem. Let me know.
Good luck.
|
|
|
|
Goto Forum:
Current Time: Fri Jul 05 11:20:40 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
