GD REF CONSTRAINTS - История изменений

SYSDBA в 10:07, 15 июля 2021

2021-07-15T10:07:51Z

SYSDBA в 10:07, 15 июля 2021

2021-07-15T10:07:35Z

SYSDBA в 15:11, 12 ноября 2010

2010-11-12T15:11:00Z

SYSDBA: Новая страница: « CREATE DOMAIN d_fk_metaname AS CHAR(31) CHARACTER SET unicode_fss; CREATE TABLE gd_ref_constraints ( id dintkey, cons…»

2010-06-21T10:56:31Z

Новая страница: «<syntaxhighlight lang="SQL"> CREATE DOMAIN d_fk_metaname AS CHAR(31) CHARACTER SET unicode_fss; CREATE TABLE gd_ref_constraints ( id dintkey, cons…»

Новая страница

<syntaxhighlight lang="SQL">
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
JOIN rdb$relation_constraints rc ON rc.rdb$index_name = i.rdb$index_name
WHERE i.rdb$relation_name = constraint_rel AND i.rdb$segment_count = 1 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'))
);
</syntaxhighlight>

[[Category:База данных]]

@@ Строка 51: / Строка 51: @@
 === См. также ===
-* [Отключение внешних ключей]
+* [[Отключение внешних ключей]]
 [[Category:База данных]]

@@ Строка 48: / Строка 48: @@
 );
 </syntaxhighlight>
 [[Category:База данных]]

@@ Строка 23: / Строка 23: @@
        WHERE rc.rdb$relation_name = constraint_rel AND rc.rdb$constraint_type = 'PRIMARY KEY')),
-   constraint_uq_count COMPUTED BY (
+   constraint_uq_count COMPUTED BY ((
-     (SELECT iif(i.rdb$statistics = 0, 0, Trunc(1/i.rdb$statistics + 0.5)) FROM rdb$indices i
+     SELECT
-       JOIN rdb$index_segments iseg ON iseg.rdb$index_name = i.rdb$index_name
+      iif(i.rdb$statistics = 0, 0, Trunc(1/i.rdb$statistics + 0.5))
-        AND iseg.rdb$field_name = constraint_field
+    FROM
-       JOIN rdb$relation_constraints rc ON rc.rdb$index_name = i.rdb$index_name
+      rdb$indices i
-      WHERE i.rdb$relation_name = constraint_rel AND i.rdb$segment_count = 1 AND rc.rdb$constraint_type = 'FOREIGN KEY')),
+       JOIN rdb$index_segments iseg
    ref_rec_count COMPUTED BY (