References and Check Constraints

[juliane26]

Hi,

one question:

two tables: CUSTOMER and ADDRESS, address having a reference to customer.

Is there a way to limit the number of addresses for each customer to lets say, 3 ?

I suggested modelling address1, address2 and address3, but since the number might change this should be avoided.

Check constraints don't allow functions like count().

I was thinking about triggers, an after trigger who would fire when having more than three for this customer and then returning a special return code to the application.

Question: is that possible ? How to return an SQLCode to the application from a trigger ? I did not find a sample and never used it myself before.

Thanks a lot !

 

[gregsimpson]

Juliane26,

I have achieved same by effectively having a small int columm on address, and each time an address is added it gets the next value. I then put a constraint on the database to say the count must be < 3. So addresses number 0, 1, 2 were allowed but not 3. However this was perhaps not my best ever design as I had to code login to take into account that the second address for example, is deleted.

I've not used as you suggest the database to fully implement the solution, though I imagine someone has done something similar and would be interested in seeing the final solution.

Cheers
Greg