[SOLVED] How to add a unique constraint on two fields with PonyORM

How to add a unique constraint on two fields with PonyORM

I use PonyORM, and I want to add a unique constraint on a combination of two fields.

Here's an example (PonyEditor link : https://editor.ponyorm.com/user/lial_slasher/DoubleConstraint).

I have two tables, Parent and Child. A parent can have many childs, but not two with the same name. Which means I want to add a unique constraint on the couple (parent, name) of the Child table.

It's pretty straightforward, but I can't figure out the python syntax to do it.

Solution

You can use composite_key which according to the document, is equivalent to UNIQUE on several columns in SQL.

example:

from pony.orm import *
db = Database()

class Parent(db.Entity):
    name = Required(str)
    children = Set("Child")

class Child(db.Entity):
    parent = Required(Parent)
    name = Required(str)
    composite_key(parent_id, name)

Here, the name attribute in the Child class will be unique together with the parent which means each parent can have exactly one child with a specific name. This is equal to the following SQL query:

CREATE TABLE IF NOT EXISTS public.child
(
    id integer NOT NULL DEFAULT nextval('child_id_seq'::regclass),
    parent integer NOT NULL,
    name text COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT child_pkey PRIMARY KEY (id),
    CONSTRAINT unq_child__name_parent UNIQUE (name, parent),
    CONSTRAINT fk_child__parent FOREIGN KEY (parent)
        REFERENCES public.parent (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE CASCADE
)