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