I have 2 tables in PostgreSQL
authentication_type
| id | name | created_at | updated_at |
|---|---|---|---|
| c1cc0489-4740-4dca-9d63-14e4c26093ad | password | … | … |
accounts
| id | password | authentication_type_id | created_at | updated_at | |
|---|---|---|---|---|---|
| … | abc@example.com | (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
)