Skip to Main Content
  • Questions
  • How to define a composite primary key

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, AB .

Asked: May 02, 2000 - 1:21 pm UTC

Last updated: March 10, 2021 - 7:26 pm UTC

Version:

Viewed 100K+ times! This question is

You Asked

I have a table with two columns that need to be defined
as primary keys, order_number and item_number. A table can have
only one primary key, but I can define composite primary keys in
the table_constraint syntax. How do I define two columns as
composite primary keys?



and Tom said...


create table T ( x int,
y int,
z int,
constraint t_pk primary key (x,y) );

by using a table level constraint. Additionally you could just:

alter table t add constraint t_pk primary key(x,y);

after the fact.


Rating

  (20 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

refering the primary key columns

SESHU, June 30, 2005 - 9:09 am UTC

This really helped me but how can i references composite primary key as master detail

exm i created two composite primary keys at table level.When ever i create a detail table i want use one of this feild use as a reference column

ThanQ

Tom Kyte
June 30, 2005 - 9:50 am UTC

not sure what you mean?

Composite primary key references: (Clarification)

sudhakar, July 01, 2005 - 4:30 am UTC

iam giving the full details

i created table dept with two keys as Composite primary key

create table dept(
dept_id number(8),
dept_name varchar2(30),
loc_id number(4),
constraint pk_dept primary key(dept_id,loc_id));

Now, can I create a foriegn key in another table referencing one among the two columns (COMPOSITE KEY)say dept_id?

For Example I am creating emp table

create table emp(
emp_id number(5),
dept_id number(8),
constraint fk_emp foreign key(dept_id) references dept(dept_id));

It is showing me error. Now what is the solution for this problem, If i want to use one among the columns.

Expecting a quick reply with solution..

Thanks and regards,
Sudhakar.




Tom Kyte
July 01, 2005 - 10:04 am UTC

you may only create a foreign key to a set of columns that is set as unique or primary key.


dept_id is not unique - hence you cannot create a foriegn key to it.


a child record has ONE parent record, it must point to a UNIQUE or PRIMARY KEY constraint.


emp would have to have the LOC_ID and the foreign key would be

foreign key(dept_id,loc_id) references dept



Thankyou

The Admin, October 07, 2005 - 9:33 am UTC

Was very helpfull.

Prats, September 17, 2009 - 3:21 am UTC

Please guide about which one is good among following option for a an application where bilk insertions/updations happens during every night-
1) creating a composite primary key for say 6 columns out of total of 10 in a table.
2) creating an index on 5 columns out of total of 10 in table.

Thanks...
Tom Kyte
September 17, 2009 - 10:51 am UTC

the answer is one of:

a) neither
b) both of them
c) first one
d) second one


There is no context here, no understanding of what the data is and what needs be done.

You will of course have a constraint for your primary key, that may create an index (if one does not exist that could be used).

Lakshmikanth, May 21, 2011 - 12:27 am UTC

Hi, I have a master table having composite key (say having 2 primary keys) and now i am creating a detalied table (say taking two primary keys in detailed table). Now the problem is i am not able to create detailed table with two foreign keys(two foreign keys for for two primary keys). Can any one suggest me.........
Tom Kyte
May 23, 2011 - 11:47 am UTC

I did not follow you at all.

why can't you create the detail table with a two column foreign key to the parent table?

One more Query

Anshul Agarwal, February 01, 2012 - 4:29 am UTC

Hi Tom,
Thanks for useful knowledge
Now, I have a table which has 4 columns in which all are in composite primary key.
Create table configuration
(year number,week number,config_num number,loc_ind varchar2(30))
and then i create a composite primary key on all of these

Now when I try to insert , I got the result as :

INSERT INTO CONFIGURATION(YEAR,WEEK,CONFIG_NUM,LOC_IND)VALUES(2011,3,211,'SIN');--Inserted
INSERT INTO CONFIGURATION(YEAR,WEEK,CONFIG_NUM,LOC_IND)VALUES(2012,3,211,'SIN');--INSERTED

