Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Making a CHECK CONSTRAINT with OR in postgres SQL

I'm wondering if it's possible to evaluate some conditions in a CHECK CONSTRAINT first and then do an OR with another expression.

My situation:

4 Tables:\

  • main, which an id, a type variable(lets make it a smallint of 0-2), and foreign keys to the following tables:
  • a, b, and c, which only contain an id

I would want to make a check constraint which basically follows the following logic:

if main.type = 0:
    main.a != NULL
    main.b = NULL
    main.c = NULL
elif main.type = 2:
    main.a = NULL
    main.b = NULL
    main.c != NULL

if the type = 1 I don't care what is filled in.

I tried doing a CHECK CONSTRAINT like so:

CHECK((main.type = 0 AND main.a != NULL AND main.b = NULL AND main.c = NULL) OR
(main.type = 2 AND main.a = NULL AND main.b = NULL AND main.c != NULL))

The problem is that the inner parentheses get deleted/ ignored after applying this constraint which means it becomes mostly nonsense and it doesn't work.

Is this possible to solve with a CHECK CONSTRAINT or should a trigger be used for this?

like image 745
Blanen Avatar asked Jan 26 '17 11:01

Blanen


People also ask

What is CHECK constraint in PostgreSQL?

In PostgreSQL, the Check constraint can be defined by a separate name. It is used to control the value of columns being inserted. It allows us to verify a condition that the value being stored into a record. If the statement is false, then the data disrupts the constraint which is not saved in the table.

Is check a column constraint?

The CHECK constraint is used to limit the value range that can be placed in a column. If you define a CHECK constraint on a column it will allow only certain values for this column.

How do I drop a CHECK constraint in PostgreSQL?

Currently DROP CONSTRAINT drops only CHECK constraints. To remove a PRIMARY or UNIQUE constraint, drop the relevant index using the DROP INDEX command. To remove FOREIGN KEY constraints you need to recreate and reload the table, using other parameters to the CREATE TABLE command.


1 Answers

!= NULL! That is not going to work. This seems to be the logic you want:

CHECK ( (type = 0 and a is not null and b is null and c is null) or
        (type = 2 and a is null and b is null and c is not null) or
        (type = 1)
      )
like image 104
Gordon Linoff Avatar answered Sep 18 '22 17:09

Gordon Linoff