How can I define a type like Text [] and how make a foreign key?

Question

I regretfully learned that I cannot define a foreign key for the Text[] type. but I need to keep the ids of other tables in a list and I have to do this with a foreign key restriction. At least so far I have no other point of view.

Do you think there is a method where I can use the foreign key restriction in json or array?

CREATE TABLE category(
    category_id  INT GENERATED ALWAYS AS IDENTITY,
    category_name VARCHAR(210) NOT NULL,
    category_description TEXT,
    constraint pk_category primary key (category_id),
    constraint u_category_name unique (category_name)
);


CREATE TABLE checkpoint_item(
    checkpoint_id  INT GENERATED ALWAYS AS IDENTITY,
    checkpoint_item_name VARCHAR(210),
    checkpoint_description TEXT,
    constraint pk_checkpoint_item primary key (checkpoint_id),
    constraint u_checkpoint_item_name unique (checkpoint_item_name)
);

Do you think there is a method where I can use the foreign key restriction in json or array?

CREATE TABLE process(
    process_id  INT GENERATED ALWAYS AS IDENTITY,
    fk_category_id INTEGER,
    fk_chekcpoint_item_id TEXT [],
    FOREIGN KEY(fk_category_id) REFERENCES category(category_id) ON DELETE CASCADE,
    FOREIGN KEY(fk_checkpoint_item_id) REFERENCES checkpoint_item(checkpoint_id) ON DELETE CASCADE

);

IMSoP · Answer

It looks like what you have is a "many to many relationship". The usual solution to this situation is normalisation: rather than trying to store a list of foreign keys in one attribute, you reorganise the data so that the links are treated as items in their own right.

In this case, you would create an additional table that represents a link between a process and a checkpoint, with two foreign keys: process_id and checkpoint_item_id

This table is then the source of truth for all queries about both "which checkpoint items are associated with this process" and "which processes are associated with this checkpoint".

How can I define a type like Text [] and how make a foreign key?

1 Answers