Search code examples
postgresqlconstraints

Add constraint on a column based on another only for specific values

I have a sql table named products that looks like this:

p_id p_name
SS100 Smoked salmon 100g
FS50 Fried shrimp 50g
MC400 Mac and cheese 400g
SS300 Smoked salmon 300g

I need to add a constraint so that if p_name starts with Smoked salmon then the p_id must start with SS.

I tried using this: CONSTRAINT my_constraint CHECK (p_id LIKE 'SS%' AND p_name LIKE 'Smoked salmon%'); But that doesn't work since there are p_id and p_name with different values.

Thanks in advance.

Solution

  • You may add an OR condition to the constraint as follows:

    CONSTRAINT my_constraint CHECK (p_name LIKE 'Smoked salmon%' AND p_id LIKE 'SS%'
                                OR p_name NOT LIKE 'Smoked salmon%');