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?

Answer 1

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.