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

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

CodePudding user response：

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

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