我有以下張貼的表格。我想fk_grid_cell在gridCellParticularPK表中呼叫的列上指定約束和外鍵。下面是我的嘗試，但是當我執行它時，它給出了一個語法錯誤
我收到的錯誤是：
syntax error at `constraint`
表：
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_grid_cell
constraint constrains_FK_gridCell_gridCellParticular
unique foreign key references grid_cell(fourCornersTreatmentAsGeoJSON,fourCornersBufferAsGeoJSON)
)
uj5u.com熱心網友回復：
缺少一些東西：
- 您用于主鍵的列
grid_cell在表中不存在：fourCornersOfKeyWindowRepresentativeToTreatmentAsGeoJSON和fourCornersOfKeyWindowRepresentativeToBufferAsGeoJSON。 - 上的列名
grid_cell表明您正在存盤 json 字串，因此您應該使用資料型別jsonb而不是text. - 在約束上宣告的外鍵列
grid_cell_particular也不存在于表本身。為了創建外鍵，您必須將外列映射到表中的現有列。您可以隨意命名它們，例如fk_grid_cell_treatment和fk_grid_cell_buffer。
無關：
考慮使用 INDENTITY 列而不是serial. 看一下這個：Don't use serial
CREATE TABLE IF NOT EXISTS grid_cell(
fourCornersTreatmentAsGeoJSON jsonb,
fourCornersBufferAsGeoJSON jsonb,
fourCornersOfKeyWindowRepresentativeToTreatmentAsGeoJSON jsonb,
fourCornersOfKeyWindowRepresentativeToBufferAsGeoJSON jsonb,
PRIMARY KEY (fourCornersOfKeyWindowRepresentativeToTreatmentAsGeoJSON,
fourCornersOfKeyWindowRepresentativeToBufferAsGeoJSON)
);
CREATE TABLE IF NOT EXISTS grid_cell_particular (
gridCellParticularPK serial PRIMARY KEY,
isTreatment boolean,
isBuffer boolean,
distanceFromTreatmentToNearestEdge float8,
distanceFromBufferToNearestEdge float8,
fk_grid_cell_treatment jsonb,
fk_grid_cell_buffer jsonb,
CONSTRAINT constrains_FK_gridCell_gridCellParticular
FOREIGN KEY (fk_grid_cell_treatment,fk_grid_cell_buffer)
REFERENCES grid_cell
(fourCornersOfKeyWindowRepresentativeToTreatmentAsGeoJSON,
fourCornersOfKeyWindowRepresentativeToBufferAsGeoJSON),
CONSTRAINT unique_fk
UNIQUE (fk_grid_cell_treatment,fk_grid_cell_buffer)
);
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/366104.html
標籤：sql PostgreSQL的
上一篇：PostgreSQL-按兩列分組并使用一列作為結果列
下一篇：查詢帳戶掛起正好2天-SQL