Storing unique values in the same table but with different parent values

Question

I have 2 tables Parent and Child.
Child has a unique attribute(childNo)
As long as 2 Children have different Parents, they should be able to have the same childNo.
2 Children with the same Parents should not have the same childNo

I can't think of a way to implement this as I'm getting a unique constraint violation.

Erwin Brandstetter · Accepted Answer · 2021-07-03T22:47:54.097

1

Use a multicolumn UNIQUE index or constraint on child (childNo, parent_id).

This way, the same combination of (childNo, parent_id) cannot be entered a second time. That's all you need.
(Might also serve as PRIMARY KEY, replacing the additional child.id, but that depends ...)

If child.parent_id can be NULL (and you don't want two rows with the same childNo and NULL), consider:

How to add a conditional unique index on PostgreSQL

And avoid CaMeL-case identifiers in Postgres to make your life easier. See:

Are PostgreSQL column names case-sensitive?

edited Jul 03 '21 at 22:47

answered Jul 03 '21 at 22:42

Erwin Brandstetter

605,456
145
1,078
1,228

1

Conceptually the OP wants a "constraint" so my guess it's better to present it as a contraint (when possible) rather than an index. Of course, both will work as expected. – The Impaler Jul 04 '21 at 03:50
@TheImpaler: Yeah, constraint should be the preferred route. Plus `NOT NULL` on both columns to rule out the mentioned corner case. – Erwin Brandstetter Jul 04 '21 at 03:54
Thank you,Can this be implemented with sqlalchemy ORM in Flask? as it's what I'm using – Internet Presence Jul 04 '21 at 09:59
@InternetPresence: I don't normally use ORMs, but I have no doubt it can. – Erwin Brandstetter Jul 04 '21 at 13:16
1

I managed to make it work, used __table_args__ = (db.UniqueConstraint("childNo","parent_id") in the Child class – Internet Presence Jul 04 '21 at 16:39

Storing unique values in the same table but with different parent values

1 Answers1