asked on FOREIGN KEY RELATIONSHIPS
I have 5 tables of which I have applied pk, fk relationships to. I'm not sure what the rules are. Does a FK have to be related to a PK or can it be related to a UNIQUE index or does the FK have to be related to a composite PK.
I created the following relationships in Erwin and I'm not sure whether it did its own thing or not.
Any advice appreciated.
TABLE NAME
DB_USER
USER_GROUP
PROFILE_PROPERTY
PROFILE_TYPE
PROPERTY
RELATIONSHIPS
DB_USER -> USER_GROUP
USER_GROUP -> PROFILE_PROPERTY
PROFILE_PROPERTY <- PROFILE_TYPE
PROFILE_PROPERTY <- PROPERTY
CREATE TABLESPACE RDIDX01
;
DROP TABLE PROFILE_PROPERTY CASCADE CONSTRAINTS;
CREATE TABLE PROFILE_PROPERTY (
PROFILE_PROPERTY_ID NUMBER(15) NOT NULL,
USER_GROUP_ID NUMBER(15) NOT NULL,
PROFILE_TYPE VARCHAR2(30) NOT NULL,
PROPERTY_NM VARCHAR2(30) NOT NULL,
PROPERTY_VAL VARCHAR2(30) NULL,
ACTIVE_FLG NUMBER(1) DEFAULT 1 NOT NULL,
CREATE_DTS TIMESTAMP(6) NULL
);
CREATE UNIQUE INDEX PROFILE_PROPERTY_PK ON PROFILE_PROPERTY
(
PROFILE_PROPERTY_ID ASC,
PROPERTY_NM ASC,
PROFILE_TYPE ASC
)
PCTFREE 10
INITRANS 2
MAXTRANS 255
TABLESPACE RDIDX01
STORAGE (
INITIAL 8M
NEXT 8M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
)
;
ALTER TABLE PROFILE_PROPERTY
ADD ( PRIMARY KEY (PROFILE_PROPERTY_ID, PROPERTY_NM,
PROFILE_TYPE)
USING INDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
TABLESPACE RDIDX01
STORAGE (
INITIAL 8M
NEXT 8M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
) ) ;
CREATE TABLE DB_USER (
USER_ID NUMBER(15) NOT NULL,
USER_FIRST_NM VARCHAR2(30) NOT NULL,
USER_LAST_NM VARCHAR2(30) NOT NULL,
USER_GROUP_ID NUMBER(15) NOT NULL,
ACTIVE_FLG NUMBER(1) DEFAULT 1 NOT NULL,
CREATE_DTS TIMESTAMP(6) NULL,
UPDATE_DTS TIMESTAMP(6) NULL
);
CREATE UNIQUE INDEX DB_USER_PK ON DB_USER
(
USER_ID ASC,
USER_GROUP_ID ASC
);
ALTER TABLE DB_USER
ADD ( PRIMARY KEY (USER_ID, USER_GROUP_ID) ) ;
CREATE TABLE USER_GROUP (
USER_GROUP_ID NUMBER(15) NOT NULL,
USER_GROUP_NM VARCHAR2(30) NOT NULL,
USER_GROUP_DESC VARCHAR2(250) NULL,
ACTIVE_FLG NUMBER(1) DEFAULT 1 NOT NULL,
CREATE_DTS TIMESTAMP(6) NULL,
UPDATE_DTS TIMESTAMP(6) NULL
);
CREATE UNIQUE INDEX USER_GROUP_ID_PK ON USER_GROUP
(
USER_GROUP_ID ASC
)
PCTFREE 10
INITRANS 2
MAXTRANS 255
TABLESPACE RDIDX01
STORAGE (
INITIAL 8M
NEXT 8M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
)
;
ALTER TABLE USER_GROUP
ADD ( PRIMARY KEY (USER_GROUP_ID)
USING INDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
TABLESPACE RDIDX01
STORAGE (
INITIAL 8M
NEXT 8M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
) ) ;
CREATE TABLE PROPERTY (
PROPERTY_NM VARCHAR2(50) NOT NULL,
PROPERTY_DESC VARCHAR2(250) NULL,
CREATE_DTS TIMESTAMP(6) NULL
);
CREATE UNIQUE INDEX PROPERTY_PK ON PROPERTY
(
PROPERTY_NM ASC
)
PCTFREE 10
INITRANS 2
MAXTRANS 255
TABLESPACE RDIDX01
STORAGE (
INITIAL 8M
NEXT 8M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
)
;
ALTER TABLE PROPERTY
ADD ( PRIMARY KEY (PROPERTY_NM)
USING INDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
TABLESPACE RDIDX01
STORAGE (
INITIAL 8M
NEXT 8M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
) ) ;
CREATE TABLE PROFILE_TYPE (
PROFILE_TYPE VARCHAR2(50) NOT NULL,
PROFILE_DESC VARCHAR2(250) NULL,
CREATE_DTS TIMESTAMP(6) NULL
);
CREATE UNIQUE INDEX PROFILE_TYPE_PK ON PROFILE_TYPE
(
PROFILE_TYPE ASC
)
PCTFREE 10
INITRANS 2
MAXTRANS 255
TABLESPACE RDIDX01
STORAGE (
INITIAL 8M
NEXT 8M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
)
;
ALTER TABLE PROFILE_TYPE
ADD ( PRIMARY KEY (PROFILE_TYPE)
USING INDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
TABLESPACE RDIDX01
STORAGE (
INITIAL 8M
NEXT 8M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
) ) ;
ALTER TABLE PROFILE_PROPERTY
ADD ( FOREIGN KEY (PROPERTY_NM)
REFERENCES PROPERTY ) ;
ALTER TABLE PROFILE_PROPERTY
ADD ( FOREIGN KEY (PROFILE_TYPE)
REFERENCES PROFILE_TYPE ) ;
ALTER TABLE PROFILE_PROPERTY
ADD ( FOREIGN KEY (USER_GROUP_ID)
REFERENCES USER_GROUP ) ;
ALTER TABLE DB_USER
ADD ( FOREIGN KEY (USER_GROUP_ID)
REFERENCES USER_GROUP ) ;
I created the following relationships in Erwin and I'm not sure whether it did its own thing or not.
Any advice appreciated.
TABLE NAME
DB_USER
USER_GROUP
PROFILE_PROPERTY
PROFILE_TYPE
PROPERTY
RELATIONSHIPS
DB_USER -> USER_GROUP
USER_GROUP -> PROFILE_PROPERTY
PROFILE_PROPERTY <- PROFILE_TYPE
PROFILE_PROPERTY <- PROPERTY
CREATE TABLESPACE RDIDX01
;
DROP TABLE PROFILE_PROPERTY CASCADE CONSTRAINTS;
CREATE TABLE PROFILE_PROPERTY (
PROFILE_PROPERTY_ID NUMBER(15) NOT NULL,
USER_GROUP_ID NUMBER(15) NOT NULL,
PROFILE_TYPE VARCHAR2(30) NOT NULL,
PROPERTY_NM VARCHAR2(30) NOT NULL,
PROPERTY_VAL VARCHAR2(30) NULL,
ACTIVE_FLG NUMBER(1) DEFAULT 1 NOT NULL,
CREATE_DTS TIMESTAMP(6) NULL
);
CREATE UNIQUE INDEX PROFILE_PROPERTY_PK ON PROFILE_PROPERTY
(
PROFILE_PROPERTY_ID ASC,
PROPERTY_NM ASC,
PROFILE_TYPE ASC
)
PCTFREE 10
INITRANS 2
MAXTRANS 255
TABLESPACE RDIDX01
STORAGE (
INITIAL 8M
NEXT 8M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
)
;
ALTER TABLE PROFILE_PROPERTY
ADD ( PRIMARY KEY (PROFILE_PROPERTY_ID, PROPERTY_NM,
PROFILE_TYPE)
USING INDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
TABLESPACE RDIDX01
STORAGE (
INITIAL 8M
NEXT 8M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
) ) ;
CREATE TABLE DB_USER (
USER_ID NUMBER(15) NOT NULL,
USER_FIRST_NM VARCHAR2(30) NOT NULL,
USER_LAST_NM VARCHAR2(30) NOT NULL,
USER_GROUP_ID NUMBER(15) NOT NULL,
ACTIVE_FLG NUMBER(1) DEFAULT 1 NOT NULL,
CREATE_DTS TIMESTAMP(6) NULL,
UPDATE_DTS TIMESTAMP(6) NULL
);
CREATE UNIQUE INDEX DB_USER_PK ON DB_USER
(
USER_ID ASC,
USER_GROUP_ID ASC
);
ALTER TABLE DB_USER
ADD ( PRIMARY KEY (USER_ID, USER_GROUP_ID) ) ;
CREATE TABLE USER_GROUP (
USER_GROUP_ID NUMBER(15) NOT NULL,
USER_GROUP_NM VARCHAR2(30) NOT NULL,
USER_GROUP_DESC VARCHAR2(250) NULL,
ACTIVE_FLG NUMBER(1) DEFAULT 1 NOT NULL,
CREATE_DTS TIMESTAMP(6) NULL,
UPDATE_DTS TIMESTAMP(6) NULL
);
CREATE UNIQUE INDEX USER_GROUP_ID_PK ON USER_GROUP
(
USER_GROUP_ID ASC
)
PCTFREE 10
INITRANS 2
MAXTRANS 255
TABLESPACE RDIDX01
STORAGE (
INITIAL 8M
NEXT 8M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
)
;
ALTER TABLE USER_GROUP
ADD ( PRIMARY KEY (USER_GROUP_ID)
USING INDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
TABLESPACE RDIDX01
STORAGE (
INITIAL 8M
NEXT 8M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
) ) ;
CREATE TABLE PROPERTY (
PROPERTY_NM VARCHAR2(50) NOT NULL,
PROPERTY_DESC VARCHAR2(250) NULL,
CREATE_DTS TIMESTAMP(6) NULL
);
CREATE UNIQUE INDEX PROPERTY_PK ON PROPERTY
(
PROPERTY_NM ASC
)
PCTFREE 10
INITRANS 2
MAXTRANS 255
TABLESPACE RDIDX01
STORAGE (
INITIAL 8M
NEXT 8M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
)
;
ALTER TABLE PROPERTY
ADD ( PRIMARY KEY (PROPERTY_NM)
USING INDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
TABLESPACE RDIDX01
STORAGE (
INITIAL 8M
NEXT 8M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
) ) ;
CREATE TABLE PROFILE_TYPE (
PROFILE_TYPE VARCHAR2(50) NOT NULL,
PROFILE_DESC VARCHAR2(250) NULL,
CREATE_DTS TIMESTAMP(6) NULL
);
CREATE UNIQUE INDEX PROFILE_TYPE_PK ON PROFILE_TYPE
(
PROFILE_TYPE ASC
)
PCTFREE 10
INITRANS 2
MAXTRANS 255
TABLESPACE RDIDX01
STORAGE (
INITIAL 8M
NEXT 8M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
)
;
ALTER TABLE PROFILE_TYPE
ADD ( PRIMARY KEY (PROFILE_TYPE)
USING INDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
TABLESPACE RDIDX01
STORAGE (
INITIAL 8M
NEXT 8M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
) ) ;
ALTER TABLE PROFILE_PROPERTY
ADD ( FOREIGN KEY (PROPERTY_NM)
REFERENCES PROPERTY ) ;
ALTER TABLE PROFILE_PROPERTY
ADD ( FOREIGN KEY (PROFILE_TYPE)
REFERENCES PROFILE_TYPE ) ;
ALTER TABLE PROFILE_PROPERTY
ADD ( FOREIGN KEY (USER_GROUP_ID)
REFERENCES USER_GROUP ) ;
ALTER TABLE DB_USER
ADD ( FOREIGN KEY (USER_GROUP_ID)
REFERENCES USER_GROUP ) ;
Oracle Database
Last Comment
dplinnane
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Erwin seems to be creating unwanted indexes etc.
Here is a specific example that I am trying to figure out right now.
CREATE TABLE DB_SCHEMA (
USER_ID NUMBER(15) NOT NULL,
SCHEMA_NM NUMBER(30) NOT NULL,
SERVER_NM VARCHAR2(30) NOT NULL,
ACTIVE_FLG NUMBER(1) DEFAULT 1 NOT NULL
);
ALTER TABLE DB_SCHEMA
ADD ( PRIMARY KEY (USER_ID,SCHEMA_NM,SERVER_
I want to relate this to the DB_USER TABLE where DB_USER is parent PK USER_ID
After creating table below and adding pk and unique index I run the following
ALTER TABLE DB_SCHEMA
ADD ( FOREIGN KEY (USER_ID)
REFERENCES DB_USER ) ;
and get
Error on line 0
ALTER TABLE DB_SCHEMA
ADD ( FOREIGN KEY (USER_ID)
ORA-02256: number of referencing columns must match referenced columns
WHAT AM I DOING WRONG?
CREATE TABLE DB_USER (
USER_ID NUMBER(15) NOT NULL,
USER_FIRST_NM VARCHAR2(30) NOT NULL,
USER_LAST_NM VARCHAR2(30) NOT NULL,
USER_GROUP_ID NUMBER(15) NOT NULL,
ACTIVE_FLG NUMBER(1) DEFAULT 1 NOT NULL,
CREATE_DTS DATE NULL,
UPDATE_DTS DATE NULL
);
CREATE UNIQUE INDEX DB_USER_PK ON DB_USER
(
USER_ID ASC,
USER_GROUP_ID ASC
);
ALTER TABLE DB_USER
ADD ( PRIMARY KEY (USER_ID, USER_GROUP_ID) ) ;
I had to manaully alter the statement below
ALTER TABLE PROFILE_PROPERTY
ADD ( PRIMARY KEY (PROFILE_PROPERTY_ID, PROPERTY_NM,
PROFILE_TYPE)
to ALTER TABLE PROFILE_PROPERTY
ADD ( PRIMARY KEY (PROFILE_PROPERTY_ID)
This was erwin generated refering to andrewst comment.
Here is a specific example that I am trying to figure out right now.
CREATE TABLE DB_SCHEMA (
USER_ID NUMBER(15) NOT NULL,
SCHEMA_NM NUMBER(30) NOT NULL,
SERVER_NM VARCHAR2(30) NOT NULL,
ACTIVE_FLG NUMBER(1) DEFAULT 1 NOT NULL
);
ALTER TABLE DB_SCHEMA
ADD ( PRIMARY KEY (USER_ID,SCHEMA_NM,SERVER_
I want to relate this to the DB_USER TABLE where DB_USER is parent PK USER_ID
After creating table below and adding pk and unique index I run the following
ALTER TABLE DB_SCHEMA
ADD ( FOREIGN KEY (USER_ID)
REFERENCES DB_USER ) ;
and get
Error on line 0
ALTER TABLE DB_SCHEMA
ADD ( FOREIGN KEY (USER_ID)
ORA-02256: number of referencing columns must match referenced columns
WHAT AM I DOING WRONG?
CREATE TABLE DB_USER (
USER_ID NUMBER(15) NOT NULL,
USER_FIRST_NM VARCHAR2(30) NOT NULL,
USER_LAST_NM VARCHAR2(30) NOT NULL,
USER_GROUP_ID NUMBER(15) NOT NULL,
ACTIVE_FLG NUMBER(1) DEFAULT 1 NOT NULL,
CREATE_DTS DATE NULL,
UPDATE_DTS DATE NULL
);
CREATE UNIQUE INDEX DB_USER_PK ON DB_USER
(
USER_ID ASC,
USER_GROUP_ID ASC
);
ALTER TABLE DB_USER
ADD ( PRIMARY KEY (USER_ID, USER_GROUP_ID) ) ;
I had to manaully alter the statement below
ALTER TABLE PROFILE_PROPERTY
ADD ( PRIMARY KEY (PROFILE_PROPERTY_ID, PROPERTY_NM,
PROFILE_TYPE)
to ALTER TABLE PROFILE_PROPERTY
ADD ( PRIMARY KEY (PROFILE_PROPERTY_ID)
This was erwin generated refering to andrewst comment.
The first error:
"ORA-02256: number of referencing columns must match referenced columns"
indicates that the primary (or unique) key that you are trying to reference has a different number of columns than the foreign key you are trying to create. The rule is, the number (and datatype) of the columns must match. So, if the primary (or unique) key contains just one column, the foreign key may contain only one column. If the primary (or unique) key is multi-column, then the foreign key must also be multi-column.
In you second example, I am surprised that Erwin created a primary key that makes up three of the four columns in that table. Maybe that is what you want, but that means that any tables that are related must contain those three columns that make up the primary key in this table.
"ORA-02256: number of referencing columns must match referenced columns"
indicates that the primary (or unique) key that you are trying to reference has a different number of columns than the foreign key you are trying to create. The rule is, the number (and datatype) of the columns must match. So, if the primary (or unique) key contains just one column, the foreign key may contain only one column. If the primary (or unique) key is multi-column, then the foreign key must also be multi-column.
In you second example, I am surprised that Erwin created a primary key that makes up three of the four columns in that table. Maybe that is what you want, but that means that any tables that are related must contain those three columns that make up the primary key in this table.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thanks Guys split the points for effort, I think Erwin screwed things up when I was joing tables together dragging the realtionship icon. I have posted my modified cleaned up script below, I think it is now correct.
CREATE TABLE PROFILE_TYPE
(
PROFILE_TYPE VARCHAR2(50 BYTE) NOT NULL,
PROFILE_DESC VARCHAR2(250 BYTE),
CREATE_DTS TIMESTAMP(6),
CONSTRAINT PT_PROFILE_TYPE_PK PRIMARY KEY (PROFILE_TYPE)
)
NOLOGGING
NOCACHE
NOPARALLEL;
CREATE TABLE PROPERTY
(
PROPERTY_NM VARCHAR2(50 BYTE) NOT NULL,
PROPERTY_DESC VARCHAR2(250 BYTE),
CREATE_DTS TIMESTAMP(6),
PROPERTY_TYPE VARCHAR2(30 BYTE),
CONSTRAINT P_PROPERTY_NM_PK PRIMARY KEY (PROPERTY_NM)
)
NOLOGGING
NOCACHE
NOPARALLEL;
CREATE TABLE USER_GROUP
(
USER_GROUP_ID NUMBER(15) NOT NULL,
USER_GROUP_NM VARCHAR2(30 BYTE) NOT NULL,
USER_GROUP_DESC VARCHAR2(250 BYTE),
ACTIVE_FLG NUMBER(1) DEFAULT 1 NOT NULL,
CREATE_DTS TIMESTAMP(6),
UPDATE_DTS TIMESTAMP(6),
CONSTRAINT UG_USER_GROUP_ID_PK PRIMARY KEY (USER_GROUP_ID)
)
NOLOGGING
NOCACHE
NOPARALLEL;
CREATE UNIQUE INDEX PROFILE_TYPE_PK ON PROFILE_TYPE
(PROFILE_TYPE)
NOLOGGING
NOPARALLEL;
CREATE UNIQUE INDEX PROPERTY_PK ON PROPERTY
(PROPERTY_NM)
NOLOGGING
NOPARALLEL;
CREATE UNIQUE INDEX USER_GROUP_ID_PK ON USER_GROUP
(USER_GROUP_ID)
NOLOGGING
NOPARALLEL;
CREATE TABLE DB_USER
(
USER_ID NUMBER(15) NOT NULL,
USER_FIRST_NM VARCHAR2(30 BYTE) NOT NULL,
USER_LAST_NM VARCHAR2(30 BYTE) NOT NULL,
USER_GROUP_ID NUMBER(15) NOT NULL,
ACTIVE_FLG NUMBER(1) DEFAULT 1 NOT NULL,
CREATE_DTS TIMESTAMP(6),
UPDATE_DTS TIMESTAMP(6),
CONSTRAINT DU_USER_ID_PK PRIMARY KEY (USER_ID),
CONSTRAINT DU_USER_GROUP_ID_FK FOREIGN KEY (USER_GROUP_ID)
REFERENCES USER_GROUP (USER_GROUP_ID)
)
NOLOGGING
NOCACHE
NOPARALLEL;
CREATE TABLE PROFILE_PROPERTY
(
PROFILE_PROPERTY_ID NUMBER(15) NOT NULL,
USER_GROUP_ID NUMBER(15) NOT NULL,
PROFILE_TYPE VARCHAR2(50 BYTE) NOT NULL,
PROPERTY_NM VARCHAR2(50 BYTE) NOT NULL,
PROPERTY_VAL VARCHAR2(30 BYTE),
ACTIVE_FLG NUMBER(1) DEFAULT 1 NOT NULL,
CONSTRAINT PP_PROFILE_PROPERTY_PK PRIMARY KEY (PROFILE_PROPERTY_ID),
CONSTRAINT PP_PROFILE_TYPE_FK FOREIGN KEY (PROFILE_TYPE)
REFERENCES PROFILE_TYPE (PROFILE_TYPE),
CONSTRAINT PP_PROPERTY_NM_FK FOREIGN KEY (PROPERTY_NM)
REFERENCES PROPERTY (PROPERTY_NM),
CONSTRAINT PP_USER_GROUP_ID_FK FOREIGN KEY (USER_GROUP_ID)
REFERENCES USER_GROUP (USER_GROUP_ID)
)
NOLOGGING
NOCACHE
NOPARALLEL;
CREATE UNIQUE INDEX USER_ID_PK ON DB_USER
(USER_ID)
NOLOGGING
NOPARALLEL;
CREATE UNIQUE INDEX USER_GROUP_ID_IDX2 ON DB_USER
(USER_GROUP_ID)
NOLOGGING
NOPARALLEL;
CREATE UNIQUE INDEX PROFILE_PROPERTY_PK ON PROFILE_PROPERTY
(PROFILE_PROPERTY_ID)
NOLOGGING
NOPARALLEL;
CREATE UNIQUE INDEX USER_GROUP_ID_IDX1 ON PROFILE_PROPERTY
(USER_GROUP_ID)
NOLOGGING
NOPARALLEL;
CREATE UNIQUE INDEX PROFILE_TYPE_IDX2 ON PROFILE_PROPERTY
(PROFILE_TYPE)
NOLOGGING
NOPARALLEL;
CREATE UNIQUE INDEX PROPERTY_NM_IDX3 ON PROFILE_PROPERTY
(PROPERTY_NM)
NOLOGGING
NOPARALLEL;
CREATE TABLE DB_SCHEMA
(
USER_ID NUMBER(15) NOT NULL,
SCHEMA_NM NUMBER(30) NOT NULL,
SERVER_NM VARCHAR2(30 BYTE) NOT NULL,
ACTIVE_FLG NUMBER(1) DEFAULT 1 NOT NULL,
CONSTRAINT DB_SCHEMA_PK PRIMARY KEY (USER_ID, SCHEMA_NM, SERVER_NM),
CONSTRAINT DS_USER_ID_FK FOREIGN KEY (USER_ID)
REFERENCES DB_USER (USER_ID)
)
NOLOGGING
NOCACHE
NOPARALLEL;
CREATE TABLE PROFILE_TYPE
(
PROFILE_TYPE VARCHAR2(50 BYTE) NOT NULL,
PROFILE_DESC VARCHAR2(250 BYTE),
CREATE_DTS TIMESTAMP(6),
CONSTRAINT PT_PROFILE_TYPE_PK PRIMARY KEY (PROFILE_TYPE)
)
NOLOGGING
NOCACHE
NOPARALLEL;
CREATE TABLE PROPERTY
(
PROPERTY_NM VARCHAR2(50 BYTE) NOT NULL,
PROPERTY_DESC VARCHAR2(250 BYTE),
CREATE_DTS TIMESTAMP(6),
PROPERTY_TYPE VARCHAR2(30 BYTE),
CONSTRAINT P_PROPERTY_NM_PK PRIMARY KEY (PROPERTY_NM)
)
NOLOGGING
NOCACHE
NOPARALLEL;
CREATE TABLE USER_GROUP
(
USER_GROUP_ID NUMBER(15) NOT NULL,
USER_GROUP_NM VARCHAR2(30 BYTE) NOT NULL,
USER_GROUP_DESC VARCHAR2(250 BYTE),
ACTIVE_FLG NUMBER(1) DEFAULT 1 NOT NULL,
CREATE_DTS TIMESTAMP(6),
UPDATE_DTS TIMESTAMP(6),
CONSTRAINT UG_USER_GROUP_ID_PK PRIMARY KEY (USER_GROUP_ID)
)
NOLOGGING
NOCACHE
NOPARALLEL;
CREATE UNIQUE INDEX PROFILE_TYPE_PK ON PROFILE_TYPE
(PROFILE_TYPE)
NOLOGGING
NOPARALLEL;
CREATE UNIQUE INDEX PROPERTY_PK ON PROPERTY
(PROPERTY_NM)
NOLOGGING
NOPARALLEL;
CREATE UNIQUE INDEX USER_GROUP_ID_PK ON USER_GROUP
(USER_GROUP_ID)
NOLOGGING
NOPARALLEL;
CREATE TABLE DB_USER
(
USER_ID NUMBER(15) NOT NULL,
USER_FIRST_NM VARCHAR2(30 BYTE) NOT NULL,
USER_LAST_NM VARCHAR2(30 BYTE) NOT NULL,
USER_GROUP_ID NUMBER(15) NOT NULL,
ACTIVE_FLG NUMBER(1) DEFAULT 1 NOT NULL,
CREATE_DTS TIMESTAMP(6),
UPDATE_DTS TIMESTAMP(6),
CONSTRAINT DU_USER_ID_PK PRIMARY KEY (USER_ID),
CONSTRAINT DU_USER_GROUP_ID_FK FOREIGN KEY (USER_GROUP_ID)
REFERENCES USER_GROUP (USER_GROUP_ID)
)
NOLOGGING
NOCACHE
NOPARALLEL;
CREATE TABLE PROFILE_PROPERTY
(
PROFILE_PROPERTY_ID NUMBER(15) NOT NULL,
USER_GROUP_ID NUMBER(15) NOT NULL,
PROFILE_TYPE VARCHAR2(50 BYTE) NOT NULL,
PROPERTY_NM VARCHAR2(50 BYTE) NOT NULL,
PROPERTY_VAL VARCHAR2(30 BYTE),
ACTIVE_FLG NUMBER(1) DEFAULT 1 NOT NULL,
CONSTRAINT PP_PROFILE_PROPERTY_PK PRIMARY KEY (PROFILE_PROPERTY_ID),
CONSTRAINT PP_PROFILE_TYPE_FK FOREIGN KEY (PROFILE_TYPE)
REFERENCES PROFILE_TYPE (PROFILE_TYPE),
CONSTRAINT PP_PROPERTY_NM_FK FOREIGN KEY (PROPERTY_NM)
REFERENCES PROPERTY (PROPERTY_NM),
CONSTRAINT PP_USER_GROUP_ID_FK FOREIGN KEY (USER_GROUP_ID)
REFERENCES USER_GROUP (USER_GROUP_ID)
)
NOLOGGING
NOCACHE
NOPARALLEL;
CREATE UNIQUE INDEX USER_ID_PK ON DB_USER
(USER_ID)
NOLOGGING
NOPARALLEL;
CREATE UNIQUE INDEX USER_GROUP_ID_IDX2 ON DB_USER
(USER_GROUP_ID)
NOLOGGING
NOPARALLEL;
CREATE UNIQUE INDEX PROFILE_PROPERTY_PK ON PROFILE_PROPERTY
(PROFILE_PROPERTY_ID)
NOLOGGING
NOPARALLEL;
CREATE UNIQUE INDEX USER_GROUP_ID_IDX1 ON PROFILE_PROPERTY
(USER_GROUP_ID)
NOLOGGING
NOPARALLEL;
CREATE UNIQUE INDEX PROFILE_TYPE_IDX2 ON PROFILE_PROPERTY
(PROFILE_TYPE)
NOLOGGING
NOPARALLEL;
CREATE UNIQUE INDEX PROPERTY_NM_IDX3 ON PROFILE_PROPERTY
(PROPERTY_NM)
NOLOGGING
NOPARALLEL;
CREATE TABLE DB_SCHEMA
(
USER_ID NUMBER(15) NOT NULL,
SCHEMA_NM NUMBER(30) NOT NULL,
SERVER_NM VARCHAR2(30 BYTE) NOT NULL,
ACTIVE_FLG NUMBER(1) DEFAULT 1 NOT NULL,
CONSTRAINT DB_SCHEMA_PK PRIMARY KEY (USER_ID, SCHEMA_NM, SERVER_NM),
CONSTRAINT DS_USER_ID_FK FOREIGN KEY (USER_ID)
REFERENCES DB_USER (USER_ID)
)
NOLOGGING
NOCACHE
NOPARALLEL;
This primary key can have multiple columns, so the FK will also constains multiple columns.
FK are NOT related to UNIQUE INDEX only, they have to be related to a constraints (primary key).
BUT you could do it, it will work and the validation will work too, but your model will be weird.