[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Primary Foreign key creation on Oracle tables

Posted on 2013-12-26
2
Medium Priority
?
774 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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 …

656 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