IBPhoenix - Resources

Section Navigation

Brief Description

InterBase V4.5 GPRE support for the extended foreign key definition.

Purpose

This feature is a part of the cascading referential integrity (RI) which has already been introduced. Since the definition for a FOREIGN KEY has been extended hence GPRE should support this. The new definition can be used wherever the previous (IB 4.0) FOREIGN KEY definition was used, i.e. , in a CREATE TABLE and ALTER TABLE statement. Details of the extended foreign key definition can be found here.

Feature Description

Syntax for extended foreign key definition:

ALTER TABLE table <operation> [, <operation> ...];

<operation>=(ADD <col_def> | ADD <tconstraint> | DROP col
| DROP CONSTRAINT constraint )
CREATE TABLE table [EXTERNAL [FILE] "<filespec>"]
(<col_def> [, <col_def> | <tconstraint> ...]);

Column level

<col_def>=col { <datatype> | COMPUTED [BY] (<expr> | domain}
[DEFAULT {literal | NULL | USER}]
[NOT NULL] [<col_constraint>]
[COLLATE collation] <col_constraint>=[CONSTRAINT constraint] <constraint_def>

<constraint_def>={UNIQUE | PRIMARY KEY
| CHECK (<search_condition>)
| REFERENCES <referenced_table_and_column> [<referential_triggered_action>]

Table level

<tconstraint>=CONSTRAINT constraint <tconstraint_def> [<tconstraint>]

<tconstraint_def>={ {PRIMARY KEY | UNIQUE} (col [, col ...])
| FOREIGN KEY (col [, col ...]) <references_specification>
| CHECK (<search_condition>)
} <references_specification>=
REFERENCES <referenced_table_and_column> [<referential_triggered_action>]
<referenced_table_and_column>=other_table [ (col [, col ...]) ]

<referential_triggered_action>=
ON UPDATE <referential_action> [ ON DELETE <referential_action> ]
| ON DELETE <referential_action> [ ON UPDATE <referential_action> ]
<referential_action>=CASCADE | NO ACTION | SET DEFAULT | SET NULL

Complete syntax for CREATE TABLE and ALTER TABLE statement can be found in the Language Reference Manual

New error messages are produced by GPRE :

(E) <file_name>:<line>: FOREIGN KEY column count does not match PRIMARY KEY

Example:

CREATE TABLE TPRIM (
TPRIM_F1 VARCHAR(20) NOT NULL,
TPRIM_F2 CHAR(10) NOT NULL,
TPRIM_F3 INTEGER,
PRIMARY KEY (TPRIM_F1, TPRIM_F2) );
CREATE TABLE TFOR (
TFOR_F1 VARCHAR(20) NOT NULL,
TFOR_F2 CHAR(10) NOT NULL,
TFOR_F3 INTEGER,
FOREIGN KEY (TFOR_F1, TFOR_F2) REFERENCES
TPRIM (TPRIM_F1) );
(E) <file_name>:<line>: expected UPDATE or DELETE encountered "<word>"

Example:

CREATE TABLE TFOR (
TFOR_F1 VARCHAR(20) NOT NULL,
TFOR_F2 CHAR(10) NOT NULL,
TFOR_F3 INTEGER,
FOREIGN KEY (TFOR_F1, TFOR_F2) REFERENCE TPRIM (TPRIM_F1, TPRIM_F2) )
ON <word> CASCADE ON <word> SET NULL );
(E) <file_name>:<line>: expected UPDATE encountered "<word>"

Example:

CREATE TABLE TFOR (
TFOR_F1 VARCHAR(20) NOT NULL,
TFOR_F2 CHAR(10) NOT NULL,
TFOR_F3 INTEGER,
FOREIGN KEY (TFOR_F1, TFOR_F2) REFERENCES TPRIM (TPRIM_F1, TPRIM_F2) )
ON DELETE CASCADE ON <word> SET NULL );
(E) <file_name>:<line>: expected DELETE encountered "<word>"

Example:

CREATE TABLE TFOR (
TFOR_F1 VARCHAR(20) NOT NULL,
TFOR_F2 CHAR(10) NOT NULL,
TFOR_F3 INTEGER,
FOREIGN KEY (TFOR_F1, TFOR_F2) REFERENCES TPRIM (TPRIM_F1, TPRIM_F2) )
ON UPDATE NO ACTION ON <word> SET NULL );
(E) <file_name>:<line>: expected ACTION encountered "<word>"

Example:

CREATE TABLE TFOR (
TFOR_F1 VARCHAR(20) NOT NULL,
TFOR_F2 CHAR(10) NOT NULL,
TFOR_F3 INTEGER,
FOREIGN KEY (TFOR_F1, TFOR_F2) REFERENCES TPRIM (TPRIM_F1, TPRIM_F2) )
ON DELETE NO <word> ON UPDATE NO <word> );
(E) <file_name>:<line>: expected NULL or DEFAULT encountered "<word>"

