Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Primary key and Unique key

412263May 19 2005 — edited May 20 2005

We can have only one primary key and many unique keys on a table.

Primary key is same as a unique key with no null values.

So can't we have multiple primary keys by making multiple unique keys with not null constraint on the columns involved in the unique keys.

Frank Kulash

Answer

Hi,

Vemula Muni wrote:
Source table Data : ps_cts_iqn_empl_wo
EMPLID Work_order_Id Project_Id Start_Dt End_Dt Standard_Rate CTS_WO_OT_RATE Vendor_ID
570716 12456 530 11-Aug-19 31-Aug-19 10 15 45621
570716 12452 530 11-Aug-19 31-Aug-19 14 15 45621
570717 12451 210 10-AUG-19 13-AUG-19 15 18 43221
570717 12450 510 15-JUL-19 28-JUL-19 15 18 43221
570718 12451 510 15-Aug-19 28-Aug-19 15 18 43221
Target Table :
EMPLID Project_Id report_due_date Work_order_Id WO_End_Dt WO_Standard_Rate WO_OT_Rate Vendor_id
570716 530 14-Aug-19 
570717 510 14-Aug-19 
Required Output :
EMPLID Project_Id report_due_date Work_order_Id End_Dt Standard_Rate CTS_WO_OT_RATE  Vendor_id
570716 530 14-Aug-19 12456 31-Aug-19 10 15 45621
570717 510 14-Aug-19 12451 13-Aug-19 15 18 43221

CREATETABLEps_cts_iqn_empl_wo(EMPLIDVARCHAR2(11)NOTNULL,
WORK_ORDER_IDVARCHAR2(15)NOTNULL,
PROJECT_IDVARCHAR2(15)NOTNULL,
START_DTDATE,
END_DTDATE,
STANDARD_RATEDECIMAL(15,2)NULL,
CTS_WO_OT_RATEDECIMAL(15,2)NULL,
VENDOR_IDVARCHAR2(10)NULL);
Insertintops_cts_iqn_empl_wo(EMPLID,WORK_ORDER_ID,PROJECT_ID,START_DT,END_DT,STANDARD_RATE,CTS_WO_OT_RATE,VENDOR_ID)values('570716','12456','530',to_date('11-AUG-2019','DD-MON-YYYY'),to_date('31-AUG-2019','DD-MON-YYYY'),10,15,'45621');
Insertintops_cts_iqn_empl_wo(EMPLID,WORK_ORDER_ID,PROJECT_ID,START_DT,END_DT,STANDARD_RATE,CTS_WO_OT_RATE,VENDOR_ID)values('570716','12452','530',to_date('11-AUG-2019','DD-MON-YYYY'),to_date('31-AUG-2019','DD-MON-YYYY'),14,15,'45621');
Insertintops_cts_iqn_empl_wo(EMPLID,WORK_ORDER_ID,PROJECT_ID,START_DT,END_DT,STANDARD_RATE,CTS_WO_OT_RATE,VENDOR_ID)values('570717','12451','510',to_date('10-AUG-2019','DD-MON-YYYY'),to_date('13-AUG-2019','DD-MON-YYYY'),15,18,'43221');
Insertintops_cts_iqn_empl_wo(EMPLID,WORK_ORDER_ID,PROJECT_ID,START_DT,END_DT,STANDARD_RATE,CTS_WO_OT_RATE,VENDOR_ID)values('570717','12450','510',to_date('15-JUL-2019','DD-MON-YYYY'),to_date('28-JUL-2019','DD-MON-YYYY'),15,18,'43221');
Insertintops_cts_iqn_empl_wo(EMPLID,WORK_ORDER_ID,PROJECT_ID,START_DT,END_DT,STANDARD_RATE,CTS_WO_OT_RATE,VENDOR_ID)values('570718','12451','510',to_date('15-AUG-2019','DD-MON-YYYY'),to_date('28-AUG-2019','DD-MON-YYYY'),15,18,'43221');
CREATETABLEPS_CT_IQN_ACC_STG(EMPLIDVARCHAR2(11)NOTNULL,
PROJECT_IDVARCHAR2(15)NOTNULL,
REPORT_DUE_DATEDATE,
WORK_ORDER_IDVARCHAR2(15)NULL,
END_DTDATE,
STANDARD_RATEDECIMAL(15,2)NULL,
CTS_WO_OT_RATEDECIMAL(15,2)NULL,
VENDOR_IDVARCHAR2(10)NULL)
InsertintoPS_CT_IQN_ACC_STG(EMPLID,PROJECT_ID,REPORT_DUE_DATE,WORK_ORDER_ID,END_DT,STANDARD_RATE,CTS_WO_OT_RATE,VENDOR_ID)values('570716','530',to_date('14-AUG-2019','DD-MON-YYYY'),null,null,null,null,null);
InsertintoPS_CT_IQN_ACC_STG(EMPLID,PROJECT_ID,REPORT_DUE_DATE,WORK_ORDER_ID,END_DT,STANDARD_RATE,CTS_WO_OT_RATE,VENDOR_ID)values('570717','510',to_date('14-AUG-2019','DD-MON-YYYY'),null,null,null,null,null);

