unique constraint grouped by other column

Question

I have a table which has a father_id column and a serial_number column, I want to create a unique constraint on the serial number by the father id.

Meaning rows that have the same father_id can't have the same serial_number, but rows that have a different father_id can have the same serial_number.

Answer 1

To rephrase the requirement, you want the combination of father_id and serial_number to be unique. Once the requirement is phrased like that, it's easier to translate to SQL:

ALTER TABLE mytable
ADD CONSTRAINT mytable_unq UNIQUE (father_id, serial_number);