Home » SQL & PL/SQL » SQL & PL/SQL » Alter table constraint help
Alter table constraint help [message #237179] Sun, 13 May 2007 00:01 Go to next message
Power Penguin
Messages: 33
Registered: April 2007
Location: Australia
Member
Hi Guys,

Im trying to alter a table I have called room to change the price from being allowed to be anything to having to be equal to or between $20 and $250

I know how to alter a table in general but not how to put in this type of constraint. Any help would be greatly appreciated.

The table is:


CREATE TABLE Room (hno char(4), rno char(4), type varchar(6) not null, price decimal (7,2), 
CONSTRAINT PK_ROOM PRIMARY KEY (hno, rno)); 

[Updated on: Sun, 13 May 2007 00:02]

Report message to a moderator

Re: Alter table constraint help [message #237181 is a reply to message #237179] Sun, 13 May 2007 00:56 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Either:

CREATE TABLE Room (hno CHAR(4), rno CHAR(4), TYPE VARCHAR(6) NOT NULL, price DECIMAL (7,2), 
CONSTRAINT PK_ROOM PRIMARY KEY (hno, rno),
CONSTRAINT PRICE_CHECK CHECK ( PRICE BETWEEN 20 AND 250 ));


or

ALTER TABLE ROOM ADD CONSTRAINT PRICE_CHECK CHECK
  ( PRICE BETWEEN 20 AND 250 );


HTH.
Michael
Re: Alter table constraint help [message #237183 is a reply to message #237181] Sun, 13 May 2007 01:03 Go to previous messageGo to next message
Power Penguin
Messages: 33
Registered: April 2007
Location: Australia
Member
Thanks heaps Michael. I didnt realize you could do a constraint check like that in alter. (im still learning) your help is very much appreciated Very Happy
Re: Alter table constraint help [message #327312 is a reply to message #237183] Mon, 16 June 2008 00:43 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi Michel,
I am stuck in a similar situation. A table is created with two columns on primary key. The table is having data in it.
Now I have to alter the Constraint and add one more column to the primary key constraint.
That is the constraint should now have three columns in place of two created before.

CREATE TABLE Room (hno char(4), rno char(4), type varchar(6) not null, price decimal (7,2), 
constraint PK_ROOM PRIMARY KEY (hno, rno)); 

Please suggest the steps to let the data stay in the table which are existing from before.

I tried altering but getting integrity constraint error. Do I have to drop the constraint and create a new one like below:

SQL> alter table Room drop constraint PK_ROOM;

Table altered.

SQL> alter table Room add constraint PK_ROOM PRIMARY KEY (hno, rno, type);


Please advice a way as I am afraid of dropping the constraint as the table is a part of a live project.

Thanks in advance,
Mona

[Updated on: Mon, 16 June 2008 00:44]

Report message to a moderator

Re: Alter table constraint help [message #327324 is a reply to message #327312] Mon, 16 June 2008 01:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Dropping and recreating the constraint is the only way.

Regards
Michel
Re: Alter table constraint help [message #327455 is a reply to message #237179] Mon, 16 June 2008 08:12 Go to previous messageGo to next message
jaganerp@gmail.com
Messages: 63
Registered: April 2008
Member
no need to drop that constraint first u delet taht value of foreign key values and primary key values after taht same values u will insert ( before deleting that values u will take back up of that values)
Re: Alter table constraint help [message #327464 is a reply to message #327455] Mon, 16 June 2008 08:35 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read and follow OraFAQ Forum Guide.

How do you add a column to a primary key?
Post the syntax.

Regards
Michel
Previous Topic: Data from Excel sheet to oracle table
Next Topic: Multi Table Update / Join syntax not working
Goto Forum:
  


Current Time: Sat Dec 10 10:43:05 CST 2016

Total time taken to generate the page: 0.27196 seconds