Home » SQL & PL/SQL » SQL & PL/SQL » change my constraint
change my constraint [message #194634] Sat, 23 September 2006 04:07 Go to next message
emadbsb
Messages: 334
Registered: May 2005
Location: egypt
Senior Member

hii all

i have create a table of "gbrchxxxx"

CREATE TABLE GBRCHXXXX(
   CODE                 NUMBER(5),
   DESCA                VARCHAR2(50),
   LASTUPDATE           DATE,
   GUS_CODE             VARCHAR2(10),
   GCO_CODE             VARCHAR2(10),
   GBR_CODE             VARCHAR2(10)             
);



with that constraint
ALTER TABLE GBRCHXXXX ADD CONSTRAINT GBRCH_PK PRIMARY KEY (CODE);


i want to change that primary key to contain also
"GCO_CODE, GBR_CODE"

i want my constraint to be like that

ALTER TABLE GBRCHXXXX ADD CONSTRAINT GBRCH_PK PRIMARY KEY (CODE,GCO_CODE, GBR_CODE);


N.B SO IMPORTANT
THAT TABLE ITS PRIMARY KEY IS REFERENCED BY OTHER TABLES
(HAVE DEPENDENCIES ON IT)




thanks for everyone helped and helping me

[Updated on: Sat, 23 September 2006 04:08]

Report message to a moderator

Re: change my constraint [message #194652 is a reply to message #194634] Sat, 23 September 2006 09:38 Go to previous messageGo to next message
amul
Messages: 252
Registered: April 2001
Location: Chennai
Senior Member
GCO_CODE, GBR_CODE are the columns from the same table?
Re: change my constraint [message #194668 is a reply to message #194652] Sat, 23 September 2006 15:58 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If there weren't another tables which reference this one, it would be quite simple: drop existing primary key constraint and create a new one, with two columns.

But, as this is not the case, you'll have to drop foreign key constraints in order to be able to drop a primary key constraint on this table and create a new one.

However, note that all tables that used to reference this table MUST have the same column combination, because - if they don't have it - you won't be able to create new foreign key constraints.
Re: change my constraint [message #194693 is a reply to message #194634] Sun, 24 September 2006 04:35 Go to previous messageGo to next message
emadbsb
Messages: 334
Registered: May 2005
Location: egypt
Senior Member

i have many table of this case

is there anyway to facilitate this operation
Re: change my constraint [message #194710 is a reply to message #194693] Sun, 24 September 2006 14:33 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You'll have to decide whether it is better to do it table after table, or will you put some effort to write a code which will do it instead of you (by querying Oracle metadata).
Re: change my constraint [message #194781 is a reply to message #194634] Mon, 25 September 2006 05:10 Go to previous messageGo to next message
vijayanarayanan
Messages: 11
Registered: September 2006
Location: chennai
Junior Member
CREATE TABLE GBRCHXXXX(
CODE NUMBER(5),
DESCA VARCHAR2(50),
LASTUPDATE DATE,
GUS_CODE VARCHAR2(10),
GCO_CODE VARCHAR2(10),
GBR_CODE VARCHAR2(10)
);

ALTER TABLE GBRCHXXXX ADD CONSTRAINT GBRCH_PK PRIMARY KEY (CODE);

ALTER TABLE GBRCHXXXX ADD CONSTRAINT GBRCH_PK PRIMARY KEY (CODE,GCO_CODE, GBR_CODE);


IF YOU WANT TO MAKE THIS PRIMARY KEY AS COMPOSITE KEY NO OTHER GO YOU HAVE TO DROP IT AND RECREATE IT.BECAUSE A COMPOSITE FOREIGN KEY CAN ONLY REFER COMPOSITE PRIMARY KEY.ALL THE FOREIGN KEY REFERENCING MUST ALSO BE DROPPED.
Re: change my constraint [message #194810 is a reply to message #194781] Mon, 25 September 2006 07:48 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
No need to shout. And especially no need to shout in the blue!
Re: change my constraint [message #194836 is a reply to message #194781] Mon, 25 September 2006 11:47 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
vijayanarayanan wrote on Mon, 25 September 2006 06:10

ALTER TABLE GBRCHXXXX ADD CONSTRAINT GBRCH_PK PRIMARY KEY (CODE);

ALTER TABLE GBRCHXXXX ADD CONSTRAINT GBRCH_PK PRIMARY KEY (CODE,GCO_CODE, GBR_CODE);


IF YOU WANT TO MAKE THIS PRIMARY KEY AS COMPOSITE KEY NO OTHER GO YOU HAVE TO DROP IT AND RECREATE IT.BECAUSE A COMPOSITE FOREIGN KEY CAN ONLY REFER COMPOSITE PRIMARY KEY.ALL THE FOREIGN KEY REFERENCING MUST ALSO BE DROPPED.


I'm not sure what you're trying to say here, but you cannot have two primary keys on a table.
Previous Topic: Another query tuning problem
Next Topic: JOIN Style Option
Goto Forum:
  


Current Time: Sun Dec 04 12:35:17 CST 2016

Total time taken to generate the page: 0.08001 seconds