INSERT INTO CONFIGURATION(YEAR,WEEK,CONFIG_NUM,LOC_IND)VALUES(2011,5,211,'SIN');--UNIQUE CONSTRAINT ERR

INSERT INTO CONFIGURATION(YEAR,WEEK,CONFIG_NUM,LOC_IND)VALUES(2011,3,212,'SIN');--INSERTED

INSERT INTO CONFIGURATION(YEAR,WEEK,CONFIG_NUM,LOC_IND)VALUES(2011,3,211,'NCE');--UNIQUE CONSTRAINT ERR


I am confused why 2nd and 4th records got inserted and why not 3rd anf 5th.

Please Help
Tom Kyte
February 01, 2012 - 7:38 am UTC

please show your work, you did something other than what you say you did - your primary key IS NOT those four columns, if it was, this would be the result:

ops$tkyte%ORA11GR2> Create table configuration
  2  (year  number,week  number,config_num  number,loc_ind varchar2(30));

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> alter table configuration add constraint c_pk primary key(year, week, config_num, loc_ind );

Table altered.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> INSERT INTO CONFIGURATION(YEAR,WEEK,CONFIG_NUM,LOC_IND)VALUES(2011,3,211,'SIN');

1 row created.

ops$tkyte%ORA11GR2> INSERT INTO CONFIGURATION(YEAR,WEEK,CONFIG_NUM,LOC_IND)VALUES(2012,3,211,'SIN');

1 row created.

ops$tkyte%ORA11GR2> INSERT INTO CONFIGURATION(YEAR,WEEK,CONFIG_NUM,LOC_IND)VALUES(2011,5,211,'SIN');

1 row created.

ops$tkyte%ORA11GR2> INSERT INTO CONFIGURATION(YEAR,WEEK,CONFIG_NUM,LOC_IND)VALUES(2011,3,212,'SIN');

1 row created.

ops$tkyte%ORA11GR2> INSERT INTO CONFIGURATION(YEAR,WEEK,CONFIG_NUM,LOC_IND)VALUES(2011,3,211,'NCE');

1 row created.

ops$tkyte%ORA11GR2> 

That output would be explained by having the pk only on year and config_num

Chuck, February 01, 2012 - 2:39 pm UTC

That output would be explained by having the pk only on year and config_num:


chajol@taxtest>create table configuration(year number,week number,config_num number,loc_ind varchar2(30));

Table created.

Elapsed: 00:00:00.17
chajol@taxtest>alter table configuration add constraint c_pk primary key(year, config_num);

Table altered.

Elapsed: 00:00:00.12
chajol@taxtest>INSERT INTO CONFIGURATION(YEAR,WEEK,CONFIG_NUM,LOC_IND)VALUES(2011,3,211,'SIN');

1 row created.

Elapsed: 00:00:00.02
chajol@taxtest>INSERT INTO CONFIGURATION(YEAR,WEEK,CONFIG_NUM,LOC_IND)VALUES(2012,3,211,'SIN');

1 row created.

Elapsed: 00:00:00.00
chajol@taxtest>INSERT INTO CONFIGURATION(YEAR,WEEK,CONFIG_NUM,LOC_IND)VALUES(2011,5,211,'SIN');
INSERT INTO CONFIGURATION(YEAR,WEEK,CONFIG_NUM,LOC_IND)VALUES(2011,5,211,'SIN')
*
ERROR at line 1:
ORA-00001: unique constraint (CHAJOL.C_PK) violated


Elapsed: 00:00:00.00
chajol@taxtest>INSERT INTO CONFIGURATION(YEAR,WEEK,CONFIG_NUM,LOC_IND)VALUES(2011,3,212,'SIN');

1 row created.

Elapsed: 00:00:00.00
chajol@taxtest>INSERT INTO CONFIGURATION(YEAR,WEEK,CONFIG_NUM,LOC_IND)VALUES(2011,3,211,'NCE');
INSERT INTO CONFIGURATION(YEAR,WEEK,CONFIG_NUM,LOC_IND)VALUES(2011,3,211,'NCE')
*
ERROR at line 1:
ORA-00001: unique constraint (CHAJOL.C_PK) violated


