Solved

Primary Foreign key creation on Oracle tables

Posted on 2013-12-26
2
683 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Creating and Managing Databases with phpMyAdmin in cPanel.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to recover a database from a user managed backup

708 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now