Avatar of Nakuru1234
Nakuru1234
 asked on

Oracle change Primary Key Constraint

Hello,

I created the following table but I need to alter the table and change the primary key constraint. I presently have one PK on PARAM_ID and I need to add another PK on the column CIR_TYPE. How can I accomplish that? Thanks for your help.

CREATE TABLE UTG.PARAMETER
(
  PARAM_ID         VARCHAR2(31 BYTE) CONSTRAINT NN_PARAM_PARAMID NOT NULL,
  LABEL            VARCHAR2(31 BYTE),
  TYPE             VARCHAR2(10 BYTE),
  VALUE            VARCHAR2(128 BYTE),
  MIN_LENGTH       NUMBER(4),
  MAX_LENGTH       NUMBER(4),
  VALID_SELECTION  VARCHAR2(512 BYTE),
  FORMAT           VARCHAR2(20 BYTE),
  CIR_TYPE         VARCHAR2(16 BYTE),
  DESCRIP          VARCHAR2(128 BYTE),
  ENABLED          VARCHAR2(10 BYTE)
)

TIA,
N
Oracle Database

Avatar of undefined
Last Comment
slightwv (䄆 Netminder)

8/22/2022 - Mon
flow01

You can only have 1 primairy key constraint on a table, but you can
add an extra unique key constraint

ALTER TABLE UTG.PARAMETER
add CONSTRAINT PARAMETER_UK1 UNIQUE (CIR_TYP);

-- maybe  you need an index on CIR_TYPE to keep performance in validating the constraint on level : check the documentation : it can be part of the add constraint clause
slightwv (䄆 Netminder)

Are you wanting to add CIR_TYPE to the existing PK so your new PK is:  PARAM_ID and CIR_TYPE?

https://forums.oracle.com/forums/thread.jspa?threadID=907737


One way to do it is:

1) Create a unique index with the new primary-key columns.
2) Disable and Drop the primary-key constraint
3) Add the new primary-key constraint using index (just created in step 1).
Nakuru1234

ASKER
Yes, you got that right. Do you have the correct syntax? How can I MODIFY this PL/SQL statement to add CIR_TYPE on the Primary key? I thought Oracle only allows one primary key constraint on a table...?

ALTER TABLE UTG.PARAMETER
  DROP CONSTRAINT XPKPARAMETER;

ALTER TABLE UTG.PARAMETER ADD (
  CONSTRAINT XPKPARAMETER
 PRIMARY KEY
 (PARAM_ID));

TIA,
N.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
slightwv (䄆 Netminder)

>>I thought Oracle only allows one primary key constraint on a table...?

It does.  You still have one PK but it is on two columns.  The pair must be unique not the individual columns.

ALTER TABLE UTG.PARAMETER ADD (
  CONSTRAINT XPKPARAMETER
 PRIMARY KEY
 (PARAM_ID,CIR_TYPE));
Nakuru1234

ASKER
I am getting an error message when I try to execute

PRIMARY KEY
 (PARAM_ID,CIR_TYPE))
Error at line 1
ORA-02260: table can have only one primary key

Script Terminated on line 1.
slightwv (䄆 Netminder)

Did you drop the existing one first?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Nakuru1234

ASKER
No, is this the correct syntax?

ALTER TABLE UTG.PARAMETER
 DROP PRIMARY KEY CASCADE;

ALTER TABLE UTG.PARAMETER ADD (
  CONSTRAINT XPKPARAMETER
 PRIMARY KEY
 (PARAM_ID,CIR_TYPE));
slightwv (䄆 Netminder)

>>No,

There is the reason for the error.

>>is this the correct syntax?

Did you try it on a test system?

You should always test things before you do them.  A simple test is all that is necessary:

drop table tab1 purge;
create table tab1(col1 char(1), col2 char(1), constraint tab1_pk primary key (col1));

alter table tab1 drop constraint tab1_pk;
alter table tab1 add constraint tab1_pk primary key (col1, col2);

Open in new window


Also: You need to make sure the new PK will be able to be created.  In other words, are you sure PARAM_ID,CIR_TYPE are unique for all existing rows?