Example:

CREATE TABLE TFOR (
TFOR_F1 VARCHAR(20) NOT NULL,
TFOR_F2 CHAR(10) NOT NULL,
TFOR_F3 INTEGER,
FOREIGN KEY (TFOR_F1, TFOR_F2) REFERENCES TPRIM (TPRIM_F1, TPRIM_F2) )
ON UPDATE SET <word> ON DELETE SET <word> );
(E) <file_name>:<line>: expected NO ACTION or CASCADE or SET DEFAULT orSET NULL encountered "<word>"

Example:

Functional Changes

The InterBase engine has already been modified to support Referential Integrity. Thus GPRE has to do folowing:
Parse the new extended foreign key definition
Generate blr and pass it to the engine

The blr structure for CREATE TABLE and ALTER TABLE statement remains the same except for the constraint definition. In case the extended foreign key definition is not used, GPRE generates blr for the constraint definition as it was before:

isc_dyn_rel_constraint, 0,0,
isc_dyn_def_foreign_key, 0,0,
isc_dyn_fld_name, <length,length>, <for_fld_name1>,
isc_dyn_fld_name, <length,length>, <for_fld_name2>,
isc_dyn_fld_name, <length,length>, <for_fld_nameN>,
isc_dyn_idx_foreign_key, <length,length>, <prim_rel_name>,
isc_dyn_idx_ref_column, <length,length>, <prim_fld_name1>,
isc_dyn_idx_ref_column, <length,length>, <prim_fld_name2>,
 . . .
isc_dyn_idx_ref_column, <length,length>, <prim_fld_nameN>,
isc_dyn_end,
isc_dyn_end,

Note: Since constraint definition is the same for both CREATE TABLE and ALTER TABLE statement, all the examples contain only CREATE TABLE statement.

Example:

CREATE TABLE TPRIM (
TPRIM_F1 VARCHAR(20) NOT NULL,
TPRIM_F2 CHAR(10) NOT NULL,
TPRIM_F3 INTEGER,
PRIMARY KEY (TPRIM_F1, TPRIM_F2) );
CREATE TABLE TFOR (
TFOR_F1 VARCHAR(20) NOT NULL,
TFOR_F2 CHAR(10) NOT NULL,
TFOR_F3 INTEGER,
FOREIGN KEY (TFOR_F1, TFOR_F2) REFERENCES
TPRIM(TPRIM_F1, TPRIM_F2) );

isc_dyn_rel_constraint, 0,0,
isc_dyn_def_foreign_key, 0,0,
isc_dyn_fld_name, 7,0, 'T','F','O','R','_','F','1',
isc_dyn_fld_name, 7,0, 'T','F','O','R','_','F','2',
isc_dyn_idx_foreign_key, 5,0, 'T','P','R','I','M',
isc_dyn_idx_ref_column, 8,0, 'T','P','R','I','M','_','F','1',
isc_dyn_idx_ref_column, 8,0, 'T','P','R','I','M','_','F','2',
isc_dyn_end,
isc_dyn_end,

If the extended foreign key definition is used, GPRE generates additional blr for the constraint definition as shown below.

<referential triggered action> ::=ON DELETE NO ACTION ON UPDATE NO ACTION

Note: The foreign key is left unchanged. This may cause the primary key update/delete to fail due to referential integrity checks.

 isc_dyn_rel_constraint, 0,0,
isc_dyn_def_foreign_key, 0,0,
isc_dyn_foreign_key_update,
isc_dyn_foreign_key_none,
isc_dyn_foreign_key_delete,
isc_dyn_foreign_key_none,
isc_dyn_fld_name, <length,length>, <for_fld_name1>,
isc_dyn_fld_name, <length,length>, <for_fld_name2>,
 . . .
isc_dyn_fld_name, <length,length>, <for_fld_nameN>,
isc_dyn_idx_foreign_key, <length,length>, <prim_rel_name>,
isc_dyn_idx_ref_column, <length,length>, <prim_fld_name1>,
isc_dyn_idx_ref_column, <length,length>, <prim_fld_name2>,
 . . .
isc_dyn_idx_ref_column, <length,length>, <prim_fld_nameN>,
isc_dyn_end,
isc_dyn_end,

Example:

CREATE TABLE TPRIM (
TPRIM_F1 VARCHAR(20) NOT NULL,
TPRIM_F2 CHAR(10) NOT NULL,
TPRIM_F3 INTEGER,
PRIMARY KEY (TPRIM_F1, TPRIM_F2) );
CREATE TABLE TFOR (
TFOR_F1 VARCHAR(20) NOT NULL,
TFOR_F2 CHAR(10) NOT NULL,
TFOR_F3 INTEGER,
FOREIGN KEY (TFOR_F1, TFOR_F2) REFERENCES TPRIM(TPRIM_F1, TPRIM_F2)
ON DELETE NO ACTION ON UPDATE NO ACTION );

