U0000002: GaussDB Distributed does not support foreign key constraints

Description

Database Type and Version

• Source database type and version: all types of the source database storing tables with foreign key constraints
• Target database type and version: GaussDB Distributed

Syntax Example

This error is reported because UGO does not convert foreign key constraints by default.

GaussDB Distributed does not support foreign key constraints, for example:

CREATE TABLE ref_types (
    dbtype VARCHAR(100) PRIMARY KEY,
    root_level_type VARCHAR(100)
);
CREATE TABLE dbtype_objtype_map (
    dbtype VARCHAR(100),
    object_type VARCHAR(100),
    is_supported INT,
    CONSTRAINT dbtype_objtype_map_fk_1 FOREIGN KEY (dbtype) REFERENCES ref_types(dbtype) ON DELETE CASCADE ON UPDATE RESTRICT
);

Suggestion

Solution 1: Modify Support for foreign key.

Solution 2: Use a trigger to enforce foreign key constraints (only data dependency constraints, not CASCADE DELETE), for example:

CREATE TABLE dbtype_objtype_map (
    dbtype VARCHAR(100),
    object_type VARCHAR(100),
    is_supported INT
);
CREATE OR REPLACE FUNCTION dbtype_objtype_map_foreign_func() RETURN TRIGGER AS
DECLARE
       reference_count INT;
BEGIN
       SELECT count(dbtype) INTO reference_count FROM ref_types WHERE dbtype = NEW.dbtype;
       IF reference_count > 0 THEN
              RETURN NEW;
       ELSE
              RAISE EXCEPTION 'Cannot add or update a child row: a foreign key constraint fails ';
       END IF;
END;
CREATE TRIGGER dbtype_objtype_map_foreign_tri
BEFORE UPDATE OR INSERT ON dbtype_objtype_map
FOR EACH ROW
EXECUTE PROCEDURE dbtype_objtype_map_foreign_func();

The trigger cannot replace foreign key constraints, or performance may be affected and maintaining database systems will become more difficult. You are advised to comment out foreign keys to ensure data integrity.