Is there a way to add a table constraint based on one of its columns value?

user_11615575 0
Viewed 54

To be more specific. I have this table for events

create table event(
 id NUMERIC(3) PRIMARY KEY,
 date DATE
)

I need to add another column (canceled) that accepts only 0 and 1 values. The problem here is that the column also needs a constraint that doesn't allow events before 1.01.2021 to be cancelled (meaning that cancel value cannot be 1(true) for an event whose date is, for example, 15.09.2020). Any hints? This is the 'closest' I got, but I know it's wrong and by searching on other threads, I learned that queries are not allowed inside a constraint. What I tried (which I know is not correct as it doesn't allow adding entries with their date before 1.01.2021):

ALTER TABLE event
ADD canceled number(1) DEFAULT 0 CHECK(canceled in (0,1));

ALTER TABLE event
ADD CONSTRAINT C_event_canceled CHECK(date >= to_date('01/01/2021', 'dd/mm/yyyy'));

Forgot to mention, I cannot use any functions or something related, jus plain queries.

ddl oracle sql
2 Answers

A check constraint can have a condition based on multiple columns. Here, you want to allow any date if canceled is 0, or a specific date range if it isn't:

ALTER TABLE event
ADD CONSTRAINT C_event_canceled CHECK(
    canceled = 0 OR
    date >= to_date('01/01/2021', 'dd/mm/yyyy')
);
user_24227760

doesn't allow events before 1.01.2021 to be canceled

Mureinik's answer is correct. However, this version is closer to how you express what you want to check:

check (not (cancelled = 1 and date < date '2021-01-01') )

Note that Oracle uses date as a prefix to date constants. In addition, date is a data type -- that makes it a very bad choice for a column name. I would suggest:

check (not (cancelled = 1 and event_date < date '2021-01-01') )
user_11440350
Related