Postgresql unique constraint for colums of multiple related tables

Question

I have the following tables:

server (server_id as PK)

server_id |  server_name
----------+---------------
 3251623  |  ServerOfDoom
 5578921  |  BestServerEU

server_groups
Each server can have multiple server groups, every group name within a server must be unique.

 id | server_id |  group_name
----+-----------+---------------
  1 |  3251623  | mods
  2 |  5578921  | admins
  3 |  5578921  | anothergroup

group_tags
Each group can have multiple tags, a tag can be connected to multiple groups. Each tag name must be unique.

 id | tag_name
----+--------------
  1 | mods
  2 | admins
  3 | ag

group_tag_relations

 group_id | tag_id
----------+--------
        2 |      1
        3 |      3
        1 |      2

Problem:

I need group tag names and server group names to be unique within one server. So a tag can be connected to multiple groups, but only to one group per server. Or the tag "foo" cannot be connected to the group "bar" of server z because server z already has a group "foo". The same goes for the other way around, so if the group "foo" had the tag "hey" you shouldn't be able to add a group "hey" to the same server.

Basically names should not appear multiple times on a server, no matter if tag or group name.

How would I implement such a Constraint?

EDIT

So basically I'm trying to convert this JSON format into SQL tables:

{
    "5578921": {
        "Server Name": "Server1",
        ...
        "Groups": {
            "Game1": {
                "Tags": [
                    "g1",
                    "gameone"
                ], ...
            },
            "Game2": {
                "Tags": [
                    "g2",
                    "gametwo"
                ], ...
            }
        }
    },
    "3251623": ...
}

The id's 5578921 and 3251623 should just represent Discord server id's, so every id is for one server my bot is on. The Json file is all the information of my bot for each server, but it's not as reliable and scalable as a database, so I wanted to convert it.

So the upper tables are what I came up with:

A One-To-Many relation between server and server_groups and a Many-To-Many relation between server_groups and group_tags (so instead of storing duplicate tags I can just assign them to different groups).

I just want to make sure there are no duplicate names on one server and asking how to do so for my current tables.

What does "How would I query such a constraint?" mean? Are you asking how to implement such a constraint? — Gordon Linoff
– Gordon Linoff, Commented Nov 23, 2020 at 15:30
This is a mess. It would be better if you explained what you are trying to do with your categorization of servers and then we could work from that to a solution. — Adrian Klaver
– Adrian Klaver, Commented Nov 23, 2020 at 16:08
It's unclear to me from the JSON structure why a server can't have a group and a tag with the same name. "Groups": { "Game1": { "Tags": ["Game1"] } } seems fine. This doesn't seem like a structural constraint, but business constraint. It might make sense to enforce it with a trigger. — Schwern
– Schwern, Commented Nov 24, 2020 at 9:15
You can't implement these as a type of constraint on the table itself, so you should probably place the logical checks into a stored procedure. I suppose you could make it a trigger for INSERT or UPDATE against either table, but I imagine you'd want stored procedures already to encapsulate your behavior for your interface layer to call, so placing this logic there would be seamless. — CoffeeNeedCoffee
– CoffeeNeedCoffee, Commented Nov 25, 2020 at 17:05

Shell Code · Accepted Answer · 2020-11-27 15:18:28Z

As mentioned before, a stored procedure or function with triggers is probably the way to go.

The code could look something like this:

CREATE FUNCTION public."CHECK_TAG_AND_GROUP_NAME_UNIQUE_PER_SERVER"()
    RETURNS trigger
    LANGUAGE 'plpgsql'
     NOT LEAKPROOF
AS $BODY$
DECLARE
    countServerGroupsWithNamePerServer  integer;
    countGroupTagsWithNamePerServer     integer;
BEGIN

    -- Count occurrences of name in server_groups table
    SELECT COUNT(*)
    FROM server_groups
    INTO countServerGroupsWithNamePerServer
    WHERE "name" = NEW.name
        AND "server_id" = NEW.server_id;
        
    -- Check if one exists. If it does, throw error
    IF countServerGroupsWithNamePerServer > 0 THEN
        RAISE 'Name already exists as a group server name %', NEW.name;
    END IF;

    -- Count occurrences of name in group_tags table
    SELECT COUNT(*)
    FROM group_tags
    INTO countGroupTagsWithNamePerServer
    WHERE "name" = NEW.name
        AND "server_id" = NEW.server_id;

    -- Check if one exists. If it does, throw error
    IF countGroupTagsWithNamePerServer > 0 THEN
        RAISE 'Name already exists as a group_tag name %', NEW.name;
    END IF;

    -- If no error is thrown, insert the new record
    RETURN NEW;

END;
$BODY$;

and then you attach the function as a BEFORE INSERT trigger to each of the two tables group_tags and server_groups:

