jknj72
asked on
Oracle table config Foreign Key
Im looking to create a Foreign Key relationship and I want to be able to do a Cascading delete from the main table to the Main_Details table.
This is my primary table script. I have a trigger adding the ID value on every insert. I want to create a table that holds the detail data with the foreign key to the POL_ID from main table
Heres the main table
CREATE TABLE ANET.POLICY_DATA
(
POL_ID NUMBER,
POL_TITLE VARCHAR2(100 BYTE),
POL_NUMBER VARCHAR2(10 BYTE),
POL_SUPERSEDES VARCHAR2(100 BYTE),
POL_EFF_DATE DATE,
POL_MANUAL VARCHAR2(100 BYTE),
POL_SOURCE VARCHAR2(100 BYTE),
POL_SUBJECT VARCHAR2(100 BYTE),
POL_APPLIES_TO VARCHAR2(100 BYTE),
POL_SIGNATURE VARCHAR2(100 BYTE),
CREATE_DATE DATE DEFAULT SYSDATE,
CREATE_BY VARCHAR2(50 BYTE) DEFAULT USER
)
I have a Details table below that Id like to have the ability to delete a row from the table above and it Delete the row from this details table. I have a column POL_ID that I want as the Foreign Key
Here is what I have thus far...
CREATE TABLE POLICY_DATA_DETAILS
(
POL_DETAILS_ID NUMBER,
POL_DATA_ID NUMBER,
POL_DETAILS_DESC VARCHAR2(50 BYTE)
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
Any help would be appreciated
Thanks
This is my primary table script. I have a trigger adding the ID value on every insert. I want to create a table that holds the detail data with the foreign key to the POL_ID from main table
Heres the main table
CREATE TABLE ANET.POLICY_DATA
(
POL_ID NUMBER,
POL_TITLE VARCHAR2(100 BYTE),
POL_NUMBER VARCHAR2(10 BYTE),
POL_SUPERSEDES VARCHAR2(100 BYTE),
POL_EFF_DATE DATE,
POL_MANUAL VARCHAR2(100 BYTE),
POL_SOURCE VARCHAR2(100 BYTE),
POL_SUBJECT VARCHAR2(100 BYTE),
POL_APPLIES_TO VARCHAR2(100 BYTE),
POL_SIGNATURE VARCHAR2(100 BYTE),
CREATE_DATE DATE DEFAULT SYSDATE,
CREATE_BY VARCHAR2(50 BYTE) DEFAULT USER
)
I have a Details table below that Id like to have the ability to delete a row from the table above and it Delete the row from this details table. I have a column POL_ID that I want as the Foreign Key
Here is what I have thus far...
CREATE TABLE POLICY_DATA_DETAILS
(
POL_DETAILS_ID NUMBER,
POL_DATA_ID NUMBER,
POL_DETAILS_DESC VARCHAR2(50 BYTE)
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
Any help would be appreciated
Thanks
you'll need to have a foreign key constraint, something like this in your create statement for your detail table:
CREATE TABLE POLICY_DATA_DETAILS
(
POL_DETAILS_ID NUMBER,
POL_DATA_ID NUMBER,
POL_DETAILS_DESC VARCHAR2(50 BYTE),
POL_ID NUMBER,
CONSTRAINT POL_DETAILS_FK FOREIGN KEY (POL_ID) REFERENCES ANET.POLICY_DATA(POL_ID) ON DELETE CASCADE
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE TABLE POLICY_DATA_DETAILS
(
POL_DETAILS_ID NUMBER,
POL_DATA_ID NUMBER,
POL_DETAILS_DESC VARCHAR2(50 BYTE),
POL_ID NUMBER,
CONSTRAINT POL_DETAILS_FK FOREIGN KEY (POL_ID) REFERENCES ANET.POLICY_DATA(POL_ID) ON DELETE CASCADE
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
use johnsone's solution if you've already created the table and if you're unable to re-create it. If not, you may use "my" statement ;-)
ASKER
johnson; POL_DATA_ID is what I named the column in the Details table that I wanted to use as the foreign key. If I change that in your post than it should work correct?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks johnson I appreciate your help.
Open in new window