SQL - How can I apply a "semi-unique" constraint?

Question

Hi,

I have a (simplified) table consisting of three columns:

id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
foreignID INT NOT NULL,
name VARCHAR NOT NULL

Basically, I would like to add a constraint (at the database level rather than at the application level) where it only possible for one unique 'name' to exist per foreignID. For example, given the data (id, foreignid, name):

1,1,Name1
2,1,Name2
3,1,Name3
4,2,Name1
5,2,Name2

I want the constraint to fail if the user tries to insert another 'Name3' under foreignId 1, but succeed if the user tries to insert 'Name3' under foreignId 2. For this reason I cannot simply make the whole column UNIQUE.

I am having difficulty coming up with a SQL expression to achieve this, can anybody help me?

Thanks

Answer 1

create a composite (multiple - column ) key... on those two columns

Create Unique Index MyIndexName On TableName(ForeignID, Name)

Answer 2

Add a unique constraint on (ForeignID, Name). The exact syntax for that depends on your DBMS. For SQL Server:

CREATE UNIQUE INDEX IndexName ON YourTable (ForeignID, Name)

(The terms "unique constraint", "unique key" and "unique index" mean roughly the same.)