isc_dyn_rel_constraint, 0,0,
isc_dyn_def_foreign_key, 0,0,
isc_dyn_foreign_key_update,
isc_dyn_foreign_key_none,
isc_dyn_foreign_key_delete,
isc_dyn_foreign_key_none,
isc_dyn_fld_name, 7,0, 'T','F','O','R','_','F','1',
isc_dyn_fld_name, 7,0, 'T','F','O','R','_','F','2',
isc_dyn_idx_foreign_key, 5,0, 'T','P','R','I','M',
isc_dyn_idx_ref_column, 8,0, 'T','P','R','I','M','_','F','1',
isc_dyn_idx_ref_column, 8,0, 'T','P','R','I','M','_','F','2',
isc_dyn_end,
isc_dyn_end,

<referential triggered action> ::=ON DELETE CASCADE ON UPDATE CASCADE

Note: The corresponding foreign key updated to the new value of the primary key.

isc_dyn_rel_constraint, 0,0,
isc_dyn_def_foreign_key, 0,0,
isc_dyn_foreign_key_update,
isc_dyn_foreign_key_cascade,
isc_dyn_def_trigger, 0,0,
isc_dyn_trg_type, 2,0, 4,0,
isc_dyn_sql_object,
isc_dyn_trg_sequence, 2,0, 1,0,
isc_dyn_trg_inactive, 2,0, 0,0,
isc_dyn_rel_name, <length,length>, <prim_rel_name>,
isc_dyn_trg_blr, <length,length>,
blr_version4,
blr_if,
[ blr_or, ]
blr_neq,
blr_field, 0, <length>, <prim_fld_name1>,
blr_field, 1, <length>, <prim_fld_name1>,
[ blr_neq,
blr_field, 0, <length>, <prim_fld_name2>,
blr_field, 1, <length>, <prim_fld_name2>,
 . . .
blr_req,
blr_field, 0, <length>, <prim_fld_nameN>,
blr_field, 1, <length>, <prim_fld_nameN>, ]
blr_begin,
blr_begin,
blr_for,
blr_rse, 1,
blr_relation, <length>, <for_rel_name>, 2
blr_boolean,
[ blr_and, ]
blr_eql,
blr_field, 2, <length>, <for_fld_name1>,
blr_field, 0, <length>, <prim_fld_name1>,
[ blr_eql,
blr_field, 2, <length>, <for_fld_name2>,
blr_field, 0, <length>, <prim_fld_name2>,
 . . .
blr_eql,
blr_field, 2, <length>, <for_fld_nameN>,
blr_field, 0, <length>, <prim_fld_nameN>, ]
blr_end,
blr_modify, 2, 2,
blr_begin,
blr_assignment,
blr_field, 1, <length>, <prim_fld_name1>,
blr_field, 2, <length>, <for_fld_name1>,
[ blr_assignment,
blr_field, 1, <length>, <prim_fld_name2>,
blr_field, 2, <length>, <for_fld_name2>,
 . . .
blr_assignment,
blr_field, 1, <length>, <prim_fld_nameN>,
blr_field, 2, <length>, <for_fld_nameN>, ]
blr_end,
blr_end,
blr_end,
blr_end,
blr_eoc,
isc_dyn_end,
isc_dyn_foreign_key_delete,
isc_dyn_foreign_key_cascade,
isc_dyn_def_trigger, 0,0,
isc_dyn_trg_type, 2,0, 6,0,
isc_dyn_sql_object,
isc_dyn_trg_sequence, 2,0, 1,0,
isc_dyn_trg_inactive, 2,0, 0,0,
isc_dyn_rel_name, <length,length>, <prim_rel_name>,
isc_dyn_trg_blr, <length,length>,
blr_version4,
blr_for,
blr_rse, 1,
blr_relation, <length>, <for_rel_name>, 2,
blr_boolean,
[ blr_and, ]
blr_eql,
blr_field, 2, <length>, <for_fld_name1>,
blr_field, 0, <length>, <prim_fld_name1>,
[ blr_eql,
blr_field, 2, <length>, <for_fld_name2>,
blr_field, 0, <length>, <prim_fld_name2>,
 . . .
blr_eql,
blr_field, 2, <length>, <for_fld_nameN>,
blr_field, 0, <length>, <prim_fld_nameN>, ]
blr_end,
blr_erase, 2,
blr_eoc,
isc_dyn_end,
isc_dyn_fld_name, <length,length>, <for_fld_name1>,
isc_dyn_fld_name, <length,length>, <for_fld_name2>,
 . . .
