OraFAQ Forum: SQL & PL/SQL » check

Home » SQL & PL/SQL » SQL & PL/SQL » check_constraint (10gR2,Unix-HP)

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

check_constraint [message #446195]

Sat, 06 March 2010 03:00

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

Report message to a moderator

Re: check_constraint [message #446199 is a reply to message #446195]

Sat, 06 March 2010 03:08

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: check_constraint [message #446204 is a reply to message #446195]

Sat, 06 March 2010 04:07

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

Report message to a moderator

Re: check_constraint [message #446213 is a reply to message #446204]

Sat, 06 March 2010 05:03

John Watson
Messages: 8964
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.

Report message to a moderator

Re: check_constraint [message #446217 is a reply to message #446213]

Sat, 06 March 2010 05:29

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

Report message to a moderator

Re: check_constraint [message #446218 is a reply to message #446217]

Sat, 06 March 2010 06:13

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

Report message to a moderator

Re: check_constraint [message #446225 is a reply to message #446217]

Sat, 06 March 2010 09:22

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

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

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Feb 11 06:26:28 CST 2025