Elapsed: 00:00:00.00
chajol@taxtest>

Tom Kyte
February 01, 2012 - 4:50 pm UTC

I know that :)

that is why I want them to "show their work"

by showing their work - their mistake will become immediately obvious to them.


that is why I always push for a TEST CASE cut and pasted right from the screen - no editing allowed. less mistakes that way...

But do they?

Chuck, February 01, 2012 - 9:39 pm UTC

Well I knew that you knew. :D

Full info

Anshul Agarwal, February 02, 2012 - 1:04 am UTC

Hi Tom,
Thanks for ur nice reply.
Now, let me provide the whole scenario :
The below is the whole script for my problem table

ALTER TABLE MEDS.MEDS_CONFIG
DROP PRIMARY KEY CASCADE;
DROP TABLE MEDS.MEDS_CONFIG CASCADE CONSTRAINTS;

CREATE TABLE MEDS.MEDS_CONFIG
(
YEAR NUMBER(4),
WEEK NUMBER(2),
CONFIG_NUM NUMBER(9),
MODULE VARCHAR2(6 BYTE),
SERVICE_POINT VARCHAR2(15 BYTE),
ORDER_TYPE VARCHAR2(3 BYTE),
ORDER_REF VARCHAR2(16 BYTE),
CUSTOMER_DEF_CODE VARCHAR2(25 BYTE),
SERVICE_TYPE VARCHAR2(50 BYTE),
CUSTOMER_CODE VARCHAR2(14 BYTE),
PROFILE_ID VARCHAR2(25 BYTE),
SUPERVISION_SITE VARCHAR2(3 BYTE),
CONFIGURATION VARCHAR2(20 BYTE),
ACTION VARCHAR2(5 BYTE),
SVC VARCHAR2(10 BYTE),
PI NUMBER(2),
PO NUMBER(2),
PU VARCHAR2(2 BYTE),
DLCI VARCHAR2(5 BYTE),
NTN VARCHAR2(20 BYTE),
DNIC VARCHAR2(4 BYTE),
USER_REP VARCHAR2(25 BYTE),
NID VARCHAR2(5 BYTE),
GID VARCHAR2(5 BYTE),
MNEMO VARCHAR2(15 BYTE),
MAC VARCHAR2(15 BYTE),
SAP VARCHAR2(15 BYTE),
CONFIG_COMMENTS VARCHAR2(155 BYTE),
MESSAGE VARCHAR2(200 BYTE),
PATCH VARCHAR2(1 BYTE),
RECORD_STATUS VARCHAR2(10 BYTE),
FLAG1 VARCHAR2(2 BYTE),
FLAG2 VARCHAR2(10 BYTE),
FLAG3 VARCHAR2(1 BYTE),
FLAG4 VARCHAR2(1 BYTE),
DATED VARCHAR2(10 BYTE),
SEQ VARCHAR2(10 BYTE),
ACSN VARCHAR2(10 BYTE),
SCHEDULE_DATE DATE,
SCHEDULE_TIME DATE,
ON_DUTY VARCHAR2(3 BYTE),
RESCHEDULE_CODE VARCHAR2(3 BYTE),
OPAS_UPD_FLAG VARCHAR2(1 BYTE),
CONF_LAST_MODIF VARCHAR2(30 BYTE),
OPAS_ERR VARCHAR2(20 BYTE),
LAST_UPD_D DATE DEFAULT sysdate,
LOC_IND VARCHAR2(5 BYTE),
QCKSTART VARCHAR2(15 BYTE),
CONFIG_TYPE VARCHAR2(3 BYTE),
PE NUMBER(2),
CHANNEL NUMBER(2),
FIRST_TS NUMBER(2),
LAST_TS NUMBER(2),
PROJECT_ID VARCHAR2(16 BYTE),
APPLN_CODE VARCHAR2(14 BYTE),
SNA_MNEMO VARCHAR2(8 BYTE),
V_PU VARCHAR2(2 BYTE),
V_NTN VARCHAR2(20 BYTE),
V_SNA_MNEMO VARCHAR2(8 BYTE),
NSAP VARCHAR2(26 BYTE),
ATMIF VARCHAR2(5 BYTE),
VPI NUMBER(5),
VCI NUMBER(5),
MEGAPLEX_NAME VARCHAR2(20 BYTE),
ACCESS_CARD_NO NUMBER(2),
ACCESS_PORT_NO NUMBER(2),
PP_PORT_NO NUMBER(2),
SERV_ORIG VARCHAR2(15 BYTE),
FLAG5 VARCHAR2(1 BYTE),
CSP_CLASS VARCHAR2(20 BYTE),
HSIF VARCHAR2(8 BYTE),
PO_1 NUMBER(2),
PO_2 NUMBER(2),
PO_3 NUMBER(2),
PO_4 NUMBER(2),
PO_5 NUMBER(2),
PO_6 NUMBER(2),
PO_7 NUMBER(2),
IMA_GROUP NUMBER(2),
IMA VARCHAR2(1 BYTE),
OPAS_UPDATED VARCHAR2(1 BYTE),
CAR VARCHAR2(35 BYTE),
CREATOR VARCHAR2(30 BYTE),
INPUT_DATE DATE,
CLOSE_DATE DATE,
CREATOR_LOC VARCHAR2(3 BYTE),
ACTIVATED VARCHAR2(1 BYTE) DEFAULT 'N'
)
TABLESPACE USERS
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 110M
NEXT 1M
MINEXTENTS 1
MAXEXTENTS 200
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;