isc_dyn_fld_name, <length,length>, <for_fld_nameN>,
isc_dyn_idx_foreign_key, <length,length>, <prim_rel_name>,
isc_dyn_idx_ref_column, <length,length>, <prim_fld_name1>,
isc_dyn_idx_ref_column, <length,length>, <prim_fld_name2>,
 . . .
isc_dyn_idx_ref_column, <length,length>, <prim_fld_nameN>,
isc_dyn_end,
isc_dyn_end,

Example:

CREATE TABLE TPRIM (
TPRIM_F1 VARCHAR(20) NOT NULL,
TPRIM_F2 CHAR(10) NOT NULL,
TPRIM_F3 INTEGER,
PRIMARY KEY (TPRIM_F1, TPRIM_F2) );
CREATE TABLE TFOR ( TFOR_F1 VARCHAR(20) NOT NULL,
TFOR_F2 CHAR(1) NOT NULL,
TFOR_F3 INTEGER
FOREIGN KEY (TFOR_F1, TFOR_F2) REFERENCES TPRIM(TPRIM_F1, TPRIM_F2)ON DELETE CASCADE ON UPDATE CASCADE);

isc_dyn_rel_constraint, 0,0,
isc_dyn_def_foreign_key, 0,0,
isc_dyn_foreign_key_update,
isc_dyn_foreign_key_cascade,
isc_dyn_def_trigger, 0,0,
isc_dyn_trg_type, 2,0, 4,0,
isc_dyn_sql_object,
isc_dyn_trg_sequence, 2,0, 1,0,
isc_dyn_trg_inactive, 2,0, 0,0,
isc_dyn_rel_name, 5,0, 'T','P','R','I','M',
isc_dyn_trg_blr, 161,0,
blr_version4,
blr_if,
blr_or,
blr_neq,
blr_field, 0, 8, 'T','P','R','I','M','_','F','1',
blr_field, 1, 8, 'T','P','R','I','M','_','F','1',
blr_neq,
blr_field, 0, 8, 'T','P','R','I','M','_','F','2',
blr_field, 1, 8, 'T','P','R','I','M','_','F','2',
blr_begin,
blr_begin,
blr_for,
blr_rse, 1,
blr_relation, 4, 'T','F','O','R', 2,
blr_boolean,
blr_and,
blr_eql,
blr_field, 2, 7, 'T','F','O','R','_','F','1',
blr_field, 0, 8, 'T','P','R','I','M','_','F','1',
blr_eql,
blr_field, 2, 7, 'T','F','O','R','_','F','2',
blr_field, 0, 8, 'T','P','R','I','M','_','F','2',
blr_end,
blr_modify, 2, 2,
blr_begin,
blr_assignment,
blr_field, 1, 8, 'T','P','R','I','M','_','F','1',
blr_field, 2, 7, 'T','F','O','R','_','F','1',
blr_assignment,
blr_field, 1, 8, 'T','P','R','I','M','_','F','2',
blr_field, 2, 7, 'T','F','O','R','_','F','2',
blr_end,
blr_end,
blr_end,
blr_end,
blr_eoc,
isc_dyn_end,
isc_dyn_foreign_key_delete,
isc_dyn_foreign_key_cascade,
isc_dyn_def_trigger, 0,0,
isc_dyn_trg_type, 2,0, 6,0,
isc_dyn_sql_object,
isc_dyn_trg_sequence, 2,0, 1,0,
isc_dyn_trg_inactive, 2,0, 0,0,
isc_dyn_rel_name, 5,0, 'T','P','R','I','M',
isc_dyn_trg_blr, 61,0,
blr_version4,
blr_for,
blr_rse, 1,
blr_relation, 4, 'T','F','O','R', 2,
blr_boolean,
blr_and,
blr_eql,
blr_field, 2, 7, 'T','F','O','R','_','F','1',
blr_field, 0, 8, 'T','P','R','I','M','_','F','1',
blr_eql,
blr_field, 2, 7, 'T','F','O','R','_','F','2',
blr_field, 0, 8, 'T','P','R','I','M','_','F','2',
blr_end,
blr_erase, 2,
blr_eoc,
isc_dyn_end, 
isc_dyn_fld_name, 7,0, 'T','F','O','R','_','F','1',
isc_dyn_fld_name, 7,0, 'T','F','O','R','_','F','2',
isc_dyn_idx_foreign_key, 5,0, 'T','P','R','I','M',
isc_dyn_idx_ref_column, 8,0, 'T','P','R','I','M','_','F','1',
isc_dyn_idx_ref_column, 8,0, 'T','P','R','I','M','_','F','2',
isc_dyn_end,
isc_dyn_end,

<referential triggered action> ::=ON DELETE SET NULL ON UPDATE SET NULL

Note: The corresponding foreign key is set to NULL. This may cause the primary key update/delete to fail due to referential integrity checks.

