Sql Table - Semi-unique Row?

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

Solution 1:

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

like this:

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

Solution 2:

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

Solution 3:

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

ALTERTABLE `tablename` ADDUNIQUE KEY (`userID`, `data`);

Solution 4:

I think the simplest solution would be to create an index

createunique index ui ontable (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.

Solution 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.