Nested queries in check SQL statement

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 (
    captio开发者_运维问答n 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.

---

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)

---

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

Regards, Rob.

---

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.

---

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!