isc_dyn_rel_constraint, 0,0,
isc_dyn_def_foreign_key, 0,0,
isc_dyn_foreign_key_update,
isc_dyn_foreign_key_null,
isc_dyn_def_trigger, 0,0,
isc_dyn_trg_type, 2,0, 4,0,
isc_dyn_sql_object,
isc_dyn_trg_sequence, 2,0, 1,0,
isc_dyn_trg_inactive, 2,0, 0,0,
isc_dyn_rel_name, <length,length>, <prim_rel_name>,
isc_dyn_trg_blr, <length,length>,
blr_version4,
blr_if,
[ blr_or, ]
blr_neq,
blr_field, 0, <length>, <prim_fld_name1>,
blr_field, 1, <length>, <prim_fld_name1>,
[ blr_neq,
blr_field, 0, <length>, <prim_fld_name2>,
blr_field, 1, <length>, <prim_fld_name2>,
 . . .
blr_neq,
blr_field, 0, <length>, <prim_fld_nameN>,
blr_field, 1, <length>, <prim_fld_nameN>, ]
blr_begin,
blr_begin,
blr_for,
blr_rse, 1,
blr_relation, <length>, <for_rel_name>, 2,
blr_boolean,
[ blr_and, ]
blr_eql,
blr_field, 2, <length>, <for_fld_name1>,
blr_field, 0, <length>, <prim_fld_name1>,
[ blr_eql,
blr_field, 2, <length>, <for_fld_name2>,
blr_field, 0, <length>, <prim_fld_name2>,
 . . .
blr_eql,
blr_field, 2, <length>, <for_fld_nameN>,
blr_field, 0, <length>, <prim_fld_nameN>, ]
blr_end,
blr_modify, 2, 2,
blr_begin,
blr_assignment,
blr_null,
blr_field, 2, <length>, <for_fld_name1>,
[ blr_assignment,
blr_null,
blr_field, 2, <length>, <for_fld_name2>,
 . . .
blr_assignment,
blr_null,
blr_field, 2, <length>, <for_fld_nameN>, ]
blr_end,
blr_end,
blr_end,
blr_end,
blr_eoc,
isc_dyn_end,
isc_dyn_foreign_key_delete,
isc_dyn_foreign_key_null,
isc_dyn_def_trigger, 0,0,
isc_dyn_trg_type, 2,0, 6,0,
isc_dyn_sql_object,
isc_dyn_trg_sequence, 2,0, 1,0,
isc_dyn_trg_inactive, 2,0, 0,0,
isc_dyn_rel_name, <length,length>, <prim_rel_name>,
isc_dyn_trg_blr, <length,length>,
blr_version4,
blr_for,
blr_rse, 1,
blr_relation, <length>, <for_rel_name>, 2,
blr_boolean,
[ blr_and, ]
blr_eql,
blr_field, 2, <length>, <for_fld_name1>,
blr_field, 0, <length>, <prim_fld_name1>,
[ blr_eql,
blr_field, 2, <length>, <for_fld_name2>,
blr_field, 0, <length>, <prim_fld_name2>,
 . . .
blr_eql,
blr_field, 2, <length>, <for_fld_nameN>,
blr_field, 0, <length>, <prim_fld_nameN>, ]
blr_end,
blr_modify, 2, 2,
blr_begin,
blr_assignment,
blr_null,
blr_field, 2, <length>, <for_fld_name1>,
[ blr_assignment,
blr_null,
blr_field, 2, <length>, <for_fld_name2>,
 . . .
blr_assignment,
blr_null,
blr_field, 2, <length>, <for_fld_nameN>, ]
blr_end,
blr_eoc,
isc_dyn_end, ]
isc_dyn_fld_name, <length,length>, <for_fld_name1>,
isc_dyn_fld_name, <length,length>, <for_fld_name2>,
 . . .
isc_dyn_fld_name, <length,length>, <for_fld_nameN>,
isc_dyn_idx_foreign_key, <length,length>, <prim_rel_anme>,
isc_dyn_idx_ref_column, <length,length>, <prim_fld_name1>,
isc_dyn_idx_ref_column, <length,length>, <prim_fld_name2>,
 . . .
isc_dyn_idx_ref_column, <length,length>, <prim_fld_nameN>,
isc_dyn_end,
isc_dyn_end,

Example:

CREATE TABLE TPRIM (
TPRIM_F1 VARCHAR(20) NOT NULL,
TPRIM_F2 CHAR(10) NOT NULL,
TPRIM_F3 INTEGER,
PRIMARY KEY (TPRIM_F1, TPRIM_F2) );
CREATE TABLE TFOR ( TFOR_F1 VARCHAR(20) NOT NULL,
TFOR_F2 CHAR(10) NOT NULL,
TFOR_F3 INTEGER,
FOREIGN KEY (TFOR_F1, TFOR_F2) REFERENCES TPRIM(TPRIM_F1, TPRIM_F2)
ON DELETE SET NULL ON UPDATE SET NULL);

