Can we use any other operator than relational operators for 'CONSTRAINT', because I am having an error

Syed Arbab Ahmed

Can we use logical operators, Conditional operators other than relational operator in query of Constraints?, because I am having an error

Below is the query I tried
Alter table arbab ADD CONSTRAINT abc CHECK (gender 'Male' or 'female');

I am having below mentioned error:

Error starting at line 1 in command:
Alter table arbab ADD CONSTRAINT abc CHECK (gender 'Male' or 'female')
Error report:
SQL Error: ORA-00920: invalid relational operator
00920. 00000 - "invalid relational operator"
*Cause:
*Action:

This query:

Alter table arbab ADD CONSTRAINT abc CHECK (gender >10);

Worked fine.

Thanks in advance.

8 Replies

Know the answer? Post it — somebody with the same question will find it here.

Mamta M15y ago
Accepted answer
The purpose of constraints is to maintain data integrity. So whenever you create constraints such as CHECK constraints or even FOREIGN KEY constraints, the RDBMS will confirm whether existing data satisfies those constraints and then only allow you to create the constraint. If the existing data violates the constraint, the constraint will not be created at all.
Just think if you had a column Price with existing data such as 40,50 and 60 and you create a constraint that Price must always be > 100, then what's the point of having the earlier values which are < 100. So the constraint creation will fail in this case unless you delete those values.
0
Syed Arbab Ahmed15y ago
@Mamta M: Thanks u got it ABSOLUTELY RIGHT, that was the problem, but does it mean, that after creating table with constrains, inserting values by force in them and then by imposing constrain again it check 1st the already existing values that it matches the constrain that a coder is creating and then insert the values, is this natural & always happens?
0
Mamta M15y ago
The constraint is perfectly valid and even working. This error itself is proof that the constraint is working, because it says "populated table that had nocomplying values"...which means that the existing data in the table is going against the constraint, ie, your existing gender values in the table are not 'Male' or 'Female'.

So what you need to do now is first empty that column, remove the data that does not satisfy the constraint, then create the constraint. Then for any new rows that you try to add, the CHECK constraint will verify if gende value satisfies the condition and then only allow insertion.
0
Syed Arbab Ahmed15y ago
I tried your solution of the query but had the following error:

Error starting at line 1 in command:
Alter table arbab ADD CONSTRAINT abcd CHECK (gender in ('Male', 'female'))
Error report:
SQL Error: ORA-02293: cannot validate (IRIS.ABCD) - 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

there must be a problem related to check constraint.
0
Vulpes15y ago
Well, you said in your original post that this query worked fine:

Alter table arbab ADD CONSTRAINT abc CHECK (gender >10);

So are you sure that gender is a string rather than a number?

0
Syed Arbab Ahmed15y ago
I tried your query it gives the following error: Error starting at line 1 in command: Alter table arbab ADD CONSTRAINT abcd CHECK (gender='Male' or gender='female') Error report: SQL Error: ORA-02293: cannot validate (IRIS.ABCD) - 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 *There is problem in solving the constraint name 'Check'.
0
Vulpes15y ago
The following should also work:

Alter table arbab ADD CONSTRAINT abc CHECK (gender in ('Male', 'female'))
0
Mamta M15y ago
Your code should be:

Alter table arbab ADD CONSTRAINT abc CHECK (gender='Male' or gender='female')

You have to mention gender twice to check for each value and the = operator was missing.
0