What I would like to do is have SQL maintain the integrity of my data as opposed to doing it through my application. I would like to have it that the SQL will not let me add duplicate items per price list.
If I have 3 tables "PriceList", "Prices" and "InventoryItems"
Columns
PriceList: ID, Name
InventoryItem: ID, SKU, Name
Prices: ID, ListID, ItemID, Price
I cannot put a unique on the prices.itemid as this wont allow me to add the same item to multiple price lists.
| ID | ListID | ItemID | Price |
|---|---|---|---|
| 1 | 1 | 106 | 25.35 |
| 2 | 1 | 122 | 45.85 |
| 3 | 1 | 122 | 33.24 |
| 4 | 1 | 136 | 86.33 |
In the example above I would like there to be a constraint which will prevent the item on line 3 from being added as there is already an itemID 122 linked to ListID 1
Can this be done in SQL with relations/index or some other methodology.
CodePudding user response：
You can create a UNIQUE constraint, as in:
alter table my_table
add constraint uq1 unique (ListID, ItemID);