i have the below posted tables. i would like to specify a constraint and foreign key on the column called fk_grid_cell in gridCellParticularPK table. below is my attempts, but when i execute it, it gives a syntay error

the error i am receiving is:

 syntax error at `constraint`

tables:

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) 
           )

CodePudding user response：

There are few things missing:

• The columns you're using for the primary key on grid_cell do not exist in the table: fourCornersOfKeyWindowRepresentativeToTreatmentAsGeoJSON and fourCornersOfKeyWindowRepresentativeToBufferAsGeoJSON.
• The column names on grid_cell suggest you're storing json strings, so you should be using the data type jsonb instead of text.
• The foreign key columns declared on the constraint on grid_cell_particular also do not exist on the table itself. In order to create a foreign key you must map the foreign columns to existing columns on your table. You can name them as you want, e.g. fk_grid_cell_treatment and fk_grid_cell_buffer.

Unrelated:

Consider using INDENTITY columns instead of serial. Check this out: 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)
 );