python - Create a constraint on multiple columns in SQLAlchemy

Question

I have a MySQL table in order to manage building entrance methods in a city described by the following model:

class EntranceMethod(db.Model):
    id = Column(String, primary_key=True)
    building_id = ForeignKey('building.id', nullable=False)
    user_id = ForeignKey('user.id', nullable=False)
    card_name = Column(String, nullable=False)
    gate = Column(Enum('A', 'B', 'C'), nullable=False) 

In a building, a person can have many cards, which can be used to identify them and allow them to go through certain gates. However, these cards also can be used to do that at other buildings.

In a building scope, I want to ensure that:

• With a specific gate, an user cannot use more than 1 card to enter. (They can enter the building at other gates by this card but cannot use another card at this gate).

So I created an unique constraint: (building_id, user_id, gate)

• A card belongs to only 1 user.

What constraint should I make? I thought of a unique constraint for building_id, card_name, gate will solve the problem, but its just say that, 'This card can be used by only one user on each gate'. The case, user U use card K at gate A, and user V use card K at gate B, is still able to be.

For example, I have some records (one building scope):

user_id	card_name	gate
user1	card1-1	A
user1	card1-2	B
user2	card2-1	C
user2	card1-1	B

question from:https://stackoverflow.com/questions/65883323/create-a-constraint-on-multiple-columns-in-sqlalchemy

Answer 1

Your constraint (building_id, user_id, gate) implies the uniqueness of an entrance method for a given user. Verbally, it means

"One user cannot have more than one entrance method for a gate in a building"

or

"One gate in a building cannot have more than 1 way of entering a single user"

Since you are not handling card uniqueness in any way, it's not possible to imply that User1 will not have the same card as User2. However, your primary condition would still be satisfied - one user will be able to use only one card ( whatever text you will put into the card field of the EntranceMethod ) to pass through.

Since there is ONLY ONE entrance method for a gate for each user, there would be no duplicates of card field for a given User, building_id and gate.

If you'd like to additionally ensure that the users won't share their cards to enter, you may introduce a Card model, with a ForeignKey to User model. This way, one card instance will be attached to only one User.

However, this method implies that you have to validate EntranceMethod upon creation - additionally check if the card_id is one of the user's cards.