CREATE UNIQUE INDEX MEDS.PK_MEDS_CONFIG ON MEDS.MEDS_CONFIG
(YEAR, WEEK, CONFIG_NUM, LOC_IND)
LOGGING
TABLESPACE MEDS_INDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 10M
NEXT 600K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
NOPARALLEL;


CREATE INDEX MEDS.ORDER_REF_CONFIG ON MEDS.MEDS_CONFIG
(ORDER_REF)
LOGGING
TABLESPACE MEDS_INDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 600K
NEXT 600K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
NOPARALLEL;


CREATE INDEX MEDS.BITMAP_WEEK_CONFIG ON MEDS.MEDS_CONFIG
(WEEK)
LOGGING
TABLESPACE MEDS_INDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 20K
NEXT 20K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
NOPARALLEL;


CREATE INDEX MEDS.MEDS_CONFIG_NTN ON MEDS.MEDS_CONFIG
(NTN)
LOGGING
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 20K
NEXT 20K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
NOPARALLEL;


CREATE INDEX MEDS.MEDS_CONFIG_MODULE ON MEDS.MEDS_CONFIG
(MODULE)
LOGGING
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 20K
NEXT 20K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
NOPARALLEL;


CREATE OR REPLACE TRIGGER MEDS."T_MEDS_CONFIG" BEFORE INSERT OR UPDATE ON MEDS.MEDS_CONFIG REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW
declare
timestamp$x date := sysdate;
site$x varchar2(128) := dbms_reputil.global_name;
loc_ind$x varchar2(5);
begin
-- don't fire if a snapshot refreshing;
-- don't fire if a master and replication is turned off
-- if (MEDS.MEDS_CONFIG$RP.active) then
if (dbms_reputil.replication_is_on) then
begin
select site into loc_ind$x from meds_site
where site_instance||'.WORLD' = site$x;
exception
when others then loc_ind$x := '?';
end;
if inserting then
-- set site and timestamp columns.
:new.loc_ind := loc_ind$x;
:new.last_upd_d := timestamp$x;
-- RFC:NC10509: Changes
:new.input_date := sysdate;
elsif updating then
if (:old.module = :new.module or
(:old.module is null and :new.module is null)) and
(:old.service_point = :new.service_point or
(:old.service_point is null and :new.service_point is null)) and
(:old.order_type = :new.order_type or
(:old.order_type is null and :new.order_type is null)) and
(:old.order_ref = :new.order_ref or
(:old.order_ref is null and :new.order_ref is null)) and
(:old.customer_def_code = :new.customer_def_code or
(:old.customer_def_code is null and :new.customer_def_code is null)) and
(:old.service_type = :new.service_type or
(:old.service_type is null and :new.service_type is null)) and
(:old.customer_code = :new.customer_code or
(:old.customer_code is null and :new.customer_code is null)) and
(:old.profile_id = :new.profile_id or
(:old.profile_id is null and :new.profile_id is null)) and
(:old.supervision_site = :new.supervision_site or
(:old.supervision_site is null and :new.supervision_site is null)) and
(:old.configuration = :new.configuration or
(:old.configuration is null and :new.configuration is null)) and
(:old.action = :new.action or
(:old.action is null and :new.action is null)) and
(:old.svc = :new.svc or
(:old.svc is null and :new.svc is null)) and
(:old.pi = :new.pi or
(:old.pi is null and :new.pi is null)) and
(:old.po = :new.po or
(:old.po is null and :new.po is null)) and
(:old.pu = :new.pu or
(:old.pu is null and :new.pu is null)) and
(:old.dlci = :new.dlci or
(:old.dlci is null and :new.dlci is null)) and
(:old.ntn = :new.ntn or
(:old.ntn is null and :new.ntn is null)) and
(:old.dnic = :new.dnic or
(:old.dnic is null and :new.dnic is null)) and
(:old.user_rep = :new.user_rep or
(:old.user_rep is null and :new.user_rep is null)) and
(:old.nid = :new.nid or
(:old.nid is null and :new.nid is null)) and
(:old.gid = :new.gid or
(:old.gid is null and :new.gid is null)) and
(:old.mnemo = :new.mnemo or
(:old.mnemo is null and :new.mnemo is null)) and
(:old.mac = :new.mac or
(:old.mac is null and :new.mac is null)) and
(:old.sap = :new.sap or
(:old.sap is null and :new.sap is null)) and
(:old.config_comments = :new.config_comments or
(:old.config_comments is null and :new.config_comments is null)) and
(:old.message = :new.message or
(:old.message is null and :new.message is null)) and
(:old.patch = :new.patch or
(:old.patch is null and :new.patch is null)) and
(:old.record_status = :new.record_status or
(:old.record_status is null and :new.record_status is null)) and
(:old.flag1 = :new.flag1 or
(:old.flag1 is null and :new.flag1 is null)) and
(:old.flag2 = :new.flag2 or
(:old.flag2 is null and :new.flag2 is null)) and
(:old.flag3 = :new.flag3 or
(:old.flag3 is null and :new.flag3 is null)) and
(:old.flag4 = :new.flag4 or
(:old.flag4 is null and :new.flag4 is null)) and
(:old.dated = :new.dated or
(:old.dated is null and :new.dated is null)) and
(:old.seq = :new.seq or
(:old.seq is null and :new.seq is null)) and
(:old.acsn = :new.acsn or
(:old.acsn is null and :new.acsn is null)) and
(:old.schedule_date = :new.schedule_date or
(:old.schedule_date is null and :new.schedule_date is null)) and
(:old.schedule_time = :new.schedule_time or
(:old.schedule_time is null and :new.schedule_time is null)) and
(:old.on_duty = :new.on_duty or
(:old.on_duty is null and :new.on_duty is null)) and
(:old.reschedule_code = :new.reschedule_code or
(:old.reschedule_code is null and :new.reschedule_code is null)) and
(:old.opas_upd_flag = :new.opas_upd_flag or
(:old.opas_upd_flag is null and :new.opas_upd_flag is null)) and
(:old.conf_last_modif = :new.conf_last_modif or
(:old.conf_last_modif is null and :new.conf_last_modif is null)) and
(:old.opas_err = :new.opas_err or
(:old.opas_err is null and :new.opas_err is null)) and
(:old.loc_ind = :new.loc_ind or
(:old.loc_ind is null and :new.loc_ind is null)) and
(:old.LAST_UPD_D = :new.LAST_UPD_D or
(:old.LAST_UPD_D is null and
:new.LAST_UPD_D is null)) and
(:old.qckstart = :new.qckstart or
(:old.qckstart is null and :new.qckstart is null)) and
(:old.year = :new.year or
(:old.year is null and :new.year is null)) and
(:old.week = :new.week or
(:old.week is null and :new.week is null)) and
(:old.config_num = :new.config_num or
(:old.config_num is null and :new.config_num is null)) and
(:old.config_type = :new.config_type or
(:old.config_type is null and :new.config_type is null)) then
-- column group not changed; do nothing
null;
else
-- column group was changed; set loc_ind and timestamp columns.
-- :new.loc_ind := loc_ind$x;
:new.LAST_UPD_D := timestamp$x;
-- consider time synchronisation problems
-- previous update to this row may have originated
-- from a site with a clock time ahead of the local clock time
if :old.LAST_UPD_D is not null and
:old.LAST_UPD_D > :new.LAST_UPD_D then
:new.LAST_UPD_D := :old.LAST_UPD_D + 1/86400;
elsif :old.LAST_UPD_D is not null and
:old.LAST_UPD_D = :new.LAST_UPD_D and
(:old.loc_ind is null or :old.loc_ind != :new.loc_ind) then
:new.LAST_UPD_D := :old.LAST_UPD_D + 1/86400;
end if;
end if;
end if;
end if;
end;
/


