U0000004: GaussDB foreign key referenced columns must be a primary or unique key

Description

Database Type and Version

• Source database type and version: all types of the source database storing tables with foreign keys
• Target database type and version: GaussDB Centralized

Syntax Example

This error is reported when UGO identifies that a column referenced by foreign keys is not a primary or unique key.

The column referenced by foreign keys in a table of a centralized GaussDB database must be a unique or primary key. For example, the following syntax is not supported in GaussDB Centralized:

CREATE TABLE demo1 (
    id INT,
    demo1_col VARCHAR(64)
);
CREATE TABLE demo2 (
    id INT,
    demo2_col VARCHAR(64),
    CONSTRAINT demo2_fk FOREIGN KEY (id) REFERENCES demo1 (id)
);

Suggestion

CREATE TABLE demo2 (
    id INT,
    demo2_col VARCHAR(64)/*,
    CONSTRAINT demo2_fk FOREIGN KEY (id) REFERENCES demo1 (id)*/
);

In this case, you need to use application code to enable the id column in the demo2 table to reference the id column in the demo1 table.

Solution 2: Change the column referenced the foreign keys to a primary or unique key.

ALTER TABLE demo1 ADD UNIQUE(id);

In this case, the data integrity constraint of the demo1 table will be modified. You need to evaluate the impact.