Postgresql trigger to maintain coloumn uniqueness

[yockee]

I am writing a trigger in Postgresql database so that one particular coloumn can maintain its uniqueness.

The idea is to check the data before they are entered. The data are entered via Field Map.

So, I write a Function below :

CREATE OR REPLACE FUNCTION xxx_check_unique()
RETURNS TRIGGER
AS
$$
BEGIN
 IF (SELECT EXISTS(SELECT FROM scema1.chamber where id = new.id)) = 'false' THEN
   RETURN new;   //entry the new record
   else
   RAISE EXCEPTION SQLSTATE '90001' USING MESSAGE = 'DUP';
   return null;   //do not entry due to duplication
 end if;
END;

CREATE OR REPLACE TRIGGER xx_check_unique
BEFORE update of id ON scema1.chamber
FOR EACH ROW EXECUTE PROCEDURE xxx_check_unique();

CREATE OR REPLACE TRIGGER xx_check_unique
BEFORE insert ON scema1.chamber
FOR EACH ROW EXECUTE PROCEDURE xxx_check_unique();

That trigger works using Field Map if I enter an ID that's never been entered before.

If the ID entered has existed in the record, then an error comes up :

Then, if I want to entry another data, *i have to repeat the process from the very beginning, with all empty coloumns*. I f I just change the ID into another unique ID, the same error message comes up just like the picture above.

So, how can I employ such a trigger.

FYI, this trigger works in Desktop without error.

Thanks

[CodyPatterson]

Hey @yockee 

Are these IDs bound by any certain restriction? Be it like a GUID  or similar? When a feature is inserted, you could have a calculated field on Field Maps create a GUID using the GUID() function, or something like  

return count($layer) + 1;

That would return a much different ID but still something unique, what are your restrictions currently?

Cody

[yockee]

Hi @CodyPatterson ,

The only restriction is that it has to be unique. This ID is based on another layer underneath it (the ID of the layer underneath is already unique) and the data have to be entered manually. I have to run this mechanism of entering it manually because I have to do so : the layer underneath is a polyline, whereas the one that I am working is a point layer. One of the points inside a polyline must have the ID of that polyline.

The trigger above somehow actually works in Field Map not in a way it is intended. If I enter an ID that is already existed and filled in all other text boxes (there are around 12 questionaires), press SUBMIT, and then that error (that error in the pic above) comes up. After the error, I just *can not simply change* the ID to another unique  value and then press SUBMIT again. The same error comes up.

But, if I do the entry from the beginning (with all 12 text boxes emptied out), and put in a unique ID, it will work without error. This is pains as I have to fill in all the text boxes again.

I have overcome this problem of re-entering-data-from-beginning by employing Attribute Validation, set in Arcgis Pro, below. But this method is rather process heavy because all data are copied into Datastore. If only a few records this method will work fine.

(PS: I am curious, where does this validation is stored ? i presume in Arcgis Server )

:::
if (id == null || id == "") {
return true;
}

var fs = FeatureSetByName(
$datastore,
"db_city.scema1.data1",
["oid_code ", "objectid"],
false
);

var oid_self = $feature.objectid;

var others = Filter(fs, "oid_code = @id AND objectid <> @oid_self");

if (Count(others) > 0) {
return {
"errorMessage": "id already existed"
};
}