SQL table semi-unique row?

Question

I have an SQL table with basically the following structure:

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

In principle, any user-defined userID can store any number of short lines. However, no user is allowed to store two identical lines (although user 1 and user 2 can store the same line separately). I would, if at all possible, would like to implement this restriction at the database level, because IMHO structural restrictions should always be in tables , as well as in programs that insert / read data from tables.

The only thing I can think of is to add a third column, in which I combine the user ID and data with each insert and call the that column, but this seems too hacked for me. I am open to a complete restructuring of my tables if one of you guys has a better way to do this, which will allow me to set this field limit :)

Thanks!
Mala

Answer 1

It looks like you need a complex unique constraint for userID columns and data.

like this:

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

Answer 2

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

Answer 3

You need a composite key that you can add to table tablename , like this in MySQL:

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

Answer 4

I think the easiest solution would be to create an index

 create unique index ui on table (userID, data) 

although this may incur overhead.

This view looks just like your primary key, although it really depends on the roles of PK and userId in the tables throughout the rest of the schema.

Answer 5

I think a complex key will accomplish this task. Create a composite key with UserId and data columns. In this case, when the user tries to insert the same data more than once, he will cause an error that you can catch in your application and show the user the corresponding error message.

Hope this helps ...

Thanks.