Обсуждение: Design database

Hello,

I’ve got a table name publication, and another table named publication_item.
Each publication_item is linked to a publication by publication_id.
How could I add a constraint that check each publication has one or more publication_item when a tuple is inserted ?
I don’t want to store items in the same table as publication because we could have 1-> n items.

Thank you

On Wed, Feb 03, 2021 at 08:35:41AM -0700, David G. Johnston wrote:
> On Wed, Feb 3, 2021 at 8:23 AM Mai Peng <[1]maily.peng@webedia-group.com> wrote:
> 
>   Hello,
> 
>   I’ve got a table name publication, and another table named publication_item.
>   Each publication_item is linked to a publication by publication_id.
>   How could I add a constraint that check each publication has one or more publication_item when a tuple is inserted
?
>   I don’t want to store items in the same table as publication because we could have 1-> n items.
> 
> [2]https://www.postgresql.org/docs/current/tutorial-fk.html

I don't think it helps. Fkey can check that publication_item contains
valid publication_id, but doesn't ensure that every publication has at
least one item.

As for the problem of OP, I think this describes working solution:
https://www.depesz.com/2017/07/03/how-to-make-sure-there-is-always-at-least-one-sub-record/

Best regards,

depesz

Thanks you !
So I’ve implemented your solution Despesz, but I have an upsert function throwing an error : publication (parent table)
hasno item, even id I’ve added a "SET CONSTRAINTS ALL DEFERRED;  
The postgresql version is 9.3; that's why I can not use upsert do on conflict.

CREATE OR REPLACE FUNCTION upsert_related_publications_v1(
    in_related_publications_id INTEGER,
    in_cms_title TEXT,
    in_title TEXT,
    in_related_publications_items JSON
)
RETURNS TABLE (
    out_related_publications_id INTEGER
) AS $$
DECLARE
    inserted_related_publications_id INTEGER;
BEGIN
SET CONSTRAINTS ALL DEFERRED;
    -- Firstly, an upsert is performed for the related_publications table.
    WITH upsert AS (
        UPDATE related_publications
        SET
            cms_title = in_cms_title,
            title = in_title
        WHERE related_publications_id = in_related_publications_id
        RETURNING related_publications_id
    )
    INSERT INTO related_publications (cms_title, title)
    SELECT
        in_cms_title,
        in_title
    WHERE NOT EXISTS (
        SELECT related_publications_id FROM upsert
    ) RETURNING related_publications_id INTO inserted_related_publications_id;

    -- Then, all related publications items that are not handled by the related JSON are
    -- considered as to be dropped.
    WITH related_publications_item_record AS (
        SELECT
            related_publications_item_id
        FROM json_populate_recordset(NULL::related_publications_item, in_related_publications_items)
    )
    DELETE FROM related_publications_item
    WHERE
        related_publications_item.related_publications_item_id NOT IN (
            SELECT coalesce(related_publications_item_record.related_publications_item_id, -1)
            FROM related_publications_item_record
        )
    AND related_publications_item.related_publications_id = coalesce(inserted_related_publications_id,
in_related_publications_id);

    -- Finally, related publications items are upserted.
    WITH related_publications_item_record AS (
        SELECT
            related_publications_item_id,
            content_id,
            item_order
        FROM json_populate_recordset(NULL::related_publications_item, in_related_publications_items)
    )
    UPDATE related_publications_item
    SET
        content_id = related_publications_item_record.content_id,
        item_order = related_publications_item_record.item_order
    FROM related_publications_item_record
    WHERE related_publications_item.related_publications_item_id =
related_publications_item_record.related_publications_item_id;

    WITH related_publications_item_record AS (
        SELECT
            related_publications_item_id,
            content_id,
            item_order
        FROM json_populate_recordset(NULL::related_publications_item, in_related_publications_items)
    )
    INSERT INTO related_publications_item (related_publications_id, content_id, item_order)
    SELECT
        coalesce(inserted_related_publications_id, in_related_publications_id),
        related_publications_item_record.content_id,
        related_publications_item_record.item_order
    FROM related_publications_item_record
    WHERE related_publications_item_record.related_publications_item_id IS NULL;

    RETURN QUERY SELECT coalesce(inserted_related_publications_id, in_related_publications_id);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

Thanks a lot

> Le 3 févr. 2021 à 17:44, hubert depesz lubaczewski <depesz@depesz.com> a écrit :
>
> On Wed, Feb 03, 2021 at 08:35:41AM -0700, David G. Johnston wrote:
>> On Wed, Feb 3, 2021 at 8:23 AM Mai Peng <[1]maily.peng@webedia-group.com> wrote:
>>
>>  Hello,
>>
>>  I’ve got a table name publication, and another table named publication_item.
>>  Each publication_item is linked to a publication by publication_id.
>>  How could I add a constraint that check each publication has one or more publication_item when a tuple is inserted
?
>>  I don’t want to store items in the same table as publication because we could have 1-> n items.
>>
>> [2]https://www.postgresql.org/docs/current/tutorial-fk.html
>
> I don't think it helps. Fkey can check that publication_item contains
> valid publication_id, but doesn't ensure that every publication has at
> least one item.
>
> As for the problem of OP, I think this describes working solution:
> https://www.depesz.com/2017/07/03/how-to-make-sure-there-is-always-at-least-one-sub-record/
>
> Best regards,
>
> depesz
>