Oracle change Primary Key Constraint

Nakuru1234
Nakuru1234 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
flow01IT-specialist

Commented:
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
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
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).

Author

Commented:
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.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>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));

Author

Commented:
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.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Did you drop the existing one first?

Author

Commented:
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));
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>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.

Author

Commented:
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.

Author

Commented:
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.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>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.

Author

Commented:
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.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>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';

Author

Commented:
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.

Author

Commented:
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
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>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.

Author

Commented:
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...
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>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.

Author

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

N.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>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.

Author

Commented:
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?
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>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.

Author

Commented:
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

Author

Commented:
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;
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
What user were you logged in with when you ran the SQL in http:#a38002926 ?
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>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.

Author

Commented:
What user were you logged in with when you ran the SQL in http:#a38002926 ?
//UTG. He has all the permissions to do anything.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
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;

Author

Commented:
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.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>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.

Author

Commented:
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
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
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.

Author

Commented:
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.

Author

Commented:
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.
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
>>ORA-02296: cannot enable (UTG.) - null values found

Error is self-explanitory.  You cannot have null values in a Primary Key.

Take a look at the following simple test

SQL> create table tab1(col1 char(1), col2 char(1));

Table created.

SQL> 
SQL> insert into tab1 values('a',null);

1 row created.

SQL> 
SQL> alter table tab1 add constraint tab1_pk primary key (col1,col2);
alter table tab1 add constraint tab1_pk primary key (col1,col2)
                                                          *
ERROR at line 1:
ORA-01449: column contains NULL values; cannot alter to NOT NULL


SQL> 
SQL> update tab1 set col2 = 'a';

1 row updated.

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

Table altered.

SQL> 

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial