Unique constraint using data in multiple tables (SQL / SQLAlchemy)

Question

A top class called Parametric is used to create objects which can have parameters associated with them:

class Parametric(object):
 def __init__(self, name):
  self.name = name
  self.pars = []

class Foo(Parametric):
 def __init__(self, name, prop):
  self.prop = prop
  Parametric.__init__(self, name)

class Bar(Parametric):
 def __init__(self, name, prop):
  self.prop = prop
  Parametric.__init__(self, name)

I use SQLAlchemy for my ORM engine.

I want to impose a UNIQUE constraint that ensures that the combination (name, prop) are unique for a given class (e.g. only one instance of Foo can be called "my_foo" and have a prop value of, say "my_prop"), but I don't see how to reference the name column from Parametric in the Foo table UNIQUECONSTRAINT section.

Is this uniqueness something which can be imposed via FOREIGN KEY directives?

At the same time you have an instance of Foo called my_foo with a prop value of my_prop, can you also have an instance of Bar called my_foo (not a typo) with a prop called my_prop? — Mike Sherrill 'Cat Recall'
– Mike Sherrill 'Cat Recall', Commented Jan 12, 2011 at 11:23
This is not sqlalchemy code. There are many different approaches to inheritance in sqlalchemy, but i can tell you right away that you can only setup in-base constraints (without creating additional tables) if you use shared table inheritance - your prop and name columns should be in the same table to do that. — thule
– thule, Commented May 20, 2011 at 10:32
Are you perhaps trying to do what mixins do? sqlalchemy.org/docs/orm/extensions/… — X-Istence
– X-Istence, Commented Jun 21, 2011 at 21:55

Dave · Accepted Answer · 2011-09-19 19:51:24Z

5

You could do this using single table inheritance. However if your two columns are in different tables, you can't do exactly what you're trying to do (it is not possible to do a unique constraint across tables).

If single table inheritance is not an option, you probably want to either (1) enforce uniqueness in python, or (2) abandon sqlalchemy inheritance, and just use a foreign key to link Foo and Bar to Parametric. You could foreign key to name, and THEN do a unique constraint on name and prop.

answered Sep 19, 2011 at 19:51

Dave

11.9k5 gold badges38 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mahmoud Magdy · Accepted Answer · 2021-12-25 19:55:37Z

0

in case 2 different tables you can do that in form validation check if it repeated.

answered Dec 25, 2021 at 19:55

Mahmoud Magdy

98913 silver badges14 bronze badges

Collectives™ on Stack Overflow

Unique constraint using data in multiple tables (SQL / SQLAlchemy)

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related