how to make a row unique in one to many relation-CodePudding

i have the below posted tables. the relation is one to many. single site could has different operations and single operation is to be applied on a single site at a time. therefore, the primary key of the table Site will be a foreign key in the table operation. but what i want to have is, the combination between selectedSiteID,treatmentGeometry,threshold,isVisualizeAreaOfCoverage,isVisualizeAverageHeights in table operation should be unique.in other words, in the table operation the values assigned to columns selectedSiteID,treatmentGeometry,threshold,isVisualizeAreaOfCoverage,isVisualizeAverageHeights should be unique and can not be duplicated. please let me know how can i achieve that.

tables:

create table if not exists Site(
selectedSiteID text primary key,
treatmentGeometry geometry,
);
create table if not exists operation(
               threshold smallint,
               isVisualizeAreaOfCoverage boolean,
               isVisualizeAverageHeights boolean,
               primary key (threshold,isVisualizeAreaOfCoverage,isVisualizeAverageHeights)
        );

CodePudding user response：

Since selectedSiteID is a primary on Site and a foreign key on operation you do not need to repeat treatmentGeometry on operation. You just the whole record of operation unique, just add the foreign key site_selectedsite_id to the primary key:

CREATE TABLE Site(
  selectedSiteID text PRIMARY KEY,
  treatmentGeometry geometry
);

CREATE TABLE operation(
  threshold smallint,
  isVisualizeAreaOfCoverage boolean,
  isVisualizeAverageHeights boolean,
  site_selectedsite_id text REFERENCES site(selectedSiteID), 
  PRIMARY KEY (site_selectedsite_id,
               threshold,
               isVisualizeAreaOfCoverage,
               isVisualizeAverageHeights)
);