Difference and use of UniqueConstraint in python class

Question

I'm using python 2.7 and sqlalchemy 0.9.9. In our code I found different ways to define an UniqueConstraint and I don't know which one is the right one or if we need them at all (we don't want to create a table with our code).

Example 1:

class ExampleOne(OrmModelBase, TableModelBase):

__tablename__ = 'example_one'

example_one_id = Column(Integer, Sequence('seq_example_one_example_one_id'), 
                        primary_key=True, nullable=False)

example_one_name = Column(String, unique=True, nullable=False)

def __init__(self):
    self.example_one_id = self.getNextPkValue()
    self.example_one_name = ''

Example 2 (Version 1):

class ExampleTwo(OrmModelBase, TableModelBase):

__tablename__ = 'example_two'
example_two_id = Column(Integer, Sequence(
    'seq_example_two_example_two_id'), primary_key=True,
    nullable=False)

example_two_name = Column(String, nullable=False)

UniqueConstraint(
    "example_two_id", "example_two_name",
    name="uk_example_two_example_two_id_example_two_name")

def __init__(self):
    self.example_two_id = self.getNextPkValue()
    self.example_two_name = example_two_name

Example 2 (Version 2):

class ExampleTwo(OrmModelBase, TableModelBase):

__tablename__ = 'example_two'
example_two_id = Column(Integer, Sequence(
    'seq_example_two_example_two_id'), primary_key=True,
    nullable=False)

example_two_name = Column(String, nullable=False)

uk_example_two_example_two_id_example_two_name = UniqueConstraint(
"example_two_id", "example_two_name")

def __init__(self):
    self.example_two_id = self.getNextPkValue()
    self.example_two_name = example_two_name

Example 3:

class ExampleThree(OrmModelBase, TableModelBase):
__tablename__ = 'example_three'

example_three_id = Column(Integer,
                          Sequence('example_three_example_three_id'),
                          primary_key=True)

example_three_name = Column(String, nullable=False)

__table_args__ = (
    UniqueConstraint("example_three_name", name="uk_example_three_name"),
)

def __init__(self):
    self.example_three_id = self.getNextPkValue()
    self.example_three_name = ""

I don't want to create a table with this code, I create the needed tables manually with the needed constraints.

When I don't create tables, do I need the definitions of an unique constraint (or primary_key, nullable etc.)? Is there an effect when I do write one of the 3 versions in the class?

I read that the first example is for unique constraints on a single column and the second example for an unique constraint on more than one. But the examples I found always used a Table and just example three was used in my case (classes).

Is there a difference between the use of the unique constraints and what is the difference?

I hope there is someone who could help me. Thanks for your time!

knitti knitti · Accepted Answer · 2017-04-05T14:26:39

The definition of unique constraints in sqlalchemy is indeed only used in for schema generation (creating tables). Additionally, there are differences between the major RDBMS how those are defined (especially across multiple columns) and what is exactly possible. This is - when supported by sqlalchemy - handled by the dialect and more or less opaque.

So... You wouldn't suffer consequences if you left them out.

But.

I'd leave them in the code for documentation, they might be a helpful reminder why the underlying RDBMS behaves in a certain way.

Difference and use of UniqueConstraint in python class

1 Answers