---------- Original Message -----------
From: "Vlad Khorsun" <hv...@users.sourceforge.net>
> It is physically impossible to check detail data in all instances
> of detail GTT when master record is changed.
>
> Regards,
> Vlad
------- End of Original Message -------
At time of upgrade, what will happen to databases that already have such FK's
in
place?
If this has the potential to break existing databases on a minor upgrade
(2.5.3)
where backup/restore wouldn't normally be needed, or nobody would expect things
to blow up during restore, it'd be good to make that outcome clear, maybe
provide scripts for finding out if you're affected [1], and hint at what they
can replace the FKs with (CHECK or TRIGGER)?
We thought we had 6 such situations, but it turns out the FKs were all from
tables with TMP in the name that weren't actually defined as GTTs. So we dodged
the bullet. But I could have sworn the release notes said GTT->permanent was
okay, I'm pretty sure I had told other people as much, as Sean points out, it
"felt right", so I'm sure we're not the only ones to have thought so...
[1] My attempt, based on the rules stated in the release notes:
select
fkc.rdb$constraint_name fk_name,
fkc.rdb$relation_name fk_table,
r1t.rdb$type_name fk_table_type,
fkseg.rdb$field_name fk_field,
pkc.rdb$constraint_name pk_name,
pkc.rdb$constraint_type pk_type,
pkc.rdb$relation_name pk_table,
r2t.rdb$type_name pk_table_type,
pkseg.rdb$field_name pk_field,
fk.rdb$delete_rule delete_rule,
fk.rdb$update_rule update_rule
from rdb$relations r1
inner join rdb$types r1t on r1.rdb$relation_type = r1t.rdb$type and
r1t.rdb$field_name = 'RDB$RELATION_TYPE'
inner join rdb$relation_constraints fkc on fkc.RDB$RELATION_NAME =
r1.RDB$RELATION_NAME
inner join rdb$ref_constraints fk on fkc.rdb$constraint_name =
fk.rdb$constraint_name
inner join rdb$relation_constraints pkc on pkc.rdb$constraint_name =
fk.rdb$const_name_uq
inner join rdb$relations r2 on r2.rdb$relation_name = pkc.RDB$RELATION_NAME
inner join rdb$types r2t on r2.rdb$relation_type = r2t.rdb$type and
r2t.rdb$field_name = 'RDB$RELATION_TYPE'
inner join rdb$indices fkix on fkix.rdb$index_name = fkc.rdb$index_name
inner join rdb$indices pkix on fkix.rdb$foreign_key = pkix.rdb$index_name
inner join rdb$relation_constraints pk on pk.rdb$index_name =
pkix.rdb$index_name
inner join rdb$index_segments fkseg on fkix.rdb$index_name =
fkseg.rdb$index_name
inner join rdb$index_segments pkseg on pkix.rdb$index_name =
pkseg.rdb$index_name
and pkseg.rdb$field_position = fkseg.rdb$field_position
where (r1t.rdb$type_name in ('GLOBAL_TEMPORARY_PRESERVE',
'GLOBAL_TEMPORARY_DELETE')
or r2t.rdb$type_name in ('GLOBAL_TEMPORARY_PRESERVE',
'GLOBAL_TEMPORARY_DELETE')
)
and (r1t.rdb$type_name = 'PERSISTENT'
or r2t.rdb$type_name = 'PERSISTENT'
or (r1t.rdb$type_name = 'GLOBAL_TEMPORARY_PRESERVE' and r2t.rdb$type_name =
'GLOBAL_TEMPORARY_DELETE')
);
-Philip
------------------------------------------------------------------------------
How ServiceNow helps IT people transform IT departments:
1. Consolidate legacy IT systems to a single system of record for IT
2. Standardize and globalize service processes across IT
3. Implement zero-touch automation to replace manual, redundant tasks
http://pubads.g.doubleclick.net/gampad/clk?id=51271111&iu=/4140/ostg.clktrk
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
