Link to home
Create AccountLog in
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
Avatar of flow01
flow01
Flag of Netherlands image

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
Avatar of slightwv (䄆 Netminder)
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).
Avatar of 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.
>>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));
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.
Did you drop the existing one first?
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));
>>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.
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.
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.
>>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.
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.
>>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';
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.
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
>>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.
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...
>>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.
I just need those 2 columns to become a compound PK. Is that possible? If yes, show me the sql syntax. Thanks.

N.
>>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.
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?
>>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.
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
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;
What user were you logged in with when you ran the SQL in http:#a38002926 ?
>>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.
What user were you logged in with when you ran the SQL in http:#a38002926 ?
//UTG. He has all the permissions to do anything.
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;
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.
>>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.
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
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.
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.
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
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account