Solved

How can I perform a select in an check constraint?

Posted on 2004-03-24
4
2,379 Views
Last Modified: 2013-12-11
Hi,

I'd like to do the following:
ALTER TABLE local_sale_terms
  ADD CONSTRAINT constr_check_cancelled
CHECK (NOT EXISTS (SELECT 1
                     FROM local_sale_terms
                    WHERE cancelled='N'
                    GROUP BY parent_no,date_of_term,cancelled
                   HAVING COUNT(*)>1))

But Oracle complains about a query in the constraint.

How can i enforce having only ONE Uncancelled row?
I can have many cancelled rows, but only one uncancelled row.

a unique index on (parent_no,date_of_term,cancelled) is therefore inappropriate.

help
thanks
adam
0
Comment
Question by:schwartz8a
  • 2
4 Comments
 
LVL 8

Accepted Solution

by:
william_jwd earned 65 total points
ID: 10666753
I dont think that you can do it in check constraint.  You can do this using triggers.  You should do the checking in two triggers, before insert and before update.
0
 

Author Comment

by:schwartz8a
ID: 10666829
thanks.
I know i can use triggers.

But Joe Celko's book, SQL for Smarties, says that queries within check constraints are preferable to triggers. He even gives an example similar to the one i posted.

any other ideas?
0
 
LVL 6

Assisted Solution

by:musdu
musdu earned 60 total points
ID: 10667143
Hi,

according to Oracle documentation you can not use such a statement in check constraint. (Second item about your problem)
Use triggers.

 Restrictions on CHECK Constraints

A CHECK integrity constraint requires that a condition be true or unknown for every row of the table. If a statement causes the condition to evaluate to false, then the statement is rolled back. The condition of a CHECK constraint has the following limitations:

    * The condition must be a Boolean expression that can be evaluated using the values in the row being inserted or updated.

    * The condition cannot contain subqueries or sequences.

    * The condition cannot include the SYSDATE, UID, USER, or USERENV SQL functions.

    * The condition cannot contain the pseudocolumns LEVEL, PRIOR, or ROWNUM;
0
 
LVL 8

Expert Comment

by:william_jwd
ID: 10668017
schwartz8a,

What you say is right...  As per the SQL standards SQL queries are allowed in check constraint, but I dont think that it is possible in oracle.

William.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
ER Diagram 3 42
update statement in oracle 9 40
date show only hh:mm 2 40
Migration from sql server to oracle 5 38
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question