CREATE TRIGGER "BEFORE_INSERT_CHECK_TAG_NAME_UNIQUE_PER_SERVER"
    BEFORE INSERT
    ON public.group_tags
    FOR EACH ROW
    EXECUTE PROCEDURE public."CHECK_TAG_AND_GROUP_NAME_UNIQUE_PER_SERVER"();


CREATE TRIGGER "BEFORE_INSERT_CHECK_TAG_NAME_UNIQUE_PER_SERVER"
    BEFORE INSERT
    ON public.server_groups
    FOR EACH ROW
    EXECUTE PROCEDURE public."CHECK_TAG_AND_GROUP_NAME_UNIQUE_PER_SERVER"();

Please notice for this example I also added a foreign key column server_id to the group_tags table. Otherwise we are not sure to which server the tag belongs. This is just a rough guide though, please feel free to change it up as much as you want.

Thanks. I actually found out a bit since I asked my question, so I answered it mostly myself now, but the trigger part helped me a lot anyway :)

northmaybe · Accepted Answer · 2020-11-28 11:52:19Z

After hours of suffering I finally got what I wanted:

Get all Tags of a server

CREATE OR REPLACE FUNCTION get_server_tags(serverid BIGINT) 
RETURNS TABLE(group_name VARCHAR(100), tag_name VARCHAR(100), group_id BIGINT, tag_id BIGINT)
AS
$$
    SELECT group_name, tag_name, group_id, tag_id FROM group_tag_relations 
    JOIN server_groups 
    ON server_groups.server_id = serverid
    AND server_groups.id = group_tag_relations.group_id
    JOIN group_tags
    ON group_tags.id = group_tag_relations.tag_id
$$
language sql
stable;

Get all Groups of a server

CREATE OR REPLACE FUNCTION get_server_group(serverid BIGINT, groupname VARCHAR(100)) 
RETURNS TABLE(group_name VARCHAR(100), group_id BIGINT)
AS
$$
    SELECT group_name, id
    FROM server_groups 
    WHERE server_id = serverid 
    AND lower(group_name) = lower(groupname);
$$
language sql
stable;

Get a Group by Name (Calling both Functions above)

CREATE OR REPLACE FUNCTION get_group_by_name(serverid BIGINT, groupname VARCHAR(100))
RETURNS TABLE(group_name VARCHAR(100), group_id BIGINT)
AS
$$
BEGIN
    RETURN QUERY SELECT get_server_group.group_name, get_server_group.group_id
    FROM get_server_group(serverid, groupname);

    IF NOT found THEN 
        RETURN QUERY SELECT get_server_tags.group_name, get_server_tags.group_id
        FROM get_server_tags(serverid)
        WHERE lower(tag_name) = lower(groupname);
    END IF;
END;
$$
language plpgsql
stable;

Update Trigger for server_groups table, checking wether the name is already taken on a server

CREATE OR REPLACE FUNCTION group_name_update() 
RETURNS TRIGGER
AS
$$ 
BEGIN
    PERFORM get_group_by_name(OLD.server_id, NEW.group_name);
    
    IF lower(OLD.group_name) = lower(NEW.group_name) THEN
        RETURN NEW;
    ELSIF found THEN
        RETURN OLD;
    ELSE
        RETURN NEW;
    END IF;
END;
$$
language plpgsql
volatile;


CREATE TRIGGER group_name_update_trigger 
BEFORE UPDATE ON server_groups
FOR EACH ROW EXECUTE PROCEDURE group_name_update();

Insert Trigger for server_groups table, checking wether the name is already taken on a server

CREATE OR REPLACE FUNCTION group_name_insert() 
RETURNS TRIGGER
AS
$$ 
BEGIN
    PERFORM get_group_by_name(NEW.server_id, NEW.group_name);

    IF found THEN
        RETURN OLD;
    ELSE
        RETURN NEW;
    END IF;
END;
$$
language plpgsql
volatile;

CREATE TRIGGER group_name_insert_trigger 
BEFORE INSERT ON server_groups
FOR EACH ROW EXECUTE PROCEDURE group_name_insert();

Insert Trigger for group_tag_relation table, checking wether the tag is already taken on a server (as the tags wont get updated there's no need for an update trigger)

CREATE OR REPLACE FUNCTION group_tag_relation_insert()
RETURNS TRIGGER
AS
$$ 
BEGIN
    PERFORM get_group_by_name((SELECT server_id FROM server_groups WHERE id = NEW.group_id), (SELECT tag_name FROM group_tags WHERE id = tag_id));

    IF found THEN
        RETURN OLD;
    ELSE
        RETURN NEW;
    END IF;
END;
$$
language plpgsql
volatile;

CREATE TRIGGER group_tag_relation_insert_trigger 
BEFORE INSERT ON group_tag_relations
FOR EACH ROW EXECUTE PROCEDURE group_tag_relation_insert();

Collectives™ on Stack Overflow

Postgresql unique constraint for colums of multiple related tables

Problem:

EDIT

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Problem:

EDIT

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related