--- In firebird-support@yahoogroups.com, "Maurizio P."
<mauriz_po@...> wrote:

>
> hi ,
> i searched hours , but i could not find any help so
> i try to ask here (my first time) for an help :
>
> i thought that normally it would not be possible to
> delete a row which is a foreign key in another table ,
>
> the example :
>
> tableBanks
> -idBAnk(PK)
> -desBAnk
>
> tableCustomers
> -idCustomer(PK)
> -desCustomer
> -idBank (FK)
> ..ecc
>
> in this situation i can delete every tableCustomer row , even if

them

> exists as FK in the second table ,
> is it the normal behave ?

Yes, you have misunderstood the purpose of a foreign key constraint.
A foreign key constraint is designed to ensure consistent data.
Removing a customer record from the tableCustomers does not violate
consistency. Consistency is broken if any record in either table no
longer makes sense because of the operation you have performed.
Clearly, there is no problem here.

It is rather the other way around. It would be a problem if you
deleted from tableBanks. What would happen to the tableCustomers
records that are 'pointing to' the tableBanks record you delete?

In Firebird you have several options when handling the delete from
tableBanks.

If you just declare the foreign key with no 'on delete' clause, the
transaction that issued the delete will receive an exception.
Otherwise, you can specify alternative behaviour (eg on Delete
cascade or on Delete set null). This will allow the delete on
tableBanks and if you define cascade will delete the tableCustomers
record, or if you define the set null will update the
tableCustomers.idBank to null. In all of these cases, consistency is
maintained. You can never get a case where idBank = 7, yet you have
no record in CustomerBank with such an ID.

> how can i prevent it ?

Prevent what? What are you trying to achieve?

Although Consistency is never broken, perhaps your business rules or
legal processes require that a tableCustomer record can not be
deleted. If that is the case, you need to create a 'before delete'
trigger on tableCustomer and raise a custom exception.

Adam