Can I use an SQL query in a column/table constraint

[sin1965]

hi all
is it possible to use an SQL query in a column or table constraint. I haven't been able to find any info for this. It seems that only logical evaluation of simple expressions is allowed.
If that is so, then I assume that I will need to use a stored procedure acting on updates inserts and deletes.
any help will be most appreciated
steve

 

[Thargy]

sin,

no you can't, and you shouldn't attempt it.

See Tom Kyte's web site for details of function based indexes, which are fairly clever, and may do some of what you want.

Your surmise is correct, you can only use Oracle's built in functions such as upper, instr, substr, is not null etc. in constraints.

If you need to use stored procedures to handle your data integrity, this is usually a sign that the tables are inadequately normalised. If you boil the design down to lots of simple tables, then often lots of simple constraints will do what you need.

Obviously genuinely complex business logic can only be done by stored procedures, but this should not be mistaken for data integrity. The two overlap, but are not the same.

If you have specific requirements, post, and I'll be glad to help.

Regards

Tharg

Grinding away at things Oracular

 

[SantaMufasa]

I will need to use a stored procedure acting on updates inserts and deletes.

I presume (and hope) that by "stored procedure" you mean a database trigger, right?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[sin1965]

Hi
My initial enquiry was just to confirm that queries cannot be used in constraints. you have answered my ?
Thank you for you replies.
steve