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

Question

A top class named Parametric used to create objects that 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 am using SQLAlchemy for my ORM mechanism.

I want to impose a UNIQUE constraint that ensures that the combination ( name , prop ) is unique to this class (for example, only one instance of Foo can be called "my_foo" and have a prop value, say "my_prop" ), but I don’t see how to refer to the name column of Parametric in the Foo table UNIQUECONSTRAINT .

Is this uniqueness something that can be applied using FOREIGN KEY directives?

Answer 1

You can do this using unidirectional table inheritance . However, if your two columns are in different tables, you cannot do exactly what you are trying to do (it is not possible to create a unique constraint for the tables).

If single-dimensional inheritance is not an option, you will probably want to either (1) ensure uniqueness in python, or (2) refuse sqlalchemy inheritance, and just use a foreign key to bind Foo and Bar to Parametric. You can use the foreign key name , and THEN is a unique restriction on name and prop .