Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
gregoryagu
Yak Posting Veteran
80 Posts |
 Posted - 2012-10-19 : 17:53:06
|
| I have a table that looks like this:Id(PK, int, not null)ListId(FK, int, not nullValue(varchar(50))ItemId (int, not null)There should be no duplicates of ListId/ItemId pair:Sample Data - OK1 10 1 Item12 10 2 Item23 11 1 Item34 11 2 Item4This should not be allowed:5 11 2 Item5The 2 should not be allowed because it is a duplicate of 11/2, ie the item above.How would I write this as a constraint or key?Greg |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-10-19 : 17:59:50
|
| You say "no duplicates of ListId/ItemId" but your example demonstrates no dupes of ListID and Value. Which is it? (maybe your sample data is formatted incorrectly?) |
 |
|
|
gregoryagu
Yak Posting Veteran
80 Posts |
Posted - 2012-10-19 : 18:04:23
|
| Row 5 shows the duplicate, in other words the List Item of 11 with the ItemId of 2 is in there twice, row 4 and row 5. |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2012-10-19 : 18:15:28
|
It looks like the table defintion and teh sample data have the columns out of order. As for the constraint, try this:ALTER TABLE <schema.table_name > ADD CONSTRAINT <constraint_name> UNIQUE NONCLUSTERED ( ListID ASC, ItemID ASC )GO |
|
|
|
gregoryagu
Yak Posting Veteran
80 Posts |
Posted - 2012-10-19 : 18:30:05
|
| Thanks, that works!Greg |
|
|
|
|
|
|
Unique Constraint