select PARAM_ID,CIR_TYPE from UTG.PARAMETER group by PARAM_ID,CIR_TYPE having count(1) > 1;


If any rows come back, you will not be able to create the new PK.
Nakuru1234

ASKER
Yes, I tried on the test system. I never touch Production until I know everything has worked in Test.

I executed the SQL statement and No rows came back. I don't have to DROP the table as per your sql script? am assuming that was just an example...is that right?

select PARAM_ID,CIR_TYPE from UTG.PARAMETER group by PARAM_ID,CIR_TYPE having count(1) > 1;
no rows selected.

Then, I went ahead and executed this SQL script but i received an error message

ALTER TABLE UTG.PARAMETER drop CONSTRAINT XPKPARAMETER

ALTER TABLE UTG.PARAMETER ADD (
  CONSTRAINT XPKPARAMETER
 PRIMARY KEY
 (PARAM_ID,CIR_TYPE));


 PRIMARY KEY
 (PARAM_ID,CIR_TYPE))
Error at line 1
ORA-01735: invalid ALTER TABLE option

Script Terminated on line 1.


TIA,
N.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Nakuru1234

ASKER
I might have sent the wrong sql statement...this is what I executed

ALTER TABLE UTG.PARAMETER drop CONSTRAINT XPKPARAMETER

ALTER TABLE UTG.PARAMETER ADD (
  CONSTRAINT XPKPARAMETER
 PRIMARY KEY
 (PARAM_ID,CIR_TYPE))
Error at line 1
ORA-01735: invalid ALTER TABLE option

Script Terminated on line 1.
slightwv (䄆 Netminder)

>>I don't have to DROP the table

Why would you think you had to drop the table to change a constraint?

>>ALTER TABLE UTG.PARAMETER drop CONSTRAINT XPKPARAMETER

Are you missing a semi-colon at the end?

>>ALTER TABLE UTG.PARAMETER ADD (

Why did you add parans after the ADD?  Look at what I posted.
Nakuru1234

ASKER
I made the changes but now I got another error.

ALTER TABLE UTG.PARAMETER drop CONSTRAINT XPKPARAMETER;

ALTER TABLE UTG.PARAMETER ADD
  CONSTRAINT XPKPARAMETER
 PRIMARY KEY
 (PARAM_ID,CIR_TYPE));

ALTER TABLE UTG.PARAMETER drop CONSTRAINT XPKPARAMETER
Error at line 1
ORA-02273: this unique/primary key is referenced by some foreign keys

N.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
slightwv (䄆 Netminder)

>>ORA-02273: this unique/primary key is referenced by some foreign keys

Did you look up that error?  You have foreign key references you need to disable first.


You need to disable them then enable them after you recreate the new PK.

There is SQL here to generate the disable commands for you.

Save the output off then you know what to enable after you are done.

http://www.databasejournal.com/features/oracle/article.php/3665591/Finding-Foreign-Key-Constraints-in-Oracle.htm

select 'alter table '||a.owner||'.'||a.table_name||
            ' disable constraint '||a.constraint_name||';'
            from all_constraints a, all_constraints b
            where a.constraint_type = 'R'
            and a.r_constraint_name = b.constraint_name
            and a.r_owner  = b.owner
            and b.table_name = 'PARAMETER';
Nakuru1234

ASKER
select 'alter table '||a.owner||'.'||a.table_name||
            ' disable constraint '||a.constraint_name||';'
            from all_constraints a, all_constraints b
            where a.constraint_type = 'R'
            and a.r_constraint_name = b.constraint_name
            and a.r_owner  = b.owner
            and b.table_name = 'PARAMETER';



'ALTERTABLE'||A.OWNER||'.'||A.TABLE_NAME||'DISABLECONSTRAINT'||A.CONSTRAINT_NAME
--------------------------------------------------------------------------------
alter table UTG.FUNCTION_PARAMETER disable constraint R_PARAM_FUNCTPARAM;      
1 row selected.
Nakuru1234

ASKER
alter table UTG.FUNCTION_PARAMETER disable constraint R_PARAM_FUNCTPARAM;

