GD REF CONSTRAINTS

CREATE DOMAIN d_fk_metaname AS CHAR(31) CHARACTER SET unicode_fss;
 
CREATE TABLE gd_ref_constraints (
  id               dintkey,
  constraint_name  d_fk_metaname UNIQUE,
  const_name_uq    d_fk_metaname,
  match_option     CHAR(7)  CHARACTER SET NONE,
  update_rule      CHAR(11) CHARACTER SET NONE,
  delete_rule      CHAR(11) CHARACTER SET NONE,
 
  constraint_rel   d_fk_metaname,
  constraint_field d_fk_metaname,
  ref_rel          d_fk_metaname,
  ref_field        d_fk_metaname,
 
  ref_state        CHAR(8) CHARACTER SET NONE,
  ref_next_state   CHAR(8) CHARACTER SET NONE,
 
  constraint_rec_count COMPUTED BY (
    (SELECT iif(i.rdb$statistics = 0, 0, Trunc(1/i.rdb$statistics + 0.5)) FROM rdb$indices i
      JOIN rdb$relation_constraints rc ON rc.rdb$index_name = i.rdb$index_name
      WHERE rc.rdb$relation_name = constraint_rel AND rc.rdb$constraint_type = 'PRIMARY KEY')),
 
  constraint_uq_count COMPUTED BY ((
    SELECT
      iif(i.rdb$statistics = 0, 0, Trunc(1/i.rdb$statistics + 0.5))
    FROM
      rdb$indices i
      JOIN rdb$index_segments iseg
        ON iseg.rdb$index_name = i.rdb$index_name
          AND iseg.rdb$field_name = constraint_field
          AND i.rdb$segment_count = 1
      JOIN rdb$relation_constraints rc
        ON rc.rdb$index_name = i.rdb$index_name
          AND rc.rdb$constraint_name = constraint_name
          AND rc.rdb$constraint_type = 'FOREIGN KEY'
  )),
 
  ref_rec_count COMPUTED BY (
    (SELECT iif(i.rdb$statistics = 0, 0, Trunc(1/i.rdb$statistics + 0.5)) FROM rdb$indices i
      JOIN rdb$relation_constraints rc ON rc.rdb$index_name = i.rdb$index_name
      WHERE rc.rdb$relation_name = ref_rel AND rc.rdb$constraint_type = 'PRIMARY KEY')),
 
  CONSTRAINT gd_pk_ref_constraint PRIMARY KEY (id),
  CONSTRAINT gd_chk1_ref_contraint CHECK (ref_state IN ('ORIGINAL', 'TRIGGER')),
  CONSTRAINT gd_chk2_ref_contraint CHECK (ref_next_state IN ('ORIGINAL', 'TRIGGER'))
);

См. также

• Отключение внешних ключей