Solved

Primary Foreign key creation on Oracle tables

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Can't Access My Database 57 67
Oracle DB monitor SW 21 50
Oracle collections 15 23
Error querying database link from Oracle 10g to Postgresql 7 34
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

803 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