OraFAQ Forum: SQL & PL/SQL » USE IS NULL AND NOT NULL IN A CHECK CONSTRAINT

Home » SQL & PL/SQL » SQL & PL/SQL » USE IS NULL AND NOT NULL IN A CHECK CONSTRAINT (ORACLE 11G SQL PLUS)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

USE IS NULL AND NOT NULL IN A CHECK CONSTRAINT [message #621239]

Mon, 11 August 2014 21:32

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:

Report message to a moderator

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

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Read The Fine Manual

http://docs.oracle.com/database/121/SQLRF/statements_3001.htm#SQLRF53396

Report message to a moderator

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

Lalit Kumar B
Messages: 3174
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)

Report message to a moderator

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

Michel Cadot
Messages: 68645
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

Report message to a moderator

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

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..

Report message to a moderator

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

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..

Report message to a moderator

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

Michel Cadot
Messages: 68645
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.

Report message to a moderator

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

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

@OP, To add further, what about the constraint_name?

Report message to a moderator

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

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....

Report message to a moderator

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

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You mean NOVALIDATE.

Report message to a moderator

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

Bill B
Messages: 1971
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));

Report message to a moderator

Previous Topic:	SQL
Next Topic:	Schedule a Procedure with Input Parameters

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 18:46:16 CDT 2024