CREATE OR REPLACE TRIGGER MEDS.T_MEDS_CONFIG_XML_UPDATE
before insert ON MEDS.MEDS_CONFIG for each row
declare
v_rec varchar2(1):=null;
v_rec1 varchar2(1):=null;
ordertype meds_config.ORDER_TYPE%type;
orderref meds_config.ORDER_REF%type;
type r_cursor is ref cursor;
c_ord r_cursor;
begin
if :new.ORDER_REF is null then
ordertype:=substr(:new.config_comments,1,3);
if :new.config_comments like ('%DLCI - CIR XNR%') then
orderref:=trim(substr(:new.config_comments,instr(:new.config_comments,' DLCI - CIR XNR ')+16));
open c_ord for select 'x' from meds_cir where order_ref=orderref;
fetch c_ord into v_rec;
if c_ord%found then
if ordertype in ('MIG','MOD') then
:new.flag1:='D' ;
else
null;
end if;
end if;
close c_ord;
elsif :new.config_comments like ('%VPI/VCI - AUC XNR%') then
orderref:=trim(substr(:new.config_comments,instr(:new.config_comments,' VPI/VCI - AUC XNR ')+19));
open c_ord for select 'y' from meds_auc where order_ref=orderref;
fetch c_ord into v_rec1;
if c_ord%found then
if ordertype in ('MIG','MOD') then
:new.flag1:='D' ;
else
null;
end if;
end if;
close c_ord;
else
null;
end if;
else
null;
end if;
end;
/