table altered

But when I tried to execute the script again, I got the same error...

ALTER TABLE UTG.PARAMETER
Drop CONSTRAINT XPKPARAMETER
Error at line 1
ORA-02273: this unique/primary key is referenced by some foreign keys
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
slightwv (䄆 Netminder)

>>ORA-02273: this unique/primary key is referenced by some foreign keys

I thought you could disable contraints but I guess you cannot.

Looks like you will have to drop/recreate all foreign key constraints to do this.

Are you sure you cannot get away with a unique index on the two columns?  The only real difference is a unique index will allow null values.
Nakuru1234

ASKER
Well yeah.. if both PARAM_ID and CIR_TYPE will be PKs.. then their automatically needs to be unique Key. So I guess no need for a separate unique constraint...
slightwv (䄆 Netminder)

>>I guess no need for a separate unique constraint

The need would be that you would not have to drop and recreate all the FK constriants to change the PK constraint.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Nakuru1234

ASKER
I just need those 2 columns to become a compound PK. Is that possible? If yes, show me the sql syntax. Thanks.

N.
slightwv (䄆 Netminder)

>>If yes, show me the sql syntax.

Yes it is possible and you have the syntax:  http:#a38002667.

You just need to make sure the database is in a state to drop/recreate the new PK.  This means there are no Foreign Keys associated with the PK.

I thought you could just disable them, but I was mistaken.  You cannot. They need to be dropped.

To do this, you need to drop all foreign key constraints, drop the existing PK, create the new PK, then recreate all the old foreign Key constraints you previously dropped.
Nakuru1234

ASKER
To do this, you need to drop all foreign key constraints,
// How can I find out all the FK"s and DROP at the same time...

then recreate all the old foreign Key constraints you previously dropped
//Can I DISABLE the FK's instead of dropping them, if NO, how do I recreate them?
Your help has saved me hundreds of hours of internet surfing.
fblack61
slightwv (䄆 Netminder)

>>How can I find out all the FK"s and DROP at the same time...

The same SQL you ran in http:#a38002926.

Looks like you only have the one.

>>//Can I DISABLE the FK's instead of dropping them

Didn't you try that in http:#a38002935 ?  Did it work?

>>how do I recreate them?

With the proper create constraint syntax for that table.

You can get the exact DDL with:

SELECT DBMS_METADATA.GET_XML('CONSTRAINT','UTG','R_PARAM_FUNCTPARAM') from dual;

This should show you the Foreign Key syntax.
Nakuru1234

ASKER
It looks the constraint is not there in the UTG schema.

SELECT DBMS_METADATA.GET_XML('CONSTRAINT','UTG','R_PARAM_FUNCTPARAM') from dual
                                                                           *
Error at line 1
ORA-31603: object "UTG" of type CONSTRAINT not found in schema "R_PARAM_FUNCTPARAM"
ORA-06512: at "SYS.DBMS_METADATA", line 4018
ORA-06512: at "SYS.DBMS_METADATA", line 5761
ORA-06512: at line 1
Nakuru1234

ASKER
The same SQL you ran in http:#a38002926.

//Yes, but once I ran it, it shows only one Foreigh Key. That FK is actually on a different table. Therefore, I am DISABLING it including all the rest of FK's in the table but still it wont let me ALTER TABLE UTG.PARAMETER Drop CONSTRAINT XPKPARAMETER;
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
slightwv (䄆 Netminder)

What user were you logged in with when you ran the SQL in http:#a38002926 ?
slightwv (䄆 Netminder)

>>DISABLING it including all the rest of FK'

I have already corrected myself several times on this point:  Disabling will not work.  Stop trying it.
Nakuru1234

ASKER
What user were you logged in with when you ran the SQL in http:#a38002926 ?
//UTG. He has all the permissions to do anything.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
slightwv (䄆 Netminder)

Sorry.  I posted the incorrect DBMS_METADATA call and messed up the syntax.  I'll test before posting from now on.

Also it is REF_CONSTRAINT not CONSTRAINT

Try:

