|Subject||Re: get foreign key|
> > Cascading update on the foreign key.my client wants each customer can only do transactions in 1 currency.
and changing currency in customer record mustn't only change currency
in sales order but also accounts/cost center and currency rates.
rather than changing the whole sales order, i guess preventing the
users from changing the currency is a better approach.
> > IF EXISTS (SELECT 1 FROM sales WHERE customer = id)it works but if i add another table which references with customer
currency, than i would have to change all the queries. i was thinking
of a more flexible query.
i get this query from an
able to list all foreign keys and references tables from the customer
table. but i still haven't figure out how to get the active references
from one customer record.
SELECT DISTINCT rc.RDB$CONSTRAINT_NAME AS "constraint_name",
rc.RDB$RELATION_NAME AS "on table", d1.RDB$FIELD_NAME AS "on field",
d2.RDB$DEPENDED_ON_NAME AS "references table",
d2.RDB$FIELD_NAME AS "references field"
FROM RDB$RELATION_CONSTRAINTS AS rc LEFT JOIN
RDB$REF_CONSTRAINTS refc ON rc.RDB$CONSTRAINT_NAME =
refc.RDB$CONSTRAINT_NAME LEFT JOIN
RDB$DEPENDENCIES d1 ON d1.RDB$DEPENDED_ON_NAME = rc.RDB$RELATION_NAME
RDB$DEPENDENCIES d2 ON d1.RDB$DEPENDENT_NAME = d2.RDB$DEPENDENT_NAME
WHERE rc.RDB$CONSTRAINT_TYPE = 'FOREIGN KEY' AND
d1.RDB$DEPENDED_ON_NAME <> d2.RDB$DEPENDED_ON_NAME AND
d1.RDB$FIELD_NAME <> d2.RDB$FIELD_NAME AND rc.RDB$RELATION_NAME =
thanks, and sorry for the long posting