CREATE OR REPLACE TRIGGER MEDS."MEDS_CONFIG_CONFLICT" BEFORE INSERT OR UPDATE ON "MEDS"."MEDS_CONFIG" REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW
begin
IF DBMS_REPUTIL.FROM_REMOTE = FALSE then
:NEW.LAST_UPD_D := SYSDATE;
END IF;
end;
/


CREATE OR REPLACE TRIGGER MEDS.T_UPDATE_MEDS_CONFIG
BEFORE UPDATE ON MEDS.MEDS_CONFIG REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW
/* - Description : This trigger is created for the RFC 2006/J00500, To update the creator field of MEDS_CONFIG table, wherever Creator is "OPAS" and record are modified by other user. */
-- Created By : Bhanu Singh
-- Creation Date: 13 March, 2007
-- Modified By : Davinder Brar and removed package variable , Autonomous Transation used.
-- Modified Date: 13 March, 2007
BEGIN
IF :OLD.creator = 'OPAS' AND :NEW.creator='OPAS' THEN
:NEW.creator :=user;
END IF;
END T_UPDATE_MEDS_CONFIG;
/


DROP PUBLIC SYNONYM MEDS_CONFIG;

CREATE PUBLIC SYNONYM MEDS_CONFIG FOR MEDS.MEDS_CONFIG;


