?
Solved

Primary Foreign key creation on Oracle tables

Posted on 2013-12-26
2
Medium Priority
?
785 Views
Last Modified: 2013-12-26
I have my main table POLICY_DATA and heres the script

CREATE TABLE POLICY_DATA_LINK
(
  POL_LINK_ID    NUMBER,
  POL_LINK_URL   VARCHAR2(100 BYTE),
  POL_LINK_PATH  VARCHAR2(255 BYTE),
  POL_DETAIL_ID  NUMBER
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;


CREATE UNIQUE INDEX POLICY_DATA_LINK_PK ON POLICY_DATA_LINK
(POL_LINK_ID)
LOGGING
NOPARALLEL;


ALTER TABLE POLICY_DATA_LINK ADD (
  CONSTRAINT POLICY_DATA_LINK_PK
  PRIMARY KEY
  (POL_LINK_ID)
  USING INDEX POLICY_DATA_LINK_PK
  ENABLE VALIDATE);

Then I created a details table POLICY_DATA_DETAILS and heres the script

CREATE TABLE POLICY_DATA_DETAILS
(
  POL_DETAIL_ID     NUMBER                      NOT NULL,
  POL_DATA_ID       NUMBER                      NOT NULL,
  POL_DETAIL_TITLE  VARCHAR2(100 BYTE),
  POL_DETAIL_DESC   CLOB
)
LOB (POL_DETAIL_DESC) STORE AS (
  TABLESPACE  ANET_DATA
  ENABLE      STORAGE IN ROW
  CHUNK       8192
  RETENTION
  NOCACHE
  LOGGING
      STORAGE    (
                  INITIAL          64K
                  MINEXTENTS       1
                  MAXEXTENTS       UNLIMITED
                  PCTINCREASE      0
                  FREELISTS        1
                  FREELIST GROUPS  1
                  BUFFER_POOL      DEFAULT
                 ))
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;


CREATE OR REPLACE TRIGGER POLICY_DETAIL_TRG
BEFORE INSERT
ON POLICY_DATA_DETAILS
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE
  N NUMBER;
BEGIN
-- For Toad:  Highlight column TALENT_ID
  Select POLICY_SEQ.nextval into n from dual;
  :new.POL_DETAIL_ID := N;
END POLICY_DETAIL_TRG;
/


ALTER TABLE POLICY_DATA_DETAILS ADD (
  FOREIGN KEY (POL_DATA_ID)
  REFERENCES POLICY_DATA (POL_ID)
  ON DELETE CASCADE
  ENABLE VALIDATE);


I need to create another table that has link info from the POLICAY_DATA_DETAILS table so I was thinking about creating a POLICY_DATA_LINK table. Id like to create this table with a foreign key(POL_DETAIL_ID) to the details table but was having a problem. Here is what I have so far...

CREATE TABLE POLICY_DATA_LINK
(
  POL_LINK_ID    NUMBER,
  POL_LINK_URL   VARCHAR2(100 BYTE),
  POL_LINK_PATH  VARCHAR2(255 BYTE),
  POL_DETAIL_ID  NUMBER
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;


CREATE UNIQUE INDEX POLICY_DATA_LINK_PK ON POLICY_DATA_LINK
(POL_LINK_ID)
LOGGING
NOPARALLEL;


ALTER TABLE POLICY_DATA_LINK ADD (
  CONSTRAINT POLICY_DATA_LINK_PK
  PRIMARY KEY
  (POL_LINK_ID)
  USING INDEX POLICY_DATA_LINK_PK
  ENABLE VALIDATE);

Any help would be appreciated!!! Thanks
0
Comment
Question by:jknj72
  • 2
2 Comments
 

Accepted Solution

by:
jknj72 earned 0 total points
ID: 39740151
I was able to figure it out...

ALTER TABLE POLICY_DATA_LINK ADD (
  CONSTRAINT POLICY_DATA_LINK_PK
  PRIMARY KEY
  (POL_LINK_ID)
  USING INDEX POLICY_DATA_LINK_PK
  ENABLE VALIDATE);

ALTER TABLE POLICY_DATA_LINK ADD (
  FOREIGN KEY (POL_DETAIL_ID)
  REFERENCES POLICY_DATA_DETAILS (POL_DETAIL_ID)
  ON DELETE CASCADE
  ENABLE VALIDATE);
0
 

Author Closing Comment

by:jknj72
ID: 39740153
I figure it out
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
This article will show a step by step guide on how to mask column values in Oracle 12c using DBMS_REDACT full redaction option. This option is available on licensed Oracle Enterprise edition as part of Oracle's Advanced Security.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

592 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question