<div>
thanks.<br />
I know i can use triggers.<br />
<br />
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.<br />
<br />
any other ideas?<br />

</div>


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?


<div>
schwartz8a,<br />
<br />
What you say is right... &nbsp;As per the SQL standards SQL queries are allowed in check constraint, but I dont think that it is possible in oracle.<br />
<br />
William.
</div>


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.

<div>
<div class="content wysiwyg-content">
Hi,<br />
<br />
I'd like to do the following:<br />
ALTER TABLE local_sale_terms<br />
&nbsp; ADD CONSTRAINT constr_check_cancelled<br />
CHECK (NOT EXISTS (SELECT 1 <br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;FROM local_sale_terms <br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WHERE cancelled='N' <br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; GROUP BY parent_no,date_of_term,can<wbr />celled<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;HAVING COUNT(*)&gt;1))<br />
<br />
But Oracle complains about a query in the constraint.<br />
<br />
How can i enforce having only ONE Uncancelled row?<br />
I can have many cancelled rows, but only one uncancelled row.<br />
<br />
a unique index on (parent_no,date_of_term,ca<wbr />ncelled) is therefore inappropriate.<br />
<br />
help <br />
thanks<br />
adam
</div>
</div>


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

How can I perform a select in an check constraint?

Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.