CREATE TABLE ps_cts_iqn_empl_wo (EMPLID VARCHAR2(11) NOT NULL,  WORK_ORDER_ID VARCHAR2(15) NOT NULL,  PROJECT_ID VARCHAR2(15) NOT NULL,  START_DT DATE,  END_DT DATE,  STANDARD_RATE DECIMAL(15, 2) NULL,  CTS_WO_OT_RATE DECIMAL(15, 2) NULL,  VENDOR_ID VARCHAR2(10) NULL) ;      Insert into ps_cts_iqn_empl_wo (EMPLID,WORK_ORDER_ID,PROJECT_ID,START_DT,END_DT,STANDARD_RATE,CTS_WO_OT_RATE,VENDOR_ID) values ('570716','12456','530',to_date('11-AUG-2019','DD-MON-YYYY'),to_date('31-AUG-2019','DD-MON-YYYY'),10,15,'45621');   Insert into ps_cts_iqn_empl_wo (EMPLID,WORK_ORDER_ID,PROJECT_ID,START_DT,END_DT,STANDARD_RATE,CTS_WO_OT_RATE,VENDOR_ID) values ('570716','12452','530',to_date('11-AUG-2019','DD-MON-YYYY'),to_date('31-AUG-2019','DD-MON-YYYY'),14,15,'45621');  Insert into ps_cts_iqn_empl_wo (EMPLID,WORK_ORDER_ID,PROJECT_ID,START_DT,END_DT,STANDARD_RATE,CTS_WO_OT_RATE,VENDOR_ID) values ('570717','12451','510',to_date('10-AUG-2019','DD-MON-YYYY'),to_date('13-AUG-2019','DD-MON-YYYY'),15,18,'43221');   Insert into ps_cts_iqn_empl_wo (EMPLID,WORK_ORDER_ID,PROJECT_ID,START_DT,END_DT,STANDARD_RATE,CTS_WO_OT_RATE,VENDOR_ID) values ('570717','12450','510',to_date('15-JUL-2019','DD-MON-YYYY'),to_date('28-JUL-2019','DD-MON-YYYY'),15,18,'43221');   Insert into ps_cts_iqn_empl_wo (EMPLID,WORK_ORDER_ID,PROJECT_ID,START_DT,END_DT,STANDARD_RATE,CTS_WO_OT_RATE,VENDOR_ID) values ('570718','12451','510',to_date('15-AUG-2019','DD-MON-YYYY'),to_date('28-AUG-2019','DD-MON-YYYY'),15,18,'43221');       CREATE TABLE PS_CT_IQN_ACC_STG (EMPLID VARCHAR2(11) NOT NULL,  PROJECT_ID VARCHAR2(15) NOT NULL,  REPORT_DUE_DATE DATE,  WORK_ORDER_ID VARCHAR2(15) NULL,  END_DT DATE,  STANDARD_RATE DECIMAL(15, 2) NULL,  CTS_WO_OT_RATE DECIMAL(15, 2) NULL,  VENDOR_ID VARCHAR2(10) NULL)      Insert into PS_CT_IQN_ACC_STG (EMPLID,PROJECT_ID,REPORT_DUE_DATE,WORK_ORDER_ID,END_DT,STANDARD_RATE,CTS_WO_OT_RATE,VENDOR_ID) values ('570716','530',to_date('14-AUG-2019','DD-MON-YYYY'),null,null,null,null,null);  Insert into PS_CT_IQN_ACC_STG (EMPLID,PROJECT_ID,REPORT_DUE_DATE,WORK_ORDER_ID,END_DT,STANDARD_RATE,CTS_WO_OT_RATE,VENDOR_ID) values ('570717','510',to_date('14-AUG-2019','DD-MON-YYYY'),null,null,null,null,null);


