Search code examples
sqlpostgresqltriggerssequelize.jsconstraints

PostgreSQL prevent row with null password when authentication type name is password

I have 2 tables in PostgreSQL

authentication_type

id name created_at updated_at
c1cc0489-4740-4dca-9d63-14e4c26093ad password ... ...

accounts

id email password authentication_type_id created_at updated_at
... [email protected] (foreign key to the other table) ... ...

I would like to insert the row under the condition that

  • When authentication type name is password, the value of the password column cannot be null

What I have tried?

CHECK CONSTRAINT (
  authentication_type_id='c1cc0489-4740-4dca-9d63-14e4c26093ad'
  AND
  password IS NOT NULL
)

but this is not working. I am not sure how to write an if else condition here

Questions

  • Is it possible to do this with a CHECK CONSTRAINT or do I need a before insert trigger? What is the difference between both?

I am using sequelize to do this if that helps

Solution

  • You got the boolean logic wrong. You want one (or both) of the following:

    • the type_id is not the "password-id"
    • the password is not empty
    CHECK CONSTRAINT (
    authentication_type_id != 'c1cc0489-4740-4dca-9d63-14e4c26093ad'
    OR
    password IS NOT NULL
)