Home » SQL & PL/SQL » SQL & PL/SQL » Command to add constraints
Command to add constraints [message #440346] Fri, 22 January 2010 14:05 Go to next message
dbmeany
Messages: 11
Registered: January 2010
Junior Member
Hi,

I've consulted the forum, documentation, the internet and text books but cannot find the correct SQL command to add constraints to an existing table

Obviously I would

ALTER TABLE Turkey ADD CONSTRAINT Whitemeat

But what comes next is puzzling, the values of column Whitemeat can be any value other than Dinner, Gravy, Pumpkin, or Sauce

Any assistance would be great

Thank You
Re: Command to add constraints [message #440347 is a reply to message #440346] Fri, 22 January 2010 14:17 Go to previous messageGo to next message
BlackSwan
Messages: 24906
Registered: January 2009
Senior Member
  1* create table turkey (whitemeat varchar2(511) constraint foobar check (whitemeat not in ('DINNER','GRAVY','PUMPKIN','SAUCE')))
SQL> /

Table created
Re: Command to add constraints [message #440348 is a reply to message #440347] Fri, 22 January 2010 14:24 Go to previous messageGo to next message
dbmeany
Messages: 11
Registered: January 2010
Junior Member
Thanks, I know I'm trying to use a check constraint, but as I said the table is already created. I am adding constraints
Re: Command to add constraints [message #440349 is a reply to message #440348] Fri, 22 January 2010 14:27 Go to previous messageGo to next message
BlackSwan
Messages: 24906
Registered: January 2009
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

syntax for ALTER is same/similar for CREATE
Re: Command to add constraints [message #440350 is a reply to message #440346] Fri, 22 January 2010 14:47 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I've consulted the forum, documentation

Did you consult Database SQL Reference?

Regards
Michel
Re: Command to add constraints [message #440352 is a reply to message #440350] Fri, 22 January 2010 15:01 Go to previous messageGo to next message
dbmeany
Messages: 11
Registered: January 2010
Junior Member
Thank for all the feedback

ALTER TABLE TURKEY
(CONSTRAINT TURKEY_WHITEMEAT_CK CHECK DEPT_ID NOT IN ('Dinner', 'Gravy', 'Pumpkin', 'Sauce'));

According to the docs this should be right but its not working Confused
Re: Command to add constraints [message #440354 is a reply to message #440352] Fri, 22 January 2010 15:09 Go to previous messageGo to next message
BlackSwan
Messages: 24906
Registered: January 2009
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/


use CUT & PASTE so we can see what you do & how Oracle responds

>ALTER TABLE Turkey ADD CONSTRAINT Whitemeat

[Updated on: Fri, 22 January 2010 15:10]

Report message to a moderator

Re: Command to add constraints [message #440355 is a reply to message #440354] Fri, 22 January 2010 15:22 Go to previous messageGo to next message
dbmeany
Messages: 11
Registered: January 2010
Junior Member
Sorry,

This is what I'm trying to run

ALTER TABLE TURKEY add
CONSTRAINT TURKEY_WHITEMEAT_CK CHECK WHITEMEAT NOT IN ('Dinner', 'Gravy', 'Pumpkin', 'Sauce');


And this is the error message I am getting

Error report:
SQL Error: ORA-00906: missing left parenthesis
00906. 00000 -  "missing left parenthesis"
*Cause:    
*Action:


Thank you for your assistance

[Updated on: Fri, 22 January 2010 15:23]

Report message to a moderator

Re: Command to add constraints [message #440356 is a reply to message #440354] Fri, 22 January 2010 15:25 Go to previous messageGo to next message
decci_7
Messages: 68
Registered: March 2006
Member
BlackSwan wrote on Sat, 23 January 2010 02:39
use CUT & PASTE

Shocked ... why use cut paste when you can help the guy w/o it.

Try this ...
ALTER TABLE TURKEY
add CONSTRAINT TURKEY_WHITEMEAT_CK CHECK (DEPT_ID NOT IN ('Dinner', 'Gravy', 'Pumpkin', 'Sauce'));

Quote:
According to the docs this should be right but its not working

@dbmeany - Read the docs (whatever) carefully!! You need to put in some efforts in finding solutions yourself before posting it here!

Re: Command to add constraints [message #440358 is a reply to message #440356] Fri, 22 January 2010 15:31 Go to previous messageGo to next message
dbmeany
Messages: 11
Registered: January 2010
Junior Member
Thank You that worked. I understand it my seem I have put little effort not before coming here but that is only because I am still learning Oracle. I have actually got a lot done today on my own.

But were specifically would you find such a functional example of Check? All I find are variations that don't work.

Thanks
Re: Command to add constraints [message #440361 is a reply to message #440346] Fri, 22 January 2010 15:40 Go to previous messageGo to next message
decci_7
Messages: 68
Registered: March 2006
Member
If you are new to Oracle and trying to learn, there is no better place than GOOGLE.
Re: Command to add constraints [message #440362 is a reply to message #440358] Fri, 22 January 2010 15:46 Go to previous message
BlackSwan
Messages: 24906
Registered: January 2009
Senior Member
>But were specifically would you find such a functional example of Check?

Clearly documented in the Posting Guidelines which you have avoided reading apparently.
Previous Topic: Slow Query
Next Topic: Query Re-structure
Goto Forum:
  


Current Time: Tue Sep 27 07:42:35 CDT 2016

Total time taken to generate the page: 0.62029 seconds