Data dilemma

[kerino]

Me again,
I have a table of items (ITM), and each item has a price code e.g.
PK...Item.....Price_Code
1.....IT2.......PC1
2.....IT32.....PC3
3.....IT9.......PC1

The price codes are held in another table (PCD), but the price per item can vary depending on the quantity e.g.
PK....Price_Code.....Qnty...Price
1......PC1................10.....20.00
2......PC1................20.....18.43
3......PC1................50.....15.22
4......PC3................8.......45.00
5......PC3...............12......43.23 etc.

(NB. The dots are just for formatting - spaces seem to get stripped out)

The problem I have is that I want to ensure that no part of a price_code may be deleted if it is used by any items. So records 1,2,3 of PCD all beong to Price_Code PC1, so none of these records can be deleted as PC1 is used by items IT2 and IT9.
I have tried to set up a referential integrity check as follows
ALTER TABLE ITM ADD CONSTRAINT FK_ITEM FOREIGN KEY(Price_Code) REFERENCES PCD(Price_Code)
but I get errors because the Price Code key in PCD table is not unique.

Is there a way to do this? Should I be holding the data in a different form perhaps?

Many thanks
Kerin

[fcodebue]

you cant make a FK on a not primary key field...

Foreign key would be a PK or a candidate key in referenced table

[kerino]

Thanks for clarifying FK vs PK for me.

Does this mean there is no way to implement referential integrity between the two tables?

Kerin

[trupka]

FK is not only way to implement ref.int. I think problem can be solved with trigger on PCT table - raise exception inside before_delete trigger if data exists in ITM table.

[kerino]

Thank you for the advice. I will implement your trigger idea.