Home » SQL & PL/SQL » SQL & PL/SQL » USE IS NULL AND NOT NULL IN A CHECK CONSTRAINT (ORACLE 11G SQL PLUS)
USE IS NULL AND NOT NULL IN A CHECK CONSTRAINT [message #621239] Mon, 11 August 2014 21:32 Go to next message
dariusd7
Messages: 24
Registered: August 2014
Junior Member
I HAVE TO WRITE A QUERY THAT CHECKS TO MAKE SURE THAT IF THE COUNTRY IS USA THEN THE US_TAX_ID MUST BE NOT NULL, AND IF IT IS NOT USA THEN THE US_TAX_ID MUST BE IS NULL.

Here is my final effort. any suggestions? Thank you.


ALTER TABLE CLASSMATE.CH09WORLD
ADD CHECK (COUNTRY = 'USA' AND US_TAX_ID = NOT NULL OR 
COUNTRY != 'USA' AND US_TAX_ID = IS NULL );



Error starting at line 1 in command:
ALTER TABLE CLASSMATE.CH09WORLD
ADD CHECK (COUNTRY = 'USA' AND US_TAX_ID = NOT NULL OR COUNTRY != 'USA' AND US_TAX_ID = IS NULL )
Error report:
SQL Error: ORA-00936: missing expression
00936. 00000 - "missing expression"
*Cause:
*Action:
Re: USE IS NULL AND NOT NULL IN A CHECK CONSTRAINT [message #621241 is a reply to message #621239] Mon, 11 August 2014 21:44 Go to previous messageGo to next message
BlackSwan
Messages: 22792
Registered: January 2009
Senior Member
Read The Fine Manual

http://docs.oracle.com/database/121/SQLRF/statements_3001.htm#SQLRF53396
Re: USE IS NULL AND NOT NULL IN A CHECK CONSTRAINT [message #621243 is a reply to message #621239] Mon, 11 August 2014 22:43 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2346
Registered: May 2013
Location: World Wide on the Web
Senior Member
dariusd7 wrote on Tue, 12 August 2014 08:02
I HAVE TO WRITE A QUERY THAT CHECKS TO MAKE SURE THAT IF THE COUNTRY IS USA THEN THE US_TAX_ID MUST BE NOT NULL, AND IF IT IS NOT USA THEN THE US_TAX_ID MUST BE IS NULL.

ALTER TABLE CLASSMATE.CH09WORLD
ADD CHECK (COUNTRY = 'USA' AND US_TAX_ID = NOT NULL OR 
COUNTRY != 'USA' AND US_TAX_ID = IS NULL );



1. Do not write your post in upper case, it is considered as shouting in forums.
2. Where is the constraint name in your alter statement?

Example,

ALTER TABLE table_name
ADD CONSTRAINT constraint_name CHECK (column_name condition)
Re: USE IS NULL AND NOT NULL IN A CHECK CONSTRAINT [message #621244 is a reply to message #621239] Tue, 12 August 2014 00:19 Go to previous messageGo to next message
Michel Cadot
Messages: 59147
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

In addition, syntax is not: "US_TAX_ID = NOT NULL" or "US_TAX_ID = IS NULL" but
US_TAX_ID IS NOT NULL
US_TAX_ID IS NULL

Re: USE IS NULL AND NOT NULL IN A CHECK CONSTRAINT [message #621290 is a reply to message #621244] Tue, 12 August 2014 07:41 Go to previous messageGo to next message
dariusd7
Messages: 24
Registered: August 2014
Junior Member
Thanks to everyone for your help. I let you know how it goes with all your advice. Great forum..
Re: USE IS NULL AND NOT NULL IN A CHECK CONSTRAINT [message #621339 is a reply to message #621290] Tue, 12 August 2014 11:58 Go to previous messageGo to next message
dariusd7
Messages: 24
Registered: August 2014
Junior Member
Well fellas I have to move on. I tried for more than 2.5 hour to figure this one out, but I just can't get it. I tried this query
ALTER TABLE CLASSMATE.CH09WORLD
ADD CONSTRAINT US_TAX_ID
CHECK (COUNTRY = 'USA' AND US_TAX_ID IS NOT NULL OR COUNTRY != 'USA' AND US_TAX_ID IS NULL);


but got this error
Error starting at line 1 in command:
ALTER TABLE CLASSMATE.CH09WORLD
ADD CONSTRAINT US_TAX_ID
CHECK (COUNTRY = 'USA' AND US_TAX_ID IS NOT NULL OR COUNTRY != 'USA' AND US_TAX_ID IS NULL)
Error report:
SQL Error: ORA-02293: cannot validate (CLASSMATE.US_TAX_ID) - check constraint violated
02293. 00000 - "cannot validate (%s.%s) - check constraint violated"
*Cause: an alter table operation tried to validate a check constraint to
populated table that had nocomplying values.
*Action: Obvious

I then tried to see what the constraints were ton the table.. so I put in this query
SELECT CONSTRAINT_NAME,
      CONSTRAINT_TYPE
FROM ALL_CONSTRAINTS
WHERE TABLE_NAME = 'CH09WORLD'
    AND OWNER = 'CLASSMATE';


gos this result
CONSTRAINT_NAME CONSTRAINT_TYPE
------------------------------ ---------------
CHK_NAMES C
US_TAX_UNIQUE U
CH09WORLD_PK P
I then researched what each constraint type means.. I figure maybe the check constraint on CHK_Names is the problem, but I couldn't figure out how to view the actual constraint on the column. I also disabled the Unique constraint on US_TAX_Unique and then ran the query, but I still got the same error.. Now I am completely stumped at this point.. Sorry I failed guys.. but I'm going to have to move on to the next question...I would appreciate any help, but I can't spend to much more time guessing with this.. I have 9 more question I have to answer..
Re: USE IS NULL AND NOT NULL IN A CHECK CONSTRAINT [message #621344 is a reply to message #621339] Tue, 12 August 2014 12:06 Go to previous messageGo to next message
Michel Cadot
Messages: 59147
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

(COUNTRY = 'USA' AND US_TAX_ID IS NOT NULL OR COUNTRY != 'USA' AND US_TAX_ID IS NULL);


Parentheses are missing.

Re: USE IS NULL AND NOT NULL IN A CHECK CONSTRAINT [message #621346 is a reply to message #621344] Tue, 12 August 2014 12:34 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2346
Registered: May 2013
Location: World Wide on the Web
Senior Member
@OP, To add further, what about the constraint_name?
Re: USE IS NULL AND NOT NULL IN A CHECK CONSTRAINT [message #621362 is a reply to message #621346] Tue, 12 August 2014 15:50 Go to previous messageGo to next message
dariusd7
Messages: 24
Registered: August 2014
Junior Member
Ok.. I think what I neede to use is the Nocheck.. so that it would not look at data that already exist within the field, but would apply it to new existing data added....
Re: USE IS NULL AND NOT NULL IN A CHECK CONSTRAINT [message #621398 is a reply to message #621362] Wed, 13 August 2014 01:24 Go to previous messageGo to next message
Michel Cadot
Messages: 59147
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You mean NOVALIDATE.

Re: USE IS NULL AND NOT NULL IN A CHECK CONSTRAINT [message #621481 is a reply to message #621398] Wed, 13 August 2014 12:44 Go to previous message
Bill B
Messages: 1099
Registered: December 2004
Senior Member
should't the check constraint be the following with novalidate?


((COUNTRY = 'USA' AND US_TAX_ID IS NOT NULL) OR (COUNTRY != 'USA' AND US_TAX_ID IS NULL));
Previous Topic: SQL
Next Topic: Schedule a Procedure with Input Parameters
Goto Forum:
  


Current Time: Sat Sep 20 15:49:37 CDT 2014

Total time taken to generate the page: 0.08209 seconds