Home » SQL & PL/SQL » SQL & PL/SQL » check_constraint (10gR2,Unix-HP)
check_constraint [message #446195] Sat, 06 March 2010 03:00 Go to next message
Sami_Younis
Messages: 49
Registered: February 2008
Location: Egypt
Member

I wanna to make a check constraint to verify
that managers salary is bigger than any employee's salary
but I found that check constraint deals with a value
like > value or = value

plz, Help me
Re: check_constraint [message #446199 is a reply to message #446195] Sat, 06 March 2010 03:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL Reference
Chapter 8 Common SQL DDL Clauses
Section constraint

Try this and come back with your tries if can't do it.

Regards
Michel
Re: check_constraint [message #446204 is a reply to message #446195] Sat, 06 March 2010 04:07 Go to previous messageGo to next message
Sami_Younis
Messages: 49
Registered: February 2008
Location: Egypt
Member

Thanks Michel, I read the topic from the refrence
I just tried that
ALTER TABLE sami_emp
ADD CONSTRAINT ck_fk CHECK(
select max(salary) from sami_emp where manager_id is not null < (select min(salary) from sami_emp
where manager_id is null)

but give me
"invalid SQL command" missing expression

I just want using check constraint to verify that no employee's salary can be more than any manager

Thank u in advance,
Sami

Re: check_constraint [message #446213 is a reply to message #446204] Sat, 06 March 2010 05:03 Go to previous messageGo to next message
John Watson
Messages: 6580
Registered: January 2010
Location: Global Village
Senior Member
Sami, are you sure you read the reference Michel gave you correctly? Including the paragraph headed "Restrictions on Check Constraints"? You might need to approach this another way, perhaps using a trigger.
Re: check_constraint [message #446217 is a reply to message #446213] Sat, 06 March 2010 05:29 Go to previous messageGo to next message
Sami_Younis
Messages: 49
Registered: February 2008
Location: Egypt
Member

Thanks John

Yes there is a restiriction on
"Calls to user-defined functions" but not "max" and "min"
function and queries as well

u have a point, I can do that using trigger but the task force me to use check constaint

Sami
Re: check_constraint [message #446218 is a reply to message #446217] Sat, 06 March 2010 06:13 Go to previous messageGo to next message
Sami_Younis
Messages: 49
Registered: February 2008
Location: Egypt
Member


&&& Here is the Original task as I received:

I would like to ask you to help me to solve this question about the integrity constraints

Write integrity constraints (domain, key, foreign key, or CHECK constraints to ensure each of the following requirements, considered independently.

1. Employees must earn a minimum salary of $1,000.
2. Every manager must also be an employee
3. A manager must have higher salary than any of his/her employee that s/he manages
4. A manager's salary must increased to match an employee's salary increase.

waiting for your help about 3 and 4
thank u in advance,
Sami
Re: check_constraint [message #446225 is a reply to message #446217] Sat, 06 March 2010 09:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sami_Younis wrote on Sat, 06 March 2010 12:29
Thanks John

Yes there is a restiriction on
"Calls to user-defined functions" but not "max" and "min"
function and queries as well

u have a point, I can do that using trigger but the task force me to use check constaint

Sami

The syntax diagram says:
Quote:
CHECK (condition)

Then
Quote:
When Oracle evaluates a check constraint condition for a particular row, any column names in the condition refer to the column values in that row.

This excludes any SELECT.

Regards
Michel

[Updated on: Sat, 06 March 2010 09:22]

Report message to a moderator

Re: check_constraint [message #446226 is a reply to message #446218] Sat, 06 March 2010 09:27 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sami_Younis wrote on Sat, 06 March 2010 13:13

&&& Here is the Original task as I received:

I would like to ask you to help me to solve this question about the integrity constraints

Write integrity constraints (domain, key, foreign key, or CHECK constraints to ensure each of the following requirements, considered independently.

1. Employees must earn a minimum salary of $1,000.
2. Every manager must also be an employee
3. A manager must have higher salary than any of his/her employee that s/he manages
4. A manager's salary must increased to match an employee's salary increase.

waiting for your help about 3 and 4
thank u in advance,
Sami

1. CHECK constraint
2. Referential constraint (foreign key, this mean you have a primary or unique key)
3. Impossible with declarative constraint (unless you have somewhere this max value in the row, but we have not your model)
4. A constraint does not increase or decrease something, impossible.

Regards
Michel

Previous Topic: Exception in proceedure
Next Topic: Fetch data based max data and rowcount
Goto Forum:
  


Current Time: Thu Dec 08 22:10:50 CST 2016

Total time taken to generate the page: 0.12317 seconds