SQL table - semi-unique row?

Question

Hi

I have an SQL table with basically the following structure:

PK (int, primary key), userID (int), data (varchar 64)

Basically, any user as defined by userID is allowed to store any number of short strings. However, no user is allowed to store two identical strings (although user 1 and user 2 can both store the same string separately). I would, if at all possible, like to implement this restriction at the database level because IMHO structural constraints should always be in the tables, as well as in the programs inserting/reading data from the tables.

The only thing I can think of is adding a third column in which I concatenate userID and data upon every insert, and call that column unique, but that seems too "hacky" to me. I am open to completely restructuring my tables if one of you guys has a better way of doing this that will allow me to put this constraint on the fields :)

Thanks!
Mala

Answer 1

Sounds like you need a composite unique constraint on the userID and data columns.

like this:

CONSTRAINT my_contraint_name UNIQUE ([userID], [data])

Answer 2

What if we have a unique constraint on UserId and Data. I hope it should solve your issue.

Answer 3

You want a composite key, which you can add to your table tablename like this in MySQL:

ALTER TABLE `tablename` ADD UNIQUE KEY (`userID`, `data`);

Answer 4

I think the simplest solution would be to create an index

create unique index ui on table (userID, data)

though there may be an overhead with this.

This kind of looks like it should be your primary key as well, though that really depends on the role PK and userId play in tables throughout the rest of the schema.

Answer 5

I think a composite key will do the task. Create a composite key with UserId and data columns. In this case, when a user tries to insert same data morethan once, it will throw error, which you can catch in your application and show the user an appropriate error message.

Hope it helps...

Thanks.