Nested queries in check SQL statement

Question

I am trying to add a constraint (in Oracle), that restricts the user from creating an entries having 'smth', 'smth else' or 'one more thing' in a field already exist. Basically, that's how I would write it:

ALTER TABLE sometable
ADD CONSTRAINT sometable_text_chk
CHECK (
    caption IN ( 
        SELECT caption
        FROM sometable
        WHERE text NOT IN ('smth', 'smth else', 'one more thing')
    )
)

Unfortunately, it seems to be not allowed to nest queries for this occasion. Is there perhaps a workaround.

This is not so clear - what is the relation between fields text and caption? what do you mean when you say 'restricts the user from creating an entries ... in a field already exist'? (are you talking about wanting to have unique values in field caption???) — Unreason
– Unreason, Commented Dec 8, 2010 at 15:47

Unreason · Accepted Answer · 2010-12-08 15:49:25Z

Basically you are trying to have some sort of a general check on the validity of the data in one table based on the data in another table (NOTE: actually probably not, waiting for the OP to clarify his question).

Workaround is either:

for general case - you can do quite a lot inside triggers in terms of complex data validation (including bringing your database to a halt for even the simplest of update queries, so use with care)
for a bit more specific case, you are trying to accomplish some dynamic validation (a more representative example would have been useful) - you can dynamically maintain the CHECK constraint with a 'snapshot' of your validation (this boils down to keeping a copy of the referenced table inside the check statement, so it is quite ugly)
maybe you can make a foreign key check. this is not how you normally design databases, but if you look at it semantically - you claim (with your CHECK) that there is a relationship between this table and some other entity. well, if that entity is not realised as separate table, maybe it should have been? (these kinds of problems are sometimes indicators of bad design)

Rob van Wijk · Accepted Answer · 2010-12-08 15:15:57Z

1

ALTER TABLE sometable 
ADD CONSTRAINT sometable_text_chk 
CHECK (text not in ('smth', 'smth else', 'one more thing'))
/

Regards, Rob.

answered Dec 8, 2010 at 15:15

Rob van Wijk

17.7k5 gold badges41 silver badges56 bronze badges

2 Comments

Vincent Malgrat Over a year ago

That's what I thought at first but the requirement is a bit more complex... you need to find the value in a lookup table, not the key itself

Rob van Wijk Over a year ago

But alex specifically mentioned "sometable" twice. And without a schema specified, that can only be the same table. You might be right though, but then I'm impressed at how you can read between the lines :-).

Michael Pakhantsov · Accepted Answer · 2010-12-08 15:13:50Z

0

You can't add such CHECK constraint, you need literally provide whole list of possible values:

CHECK (caption IN (Word1, Word2, Word3 ...))

However you can do in other way:

CHECK (Caption <> 'smth' and caption <> 'smth else' and caption <> 'one more thing')

But you lost checks from sometable.

answered Dec 8, 2010 at 15:13

Michael Pakhantsov

25.5k7 gold badges63 silver badges60 bronze badges

Comments

Ronnis · Accepted Answer · 2010-12-08 17:56:23Z

I admit this may be overkill. If you can create a fast-refreshable, refresh-on-commit materialized view with a definition that would count the number of bad values in your table, (maybe something like below):

select count(*) as rows_with_errors
  from data_table   a
  join lookup_table b on(a.caption = b.caption);

The query should return a count of zero, because values in data_table may NOT contain the values in the lookup_table. Then you create a check constraint on the materialized view itself, CHECK(rows_with_errors = 0).

Whenever an update or insert is committed into data_table, the materialized view will refresh. But (and here is the thing), if you entered a value in data_table that exists in lookup_table, the count(*) will return a non-zero value, which will cause the check constraint on the materialized view to fail, thus the entire insert/update statement will fail/rollback. Voila!

Collectives™ on Stack Overflow

Nested queries in check SQL statement

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related