CODEWITHSUNDEEP
sqloracle-databaseconstraintsddl
user2339718
user2339718

Reputation: 31

check constraint with if logic oracle sql

Is there a way to use if logic in an oracle sql check constraints?

This is my pseudo table:

create table child
(
    name,
    behaviour,
    treat,
);

Now what i want is that if the behaviour of child = 'bad' then treat != 'lollies'.

Upvotes: 3

Views: 656

Answers (4)

AnsonH
AnsonH

Reputation: 3266

If you have learned propositional logic before, we can apply it in this situation and explain how the top voted answer is derived.

We let A be the proposition of behavior = 'bad' and B be the proposition of treat != 'lollies'. The statement of "If behavior = 'bad', then treat != 'lollies'" can be written as "A → B".

Note that "A → B" is logically equivalent to "¬A ∨ B" (¬ means NOT and ∨ means OR). Therefore, we can translate the statement "¬A ∨ B" back to "behavior != 'bad' OR 'treat != 'lollies'".

In terms of SQL, we can write this as:

CHECK (behaviour <> 'bad' OR treat <> 'lollies')

This is equivalent to:

CHECK (NOT (behaviour = 'bad' AND treat = 'lollies'))

Upvotes: 0

Tony Andrews
Tony Andrews

Reputation: 132580

Sometimes it is easier to express using NOT to define what is not allowed:

CHECK (NOT (behaviour = 'bad' AND treat = 'lollies'))

... which means same as:

CHECK (behaviour != 'bad' OR treat != 'lollies')

It is easy to get this wrong, as other answers have shown!

Upvotes: 4

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

ALTER TABLE child 
  ADD CONSTRAINT bad_behaviour_CK 
    CHECK (NOT (behaviour = 'bad' AND treat = 'lollies')) ;

Tesed in SQL-Fiddle

Upvotes: 0

Mudassir Hasan
Mudassir Hasan

Reputation: 28751

Try this

create table children ( 
        childname VARCHAR2(50), behaviour VARCHAR2(50),treats VARCHAR2(50),
        constraint behave_treat check (behaviour = 'bad' AND treats != 'lollies')
        )

Upvotes: -1

Related Questions