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