Home » SQL & PL/SQL » SQL & PL/SQL » Help on creating master and detail relationship (Oracle 11g)
Help on creating master and detail relationship [message #575915] Tue, 29 January 2013 01:56 Go to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi Experts,

I want to create master and detail relationship
using foreign key and primary key among the following three tables.

Category_Id in GOMT_TABLE should be unique.

Category_Id in CATEGORY_TABLE is repeating and should be not null.

Category_Id in CONTROL_TABLE is repeating and should be not null.

CREATE TABLE gomt_table
(
   Category_Id     VARCHAR2 (10) PRIMARY KEY,
   Category_Name   VARCHAR2 (50) UNIQUE,
   act_type        CHAR (1) NOT NULL,
   Minimum_value   NUMBER NOT NULL,
   CONSTRAINT gom_table_act_type_ck CHECK ((ACT_TYPE) IN ('A','P'))
   );

ALTER TABLE gomt_table 
MODIFY (Category_Name VARCHAR2 (50) CONSTRAINT gom_table_category_name_nn NOT NULL);

CREATE TABLE Category_Table
(
   Category_Id        VARCHAR2 (10) NOT NULL,
   Retention_period   NUMBER NOT NULL,
   Buid               NUMBER NOT NULL,
   Create_date        DATE NOT NULL,
   Last_update_date   DATE NOT NULL,
   Modifiedby         VARCHAR2 (200) NOT NULL,
   CONSTRAINT Category_Table_cid_rp_pk PRIMARY KEY
      (Category_Id, Retention_period)
);

CREATE TABLE control_table
(
   category_id        VARCHAR2 (30) NOT NULL,
   seq_id             NUMBER PRIMARY KEY,
   tablename         VARCHAR2 (30) NOT NULL,
   parent_column   VARCHAR2 (100) NOT NULL,
   child_column   VARCHAR2 (100) NOT NULL,
   parent_id          NUMBER DEFAULT NULL ,
   schema_name        VARCHAR2 (30) NOT NULL,
   FLAG VARCHAR2(30) DEFAULT NULL,
   CONSTRAINT control_Table_tn_sn_uk UNIQUE
      (tablename, schema_name),
   CONSTRAINT parentid_seqid_fk FOREIGN KEY
      (parent_id)
       REFERENCES control_table (seq_id)
);

Is it better to have GOMT_TABLE as mater and CATEGORY_TABLE,CONTROL_TABLE as details tables to GOMT_TABLE.
Else any other approach is better.

   CONSTRAINT fk_gomt_cat FOREIGN KEY       ----- This constraint in CATEGORY_TABLE
      (category_id)
       REFERENCES gomt_table (category_id);

   CONSTRAINT fk_gomt_control FOREIGN KEY 
      (category_id)                           ----- This constraint in CONTROL_TABLE
       REFERENCES gomt_table (category_id);

Please help me.

Thanks in advance.
Re: Help on creating master and detail relationship [message #575916 is a reply to message #575915] Tue, 29 January 2013 02:30 Go to previous message
Michel Cadot
Messages: 60012
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It depends on what is your "Universe".
You have to describe it with wrods in details, we cannot build a model of your universe with the table DDL which is already a model of this universe.

Regards
Michel
Previous Topic: Unable to execute the procedure
Next Topic: Database link creation
Goto Forum:
  


Current Time: Sun Dec 21 20:09:57 CST 2014

Total time taken to generate the page: 0.15979 seconds