isc_dyn_rel_constraint, 0,0,
isc_dyn_def_foreign_key, 0,0,
isc_dyn_foreign_key_update,
isc_dyn_foreign_key_null,
isc_dyn_def_trigger, 0,0,
isc_dyn_trg_type, 2,0, 4,0,
isc_dyn_sql_object,
isc_dyn_trg_sequence, 2,0, 1,0,
isc_dyn_trg_inactive, 2,0, 0,0,
isc_dyn_rel_name, 5,0, 'T','P','R','I','M',
isc_dyn_trg_blr, 141,0,
blr_version4,
blr_if,
blr_or,
blr_neq,
blr_field, 0, 8, 'T','P','R','I','M','_','F','1',
blr_field, 1, 8, 'T','P','R','I','M','_','F','1',
blr_neq,
blr_field, 0, 8, 'T','P','R','I','M','_','F','2',
blr_field, 1, 8, 'T','P','R','I','M','_','F','2',
blr_begin,
blr_begin,
blr_for,
blr_rse, 1,
blr_relation, 4, 'T','F','O','R', 2,
blr_boolean,
blr_and,
blr_eql,
blr_field, 2, 7, 'T','F','O','R','_','F','1',
blr_field, 0, 8, 'T','P','R','I','M','_','F','1',
blr_eql,
blr_field, 2, 7, 'T','F','O','R','_','F','2',
blr_field, 0, 8, 'T','P','R','I','M','_','F','2',
blr_end,
blr_modify, 2, 2,
blr_begin,
blr_assignment,
blr_null,
blr_field, 2, 7, 'T','F','O','R','_','F','1',
blr_assignment,
blr_null,
blr_field, 2, 7, 'T','F','O','R','_','F','2',
blr_end,
blr_end,
blr_end,
blr_end,
blr_eoc,
isc_dyn_end,
isc_dyn_foreign_key_delete,
isc_dyn_foreign_key_null,
isc_dyn_def_trigger, 0,0,
isc_dyn_trg_type, 2,0, 6,0,
isc_dyn_sql_object,
isc_dyn_trg_sequence, 2,0, 1,0,
isc_dyn_trg_inactive, 2,0, 0,0,
isc_dyn_rel_name, 5,0, 'T','P','R','I','M',
isc_dyn_trg_blr, 88,0,
blr_version4,
blr_for,
blr_rse, 1,
blr_relation, 4, 'T','F','O','R', 2,
blr_boolean,
blr_and,
blr_eql,
blr_field, 2, 7, 'T','F','O','R','_','F','1',
blr_field, 0, 8, 'T','P','R','I','M','_','F','1',
blr_eql,
blr_field, 2, 7, 'T','F','O','R','_','F','2',
blr_field, 0, 8, 'T','P','R','I','M','_','F','2',
blr_end,
blr_modify, 2, 2,
blr_begin,
blr_assignment,
blr_null,
blr_field, 2, 7, 'T','F','O','R','_','F','1',
blr_assignment,
blr_null,
blr_field, 2, 7, 'T','F','O','R','_','F','2',
blr_end,
blr_eoc,
isc_dyn_end,
isc_dyn_fld_name, 7,0, 'T','F','O','R','_','F','1',
isc_dyn_fld_name, 7,0, 'T','F','O','R','_','F','2',
isc_dyn_idx_foreign_key, 5,0, 'T','P','R','I','M',
isc_dyn_idx_ref_column, 8,0, 'T','P','R','I','M','_','F','1',
isc_dyn_idx_ref_column, 8,0, 'T','P','R','I','M','_','F','2',
isc_dyn_end,
isc_dyn_end,

<referential triggered action> ::=ON DELETE SET DEFAULT ON UPDATE SET DEFAULT

Note: Every column of the corresponding foreign key is set to its default value. The default value is the one in effect when the RI constraint is defined. This may cause the primary key update/delete to fail due to referential integrity checks.