Query
UPDATE PS_CT_IQN_ACC_STG stg
SET (Work_order_Id,WO_End_Dt,WO_Standard_Rate,WO_OT_Rate,Vendor_id) = (select Work_order_Id,End_Dt,Standard_Rate,CTS_WO_OT_RATE,Vendor_ID from ps_cts_iqn_empl_wo WO1
where WO1.Work_order_Id= (select max(Work_order_Id) from ps_cts_iqn_empl_wo WO where WO.EMPLID = STG.EMPLID
AND WO.project_id = STG.project_id and stg.report_due_date between WO.start_dt and WO.end_dt ))

Thanks for posting the CREATE TABLE and INSERT statements. Don't forget to post your Oracle version.

See the Forum FAQ:

When I run the UPDATE statement you posted with the sample data you posted, I get this error:

SET (Work_order_Id,WO_End_Dt,WO_Standard_Rate,WO_OT_Rate,Vendor_id) = (select Work_order_Id,End_Dt,Standard_Rate,CTS_WO_OT_RATE,Vendor_ID from ps_cts_iqn_empl_wo WO1

ERROR at line 2:

ORA-00904: "WO_OT_RATE": invalid identifier

There is a column called CTS_WO_OT_RATE, but not WO_OT_RATE.

Marked as Answer by Vemula Muni · Sep 27 2020

Vemula Muni

Hi @"Frank Kulash", please find the modified query.

Query

UPDATE PS_CT_IQN_ACC_STG stg

SET (Work_order_Id,End_Dt,Standard_Rate,CTS_WO_OT_Rate,Vendor_id) = (select Work_order_Id,End_Dt,Standard_Rate,CTS_WO_OT_RATE,Vendor_ID from ps_cts_iqn_empl_wo WO1

where WO1.Work_order_Id= (select max(Work_order_Id) from ps_cts_iqn_empl_wo WO where WO.EMPLID = STG.EMPLID

AND WO.project_id = STG.project_id and stg.report_due_date between WO.start_dt and WO.end_dt ))

I hav

L. Fernigrini

I've just run the script you posted, adding a missing ";" to the second create table, and got no errors:

Can you show us where do you get the ORA-01407 error??

Vemula Muni

Hi @L. Fernigrini, when i am trying to execute below query I am getting that error.

Query

UPDATE PS_CT_IQN_ACC_STG stg

SET (Work_order_Id,End_Dt,Standard_Rate,CTS_WO_OT_Rate,Vendor_id) = (select Work_order_Id,End_Dt,Standard_Rate,CTS_WO_OT_RATE,Vendor_ID from ps_cts_iqn_empl_wo WO1

where WO1.Work_order_Id= (select max(Work_order_Id) from ps_cts_iqn_empl_wo WO where WO.EMPLID = STG.EMPLID

AND WO.project_id = STG.project_id and stg.report_due_date between WO.start_dt and WO.end_dt ))

I hav

L. Fernigrini

that's strange, with the sample data you posted, I'm not getting that error:

Can you show the entire script and it's output? Please post the entire error message.

Can you tell us full Oracle version (e.g., 11.2.0.4).

Frank Kulash

Hi,

I don't get any error, either. I suspect you're not running the statements you posted.

What is the difference between this problem and the one you posted earlier today:

Need to alter Update Query

? The solutions from that problem do what you requested in this problem, too.

Vemula Muni

Hi @"L. Fernigrini", Could you please insert the below row to replicate the issue.

Insert into PS_CT_IQN_ACC_STG (EMPLID,PROJECT_ID,REPORT_DUE_DATE,WORK_ORDER_ID,END_DT,STANDARD_RATE,CTS_WO_OT_RATE,VENDOR_ID) values ('570716','530',to_date('13-AUG-2019','DD-MON-YYYY'),null,null,null,null,null);

Frank Kulash

Hi,

Vemula Muni wrote:

Hi L. Fernigrini, Could you please insert the below row to replicate the issue.

Is the new row in addition to the row that already has emplid='5070716', or instead of it?

Either way, I don't get any error when I run the UPDATE statement in reply #4.

ORA-01407 occurs when you try to set a NOT NULL column to NULL. The UPDATE statement you posted in reply #4 does not set any NOT NULL columns. If you're getting ORA-01407, then either your table or your UPDATE statement is different from what you said.

1 person found this helpful

L. Fernigrini

Still cannot reproduce the error.

Can you post the entire script that gives you error, and the output? And also include the database version. It "may" be some strange bug if you get that error message with the script you provided us.

1 - 9

Locked Post

New comments cannot be posted to this locked post.

Locked on Jun 17 2005

Added on May 19 2005

#general-database-discussions

8 comments

2,098 views

Oracle Database Discussions

Primary key and Unique key

Comments

Post Details