如何为表字段添加约束和外键 执行SQL出现constraint语法错误

错误原因 #

• 声明fk_grid_cell字段时没有指定字段类型，直接紧跟constraint关键字，不符合SQL语法规范
• grid_cell表的主键是由fourCornersTreatmentAsGeoJSON、fourCornersBufferAsGeoJSON两个字段组成的联合主键，外键关联联合主键时，必须对应两个同类型的字段，不能绑定在单个字段上。

修正方案 #

方案1：保留原父表的联合主键结构 #

你需要先在grid_cell_particular表中定义两个和父表主键字段类型一致的字段，再单独声明唯一约束和外键约束，修正后的SQL如下：

create table if not exists grid_cell(
    fourCornersTreatmentAsGeoJSON text,
    fourCornersBufferAsGeoJSON text,
    primary key (fourCornersTreatmentAsGeoJSON,fourCornersBufferAsGeoJSON)
);

CREATE TABLE IF NOT EXISTS grid_cell_particular (
    gridCellParticularPK serial primary key,
    isTreatment boolean,
    isBuffer boolean,
    distanceFromTreatmentToNearestEdge float8,
    distanceFromBufferToNearestEdge float8,
    -- 定义和父表主键同类型的两个外键关联字段
    fk_fourCornersTreatmentAsGeoJSON text,
    fk_fourCornersBufferAsGeoJSON text,
    -- 声明唯一约束
    CONSTRAINT constrains_FK_gridCell_gridCellParticular 
        UNIQUE(fk_fourCornersTreatmentAsGeoJSON, fk_fourCornersBufferAsGeoJSON),
    -- 声明外键约束
    CONSTRAINT fk_grid_cell FOREIGN KEY (fk_fourCornersTreatmentAsGeoJSON, fk_fourCornersBufferAsGeoJSON) 
        REFERENCES grid_cell(fourCornersTreatmentAsGeoJSON,fourCornersBufferAsGeoJSON)
);

方案2：调整父表为单字段主键，适配单个外键字段需求 #

如果你希望用单个fk_grid_cell字段做外键，可以调整父表结构新增单字段主键，示例如下：

-- 调整父表结构，新增单字段主键
create table if not exists grid_cell(
    grid_cell_id serial primary key,
    fourCornersTreatmentAsGeoJSON text,
    fourCornersBufferAsGeoJSON text,
    -- 原来的联合字段加唯一约束保证数据唯一性
    UNIQUE(fourCornersTreatmentAsGeoJSON,fourCornersBufferAsGeoJSON)
);

CREATE TABLE IF NOT EXISTS grid_cell_particular (
    gridCellParticularPK serial primary key,
    isTreatment boolean,
    isBuffer boolean,
    distanceFromTreatmentToNearestEdge float8,
    distanceFromBufferToNearestEdge float8,
    -- 单个外键字段
    fk_grid_cell int unique,
    -- 外键约束
    CONSTRAINT constrains_FK_gridCell_gridCellParticular 
        FOREIGN KEY (fk_grid_cell) REFERENCES grid_cell(grid_cell_id)
);

内容的提问来源于stack exchange，提问作者Amrmsmb