ALTER TABLE MEDS.MEDS_CONFIG ADD (
CONSTRAINT CHK_MEDS_CONF_ORDTYP
CHECK (order_type in ('ADD','MOD','MIG','CES','EXP')),
CONSTRAINT CHK_MEDS_CONF_ACTN
CHECK (action in ('ADD','DEL','UPD')),
CONSTRAINT CHECK_IMA
CHECK ( ima in ('Y','N')),
CONSTRAINT CHK_UPD
CHECK (opas_updated in ('Y','N')),
CONSTRAINT CHK_MEDS_CONF_ONDTY
CHECK (on_duty in ('NCE','ATL','SIN','SYD','DEL','CAI')),
CONSTRAINT CHECK_ACTIVATED
CHECK ( activated in ('Y','N') ),
CONSTRAINT PK_MEDS_CONFIG
PRIMARY KEY
(YEAR, WEEK, CONFIG_NUM, LOC_IND)
USING INDEX
TABLESPACE MEDS_INDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 10M
NEXT 600K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
));

GRANT SELECT ON MEDS.MEDS_CONFIG TO MEDS_READONLY;

GRANT DELETE, INSERT, UPDATE ON MEDS.MEDS_CONFIG TO MEDS_WRITE;

GRANT ALTER, DELETE, INSERT, SELECT, UPDATE ON MEDS.MEDS_CONFIG TO OPERATOR;

GRANT SELECT ON MEDS.MEDS_CONFIG TO QUERY;




Now when I use the 5 insert queries i get unique constraint voilation error for 3 and 5 cases.

INSERT INTO MEDS_CONFIG(YEAR,WEEK,CONFIG_NUM,LOC_IND)VALUES(2011,3,211,'SIN');

INSERT INTO MEDS_CONFIG(YEAR,WEEK,CONFIG_NUM,LOC_IND)VALUES(2012,3,211,'SIN');--INSERTED

INSERT INTO MEDS_CONFIG(YEAR,WEEK,CONFIG_NUM,LOC_IND)VALUES(2011,5,211,'SIN');--UNIQUE CONSTRAINT ERR

INSERT INTO MEDS_CONFIG(YEAR,WEEK,CONFIG_NUM,LOC_IND)VALUES(2011,3,212,'SIN');--INSERTED

INSERT INTO MEDS_CONFIG(YEAR,WEEK,CONFIG_NUM,LOC_IND)VALUES(2011,3,211,'NCE');--UNIQUE ERR



Please Help.....
Tom Kyte
February 02, 2012 - 7:48 am UTC

I didn't want the entire scenario, I wanted the SMALLEST POSSIBLE BIT OF STUFF to reproduce the issue (a test case, a small thing to debug, what developers put together to figure things out with)

what the heck is this????


begin
         select site into loc_ind$x from meds_site
            where site_instance||'.WORLD' = site$x;
      exception
         when others then loc_ind$x := '?';
      end;


You might as well just code:

loc_ind$x := '?';

and be done with it, you've basically said in your code "we don't care if it works or not, it isn't important". So, why BOTHER at all???? Your error handling is horrific - I would never let this code go production, it would fail a code review straight off (code reviews go really fast when you look for when others like this - you can save so much time this way - I don't have to read the rest of the code at all!! instant reject)




Since I cannot compile your code - you reference tables in your triggers that I quite simply DO NOT HAVE.

Since I cannot see the real error message - because you won't show us, we did ask for you to run this in sqlplus and cut and paste.

Since you gave me about 50 times as much code as you should (people don't want your storage clause, I don't have your tablespaces, I'm pretty darn sure we don't need 100 columns in the create table to demonstrate 4 columns violating a unique constraint.....) - and I'm not going to try to decipher it all in my head...


We'll have to wait for you to post something reasonable to work with - and make sure to reproduce it for us - so we can see things like the NAME of the constraint (the actual error). And remove ANYTHING and EVERYTHING that is not *relevant*. Like create public synonyms, triggers that don't contribute to the issue, indexes that are not part of the problem, columns that don't count, etc etc etc - you know, a test case that demonstrates very simply what your problem is.


99 times out of 100 - when I put together such a test case myself, I find my own mistake :)


