Zeoslib Portal

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

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

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

Thanks for clarifying FK vs PK for me.

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

Kerin

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.

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