Composite Unique Constraint SQL

Question

I have a table with parents and children, each record has a PRIMARY KEY id, a 'name', and a 'parent' that references another record's 'id'.

Can I enforce UNIQUE constraint on the 'name' among records that share a 'parent'?

Answer 1

Yes. This would be a composite unique key:

alter table t add constraint unq_t_parent_name unique (parent, name);

If you don't care if the constraint has a name, then you can just create a unique index:

create unique index unq_t_parent_name on t(parent, name);