SELECT DBMS_METADATA.GET_DDL('REF_CONSTRAINT','R_PARAM_FUNCTPARAM') from dual;
Nakuru1234

ASKER
ALTER TABLE "UTG"."FUNCTION_PARAMETER" ADD CONSTRAINT "R_PARAM_FUNCTPARAM" FOR
EIGN KEY ("PARAM_ID")                                                          
      REFERENCES "UTG"."PARAMETER" ("PARAM_ID") ON DELETE CASCADE DISABLE


Why are we adding constraint "R_PARAM_FUNCTPARAM" FOREIGN KEY ("PARAM_ID") ?
I am confused because the original question was asking how to add a Primary Key on column PARAM_ID and CIR_TYPE.
slightwv (䄆 Netminder)

>>I am confused because the original question was asking how to add a Primary Key on column PARAM_ID and CIR_TYPE.

To do that you need to drop ALL foreign key constraints.

The foreign key in the FUNCTION_PARAMETER table is only against the PARAM_ID column.



Before you drop the foreign keys, you need to ability to recreate them.

The SQL above gives you the DDL to recreate them.

The steps are:
drop foreign key references to the PARAMETER table.
drop primary key on PARAMETER.
create new primary key on PARAMETER.
recreate all foreign key references that were in place BEFORE you dropped them.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Nakuru1234

ASKER
Hello,

The SQL above gives you the DDL to recreate them.
//What SQL above are you talking about?

create new primary key on PARAMETER
//Will I be able to create a new PK on both columns PARAM_ID and CIR-TYPE?

TIA,
N
slightwv (䄆 Netminder)

I don't know how much more I can explain this.  I have covered this just about every way I know how.

>>//What SQL above are you talking about?

The dbms_metadata.get_ddl SQL I posted in http:#a38004179 will give you the DDL for all the foreign keys you need to recreate after you drop them.

>>//Will I be able to create a new PK on both columns PARAM_ID and CIR-TYPE?

YES, YES and YES.  However, you need to drop the original one.  To do that, you need to drop all foreign keys that reference to original PK.
Nakuru1234

ASKER
SELECT DBMS_METADATA.GET_DDL('REF_CONSTRAINT','R_PARAM_FUNCTPARAM') from dual;

OUTPUT;

ALTER TABLE "UTG"."FUNCTION_PARAMETER" ADD CONSTRAINT "R_PARAM_FUNCTPARAM" FOR
EIGN KEY ("PARAM_ID")                                                          
        REFERENCES "UTG"."PARAMETER" ("PARAM_ID") ON DELETE CASCADE DISABLE          
                                                                               
1 row selected.


Then...

ALTER TABLE "UTG"."FUNCTION_PARAMETER" ADD CONSTRAINT "R_PARAM_FUNCTPARAM" FOR
EIGN KEY ("PARAM_ID")                                                          
      REFERENCES "UTG"."PARAMETER" ("PARAM_ID") ON DELETE CASCADE DISABLE
Error at line 1
ORA-00904: : invalid identifier

N.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Nakuru1234

ASKER
Now, after I received the error message ORA-00904: : invalid identifier
I went ahead and executed the following sql statements

ALTER TABLE UTG.FUNCTION_PARAMETER
 DROP CONSTRAINT R_PARAM_FUNCTPARAM;

ALTER TABLE UTG.PARAMETER
Drop CONSTRAINT XPKPARAMETER;

Table altered.

ALTER TABLE UTG.PARAMETER
 ADD CONSTRAINT PARAMETER_PK
 PRIMARY KEY
 (PARAM_ID, CIR_TYPE);

ALTER TABLE UTG.PARAMETER
MODIFY(CIR_TYPE  NOT NULL);

But when I go back to add the PK's on table UTG.PARAMETER, I am getting the error below. Any advice?

ALTER TABLE UTG.PARAMETER
 ADD CONSTRAINT PARAMETER_PK
 PRIMARY KEY
 (PARAM_ID, CIR_TYPE);

ORA-02296: cannot enable (UTG.) - null values found

N.
ASKER CERTIFIED SOLUTION
slightwv (䄆 Netminder)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question