Composite Unique Constraint SQL

sql unique-constraint
Stephen picture Stephen · Jun 14, 2016 · Viewed 6.9k times · Source

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

Gordon Linoff picture Gordon Linoff · Jun 14, 2016

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);

Related questions

How can I create a unique constraint on my column (SQL Server 2008 R2)?

I have SQL Server 2008 R2 and I want to set a unique column. There seems to be two ways to do this: "unique index" and "unique constraint". They are not much different from what I understand, although unique constraint is …

Read More
Can I add a UNIQUE constraint to a PostgreSQL table, after it's already created?

I have the following table: tickername | tickerbbname | tickertype ------------+---------------+------------ USDZAR | USDZAR Curncy | C EURCZK | EURCZK Curncy | C EURPLN | EURPLN Curncy | C USDBRL | USDBRL Curncy | C USDTRY | USDTRY Curncy | C EURHUF | EURHUF Curncy | C USDRUB | USDRUB Curncy | C I …

Read More
How do I ALTER a PostgreSQL table and make a column unique?

I have a table in PostgreSQL where the schema looks like this: CREATE TABLE "foo_table" ( "id" serial NOT NULL PRIMARY KEY, "permalink" varchar(200) NOT NULL, "text" varchar(512) NOT NULL, "timestamp" timestamp with time zone NOT NULL ) Now I want …

Read More