CODEWITHSUNDEEP
sqloracle-databaseconstraints
user964843
user964843

Reputation:

Oracle SQL - multiple column constrainst on single constrainst statement

Is it possible to create a constrainst that affects multiple columns at once?

Lets say I'm creating this table which contains a bit map:

CREATE TABLE weekdays
(
    id           NUMBER(5,0)

    monday       NUMBER(1,0)  NOT NULL    DEFAULT 0,
    tuesday      NUMBER(1,0)  NOT NULL    DEFAULT 0,
    wednesday    NUMBER(1,0)  NOT NULL    DEFAULT 0,
    thursday     NUMBER(1,0)  NOT NULL    DEFAULT 0,
    Friday       NUMBER(1,0)  NOT NULL    DEFAULT 0,
    Saturday     NUMBER(1,0)  NOT NULL    DEFAULT 0,
    Sunday       NUMBER(1,0)  NOT NULL    DEFAULT 0,


    CONSTRAINT monday_b CHECK (monday IN (1, 0))
    --... OTHER WEEKDAYS CONSTRAINSTS HERE
);

Since all weekdays will have the same constrainst (values of 1 or 0), is it possible to do somethings like this:

CONSTRAINT wd_b CHECK ((monday, tuesday, wednesday, thursday, ...) IN (1, 0))

Upvotes: 1

Views: 82

Answers (3)

David דודו Markovitz
David דודו Markovitz

Reputation: 44921

    check 
    (
        0 <= all (monday,tuesday,wednesday,thursday,Friday,Saturday,Sunday)
    and 1 >= all (monday,tuesday,wednesday,thursday,Friday,Saturday,Sunday)
    )

or

    check 
    (
        0 <= least    (monday,tuesday,wednesday,thursday,Friday,Saturday,Sunday)
    and 1 >= greatest (monday,tuesday,wednesday,thursday,Friday,Saturday,Sunday) 
    )

Upvotes: 0

user5683823
user5683823

Reputation:

You could do something like

check(monday in (0,1) and tuesday in (0,1) and...)

However if this was my table I would prefer the seven separate constraints (and perhaps define them at the column level - so called "inline" constraints). When one of the values is in violation, with separate constraints I will now immediately exactly which constraint was violated. With a row-level constraint I will only know that "one of them" was violated.

Upvotes: 3

Aleksej
Aleksej

Reputation: 22949

You could build a single constraint to check that all the values are not null and that their value is 0 or 1 this way:

CREATE TABLE weekdays
(
    id           NUMBER(5,0),
    monday       NUMBER(1,0)  DEFAULT 0,
    tuesday      NUMBER(1,0)  DEFAULT 0,
    wednesday    NUMBER(1,0)  DEFAULT 0,
    thursday     NUMBER(1,0)  DEFAULT 0,
    Friday       NUMBER(1,0)  DEFAULT 0,
    Saturday     NUMBER(1,0)  DEFAULT 0,
    Sunday       NUMBER(1,0)  DEFAULT 0,
    CONSTRAINT check_all CHECK (regexp_like(monday || tuesday || wednesday || thursday || Friday || Saturday || Sunday, '[01]{7}'))
);

However, think of it as an exercise: I do not recommend this approach because it's hard to maintain, difficult to read, it has poor performance, ...

Upvotes: 0

Related Questions