Constrain exactly one column not null

Question

I have three tables for different payment types, and want to create a table that holds payments made using all three. I'm not sure if I'm going about this the right way, but I was going to create a foreign key column in the table for each of the three, and the write a constraint such that exactly one of those columns has to be not null.

Is this the right way to go about this?
How do you go about writing this constraint?
Is there any way to do this from within SQLAlchemy on sqlite? (code for declarative classes would be much appreciated)

score 1 · Answer 1 · answered Jun 27 '12 at 15:38

1

Have a single foreign key column, and a separate type column so you know which table to look in.

answered Jun 27 '12 at 15:38

Graham Borland

60,055
21
138
179

in which case how do I write the constraint make that column a foreign key in the correct one of the three tables? – theheadofabroom Jun 27 '12 at 15:56
I ask as this was the original way I thought of doing it, but I could not come up with a way of writing this constraint, and decided that the *exactly one not null* constraint seems more common - I've found some rudimentary ways of doing this more simple constraint for TSQL, but not had much luck here. – theheadofabroom Jun 27 '12 at 16:12

score 1 · Accepted Answer · edited May 23 '17 at 11:56

Ok I've got it - Is this the best way to do it? - I created a generic id field table as such:

class PaymentDetails(Base):
    __tablename__ = 'payment_details'
    id = Column(Integer, primary_key=True)
    type = Column(PaymentType.db_type(), nullable=False)

where PaymentType uses the declarative enum recipe and and then subclassed this for the various payment methods:

@concrete
@do_unique_index('paypal_unique_details', 'env', 'main_email', 'sub_email')
class Paypal(Base):
    __tablename__ = 'paypal_details'
    id = Column(ForeignKey('payment_details.id'), primary_key=True)
    # The rest of the implementation
    #
@concrete
@do_unique_index('credit_card_unique_details', 'env', 'card_number')
class CreditCard(Base):
    __tablename__ = 'card_details'
    id = Column(ForeignKey('payment_details.id'), primary_key=True)
    # The rest of the implementation
    #
@concrete
@do_unique_index('time_code_unique_details', 'env', 'code')
class TimeCodes(Base):
    __tablename__ = 'code_details'
    id = Column(ForeignKey('payment_details.id'), primary_key=True)
    # The rest of the implementation
    #

(Where concrete and do_unique_index set the relevant __mapper_args__ and __table_args__). I then set the description field of the PaymentType enum values to be each of these classes, so that to look up a payment I can query for a PaymentDetails object, then get an id and a type from that, say id and Paypal, to perform a second query for the Paypal with that id.

My code for adding sets of details is fairly simple in that in a single transaction, it adds the next logical id to the PaymentDetails table with the type of the payment details we are trying to create, and then adds an entry to that table with the details I want to enter. I can then add methods to these ORM classes to handle the different ways that we would handle buying, selling, and refunding for each method, such that they can be treated as identical.

You then need to switch on FK constraints as van mentioned - I did so by adding the FK listener to the helper class I use for DB access.

Your solution is the correct one, however not complete, which results in the issue you have. My suggestions: 1) you should [enable FK constraints on sqlite](http://stackoverflow.com/a/2615603/99594); 2) You should complete the configuration of inheritance. see [Mapping Class Inheritance Hierarchies](http://docs.sqlalchemy.org/en/rel_0_7/orm/inheritance.html). Your code is using *Joined Table Inheritance*. Just add *polymorphic_on* and *polymorphic_identity* and you are all set. — van, Jun 27 '12 at 22:05

Constrain exactly one column not null

2 Answers2