isc_dyn_rel_constraint, 0,0,
isc_dyn_def_foreign_key, 0,0,
isc_dyn_foreign_key_update,
isc_dyn_foreign_key_default,
isc_dyn_def_trigger, 0,0,
isc_dyn_trg_type, 2,0, 4,0,
isc_dyn_sql_object,
isc_dyn_trg_sequence, 2,0, 1,0,
isc_dyn_trg_inactive, 2,0, 0,0,
isc_dyn_rel_name, <length,length>, <prim_rel_name>,
isc_dyn_trg_blr, <length,length>,
blr_version4,
blr_if,
[ blr_or, ]
blr_neq,
blr_field, 0, <length>, <prim_fld_name1>,
blr_field, 1, <length>, <prim_fld_name1>,
[ blr_neq,
blr_field, 0, <length>, <prim_fld_name2>,
blr_field, 1, <length>, <prim_fld_name2>,
 . . .
blr_neq,
blr_field, 0, <length>, <prim_fld_nameN>,
blr_field, 1, <length>, <prim_fld_nameN>, ]
blr_begin,
blr_begin,
blr_for,
blr_rse, 1,
blr_relation, <length>, <for_rel_name>, 2,
blr_boolean,
[ blr_and, ]
blr_eql,
blr_field, 2, <length>, <for_fld_name1>,
blr_field, 0, <length>, <prim_fld_name1>,
[ blr_eql,
blr_field, 2, <length>, <for_fld_name2>,
blr_field, 0, <length>, <prim_fld_name2>,
 . . .
blr_eql,
blr_field, 2, <length>, <for_fld_nameN>,
blr_field, 0, <length>, <prim_fld_nameN>, ]
blr_end,
blr_modify, 2, 2,
blr_begin,
blr_assignment,
{ blr_null, | blr_literal, <blr descriptor>, <default value>, }
blr_field, 2, <length>, <for_fld_name1>,
[ blr_assignment,
{ blr_null, | blr_literal, <blr descriptor>, <default value>, }
blr_field, 2, <length>, <for_fld_name2>,
 . . .
blr_assignment,
{ blr_null, | blr_literal, <blr descriptor>, <default value>, }
blr_field, 2, <length>, <for_fld_nameN>,
blr_end,
blr_end,
blr_end,
blr_end,
blr_eoc,
isc_dyn_end,
isc_dyn_foreign_key_delete,
isc_dyn_foreign_key_default,
isc_dyn_def_trigger, 0,0,
isc_dyn_trg_type, 2,0, 6,0,
isc_dyn_sql_object,
isc_dyn_trg_sequence, 2,0, 1,0,
isc_dyn_trg_inactive, 2,0, 0,0,
isc_dyn_rel_name, <length>, <prim_rel_name>,
isc_dyn_trg_blr, <length,length>,
blr_version4,
blr_for,
blr_rse, 1,
blr_relation, <length>, <for_rel_name>, 2,
blr_boolean,
[ blr_and, ]
blr_eql,
blr_field, 2, <length>, <for_fld_name1>,
blr_field, 0, <length>, <prim_fld_name1>,
[ blr_eql,
blr_field, 2, <length>, <for_fld_name2>,
blr_field, 0, <length>, <prim_fld_name2>,
 . . .
blr_eql,
blr_field, 2, <length>, <for_fld_nameN>,
blr_field, 0, <length>, <prim_fld_nameN>, ]
blr_end,
blr_modify, 2, 2,
blr_begin,
blr_assignment,
{ blr_null, | blr_literal, <blr descriptor>, <default value>, }
blr_field, 2, <length>, <for_fld_name1>,
[ blr_assignment,
{ blr_null, | blr_literal, <blr descriptor>, <default value>, }
blr_field, 2, <length>, <for_fld_name2>,
 . . .
blr_assignment,
{ blr_null, | blr_literal, <blr descriptor>, <default value>, }
blr_field, 2, <length>, <for_fld_nameN>,
blr_end,
blr_eoc,
isc_dyn_end, ] 
isc_dyn_fld_name, <length,length>, <for_fld_name1>,
isc_dyn_fld_name, <length,length>, <for_fld_name2>,
 . . .
isc_dyn_fld_name, <length,length>, <for_fld_nameN>,
isc_dyn_idx_foreign_key, <length,length>, <prim_rel_name>,
isc_dyn_idx_ref_column, <length,length>, <prim_fld_name1>,
isc_dyn_idx_ref_column, <length,length>, <prim_fld_name2>,
 . . .
isc_dyn_idx_ref_column, <length,length>, <prim_fld_nameN>,
isc_dyn_end,
isc_dyn_end,

Example:

CREATE TABLE PRIM_TABLE ( PRIM_COL_1 INT NOT NULL,
PRIM_COL_2 CHAR(8) NOT NULL,
PRIMARY KEY (PRIM_COL_1, PRIM_COL_2));
CREATE DOMAIN MY_INT AS INT DEFAULT 100;
CREATE DOMAIN MY_CHAR AS CHAR(8) DEFAULT "hundred";

CREATE TABLE FOR_TABLE ( FOR_COL_1 MY_INT,
FOR_COL_2 MY_CHAR,
FOREIGN KEY (FOR_COL_1, FOR_COL_2) REFERENCES
PRIM_TABLE (PRIM_COL_1, PRIM_COL_2)
ON UPDATE SET DEFAULT ON DELETE SET DEFAULT);

isc_dyn_rel_constraint, 0,0,
isc_dyn_def_foreign_key, 0,0,
isc_dyn_foreign_key_update,
isc_dyn_foreign_key_default,
isc_dyn_def_trigger, 0,0,
isc_dyn_trg_type, 2,0, 4,0,
isc_dyn_sql_object,
isc_dyn_trg_sequence, 2,0, 1,0,
isc_dyn_trg_inactive, 2,0, 0,0,
isc_dyn_rel_name, 10,0, 'P','R','I','M','_','T','A','B','L','E',
isc_dyn_trg_blr, 184,0,
blr_version4,
blr_if,
blr_or,
blr_neq,
blr_field, 0, 10, 'P','R','I','M','_','C','O','L','_','1',
blr_field, 1, 10, 'P','R','I','M','_','C','O','L','_','1',
blr_neq,
blr_field, 0, 10, 'P','R','I','M','_','C','O','L','_','2',
blr_field, 1, 10, 'P','R','I','M','_','C','O','L','_','2',
blr_begin,
blr_begin,
blr_for,
blr_rse, 1,
blr_relation, 9, 'F','O','R','_','T','A','B','L','E', 2,
blr_boolean,
blr_and,
blr_eql,
blr_field, 2, 9, 'F','O','R','_','C','O','L','_','1',
blr_field, 0, 10, 'P','R','I','M','_','C','O','L','_','1',
blr_eql,
blr_field, 2, 9, 'F','O','R','_','C','O','L','_','2',
blr_field, 0, 10, 'P','R','I','M','_','C','O','L','_','2',
blr_end,
blr_modify, 2, 2,
blr_begin,
blr_assignment,
blr_literal, blr_long, 0, 'd',0,0,0,
blr_field, 2, 9, 'F','O','R','_','C','O','L','_','1',
blr_assignment,
blr_literal, blr_text2, 127,0, 7,0, 'h','u','n','d','r','e','d',
blr_field, 2, 9, 'F','O','R','_','C','O','L','_','2',
blr_end,
blr_end,
blr_end,
blr_end,
blr_eoc,
isc_dyn_end,
isc_dyn_foreign_key_delete,
isc_dyn_foreign_key_default,
isc_dyn_def_trigger, 0,0,
isc_dyn_trg_type, 2,0, 6,0,
isc_dyn_sql_object,
isc_dyn_trg_sequence, 2,0, 1,0,
isc_dyn_trg_inactive, 2,0, 0,0,
isc_dyn_rel_name, 10,0, 'P','R','I','M','_','T','A','B','L','E',
isc_dyn_trg_blr, 123,0,
blr_version4,
blr_for,
blr_rse, 1,
blr_relation, 9, 'F','O','R','_','T','A','B','L','E', 2,
blr_boolean,
blr_and,
blr_eql,
blr_field, 2, 9, 'F','O','R','_','C','O','L','_','1',
blr_field, 0, 10, 'P','R','I','M','_','C','O','L','_','1',
blr_eql,
blr_field, 2, 9, 'F','O','R','_','C','O','L','_','2',
blr_field, 0, 10, 'P','R','I','M','_','C','O','L','_','2',
blr_end,
blr_modify, 2, 2,
blr_begin,
blr_assignment,
blr_literal, blr_long, 0, 'd',0,0,0,
blr_field, 2, 9, 'F','O','R','_','C','O','L','_','1',
blr_assignment,
blr_literal, blr_text2, 127,0, 7,0, 'h','u','n','d','r','e','d',
blr_field, 2, 9, 'F','O','R','_','C','O','L','_','2',
blr_end,
blr_eoc,
isc_dyn_end,
isc_dyn_fld_name, 9,0, 'F','O','R','_','C','O','L','_','1',
isc_dyn_fld_name, 9,0, 'F','O','R','_','C','O','L','_','2',
isc_dyn_idx_foreign_key, 10,0, 'P','R','I','M','_','T','A','B','L','E',
isc_dyn_idx_ref_column, 10,0, 'P','R','I','M','_','C','O','L','_','1',
isc_dyn_idx_ref_column, 10,0, 'P','R','I','M','_','C','O','L','_','2',
isc_dyn_end,
isc_dyn_end,

Implementation

Affected modules:
Component: gpre
Files: words.h, hsh.h, gpre.h, dyntable.h, met_proto.h, sql.c, cmd.c, pretty.c, met.e

Testing:
The following features shall be tested:

Syntax for the extended foreign key definition
New blr structures for all combinations of ON UPDATE/ON DELETE and NO ACTION/CASCADE/SET NULL/SET DEFAULT.
Foreign key behaviour for all combinations of ON UPDATE/ON DELETE and NO ACTION/CASCADE/SET NULL/SET DEFAULT.

Migration:
None, since compatibility with the previous version of GPRE is maintained, no change to existing code is required.

Additional Comments:
Compliance with the SQL ‘92 standard is enforced.

Sign In

Section Navigation

Brief Description

Purpose

Feature Description

Functional Changes

Implementation