I rather suspect a bit of code like this:

     if (dbms_reputil.replication_is_on) then
      begin
         select site into loc_ind$x from meds_site
            where site_instance||'.WORLD' = site$x;
      exception
         when others then loc_ind$x := '?';
      end;
      if inserting then
         -- set site and timestamp columns.
         :new.loc_ind := loc_ind$x;


(ugh, that ugly bit of nasty code)

see how it sets :new.loc_ind in the trigger, the values you supply in your insert statement - your SIN, NCE values - they don't count - you overwrite them. That is probably the root cause. I'm pretty close to 100% sure on that.

I hate triggers.
http://www.oracle.com/technetwork/issue-archive/2008/08-sep/o58asktom-101055.html

Not Relating to this - error Message

djb, February 02, 2012 - 4:46 am UTC

This comes up when trying to view the next page (16-30):-

Error ERR-7620 Could not determine workspace for application (0).
Expecting p_company or wwv_flow_company cookie to contain security group id of application owner.

Odd ? Known bug ?
Tom Kyte
February 02, 2012 - 7:55 am UTC

I cannot reproduce, does it reproduce for you? what are the steps I need to take to see it?

Error

DJB, February 02, 2012 - 10:12 am UTC

It appeared a number of times when I clicked 'next' for 16 - 30. Working ok now. Odd.

good

neetu, March 11, 2012 - 10:32 am UTC

this is the very useful tips for us

foreign key referencing composite primary key

Anand Iyer, July 16, 2012 - 1:39 am UTC

If you want your FK to refer only one of the columns of the composite key then you cant do that unless you create a unique+not null constraint on that specific column.

after doing this you will have to make your FK refer this column.

STEPS-
1.create a unique+not null constraint on the parent table's column that you want to refer.

2. make your child table's column refer this column.

Note- A child column has to always refer all of the columns of the composite key(be it primary or unique composite)
Tom Kyte
July 16, 2012 - 3:52 pm UTC

you just need unique, the not null is not necessary.



Tip: If you have a primary key such that some subset of that primary key is itself unique, you have done your model wrong.

Ragu, April 05, 2013 - 8:36 am UTC


creating composite PK in oracle data modeler

roderick, December 30, 2013 - 5:23 pm UTC

can you tell me steps how to create a composite Primary Key in oracle data modeler

A reader, February 11, 2016 - 9:28 am UTC


NUR ATHIRAH BINTI AZAHARI, November 24, 2017 - 3:53 am UTC

a table need two primary key to operate. how to create table with two primary key?
eg:
Table: MedicalHistory
PK-MedHis_Id
PK,FK-Pat_Id
Chris Saxon
November 24, 2017 - 8:06 am UTC

You can't. A table can only have one primary key.

But you can create as many unique constraints as you want. So choose one set of columns to be the primary key. And put unique and not null constraints on the others.

A reader, November 09, 2018 - 9:28 am UTC

nice

How to add primary key constraint on two columns to make a composite key column?

JacknJill, October 15, 2020 - 5:16 am UTC

Hi everyone,

Can anyone let me know how to add a primary key constraint on two columns to make a composite key column in a table?

Instead of writing so many lines, kindly provide the answer as short as possible with code.

JacknJill
Chris Saxon
October 15, 2020 - 4:07 pm UTC

Tom showed you how to do this with create table and alter table in the original answer.

What exactly are you struggling with?

Can Primary Key be on specific field value?

Simon Platten, March 10, 2021 - 5:06 pm UTC

I have a requirement where I need to know if the Primary key could be the result of a unique auto incremented field and another field which will have the value of 0 to indicate it is the most recent and 1 to indicate its old.

Is this possible?
Chris Saxon
March 10, 2021 - 7:26 pm UTC

If you mean you'll have something like this:

1234, 0
1234, 1
1234, 1
1234, 1


Then you can have a primary key over these two values.

If you want to ensure that only one row can have the value 0 for a particular ID, but many can have value 1 you'll need to:

- Have an expression that returns the id when value = 1 and null otherwise
- Use this in a unique function-based index or virtual column with a unique constraint on it

As the expression returns null for the duplicate rows, you can't base a primary key on it. All PK values must be non-null.