I've a users table:
id
type
merchant_id
agent_id
...
I want to add a check constraint using the following conditions:
if type == 'MERCHANT' then merchant_id is not null
if type == 'AGENT' then agent_id is not null
How this constraint is implemented?
Update:
I forgot to mention an extra requirement. the user can only have an agent_id or merchant_id.
You may add the following check constraints to the create table statement:
CREATE TABLE users (
id INTEGER,
type VARCHAR(55),
merchant_id INTEGER,
agent_id INTEGER,
...,
CHECK ((type <> 'MERCHANT' OR merchant_id IS NOT NULL) AND
(type <> 'AGENT' OR agent_id IS NOT NULL))
)
agent_id or merchant_id not both at a time so I've added extra condition: and ((agent_id is not null AND merchant_id is null) or (agent_id is null AND merchant_id is not null))You can check constraints in CREATE TABLE command:
DROP TABLE IF EXISTS users;
CREATE TABLE users (
id SERIAL PRIMARY KEY,
type VARCHAR (50),
merchant_id INT (50),
CONSTRAINT if_attribute_then_field_is_not_null
CHECK ( (NOT attribute) OR (field IS NOT NULL) )
);
OR in Alter Table command:
ALTER TABLE users
ADD CONSTRAINT if_attribute_then_field_is_not_null
CHECK ( (NOT attribute) OR (field IS NOT NULL) )
);
