1

I would like to model a User having many Addresses and being able to have a "primary" address.

I'm trying to create the strict guarantee that a User must have at least one Address by making the primary_address_id required. I would also like no orphaned addresses and therefore make Address.user_id also required.

class User(db.Model):
    __tablename__ = 'Users'
    id = Column(Integer, primary_key=True)
    primary_address_id = Column(Integer, ForeignKey('Addresses.id'), nullable=False)

    primary_address = relationship(Address, primaryjoin=primary_address_id == Address.id, foreign_keys=primary_address_id)
    addresses = relationship(Address, primaryjoin=id == Address.building_id, foreign_keys=Address.building_id)

class Address(db.Model):
    __tablename__ = 'Addresses'

    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('Users.id'), nullable=False)

When I try the following insertion of a User with a single address it fails because primary_address_id is None. 

user = User()
address1.user = user
user.addresses = [address1]
user.primary_address = address1

session.add_all([address1, user])

session.commit()

cursor.execute(statement, parameters) sqlalchemy.exc.IntegrityError: (psycopg2.IntegrityError) null value in column "primary_address_id" violates not-null constraint [SQL: 'INSERT INTO "Users" (primary_address_id) VALUES (%(primary_address_id)s) RETURNING "Users".id'] [parameters: {'primary_address_id': None}] (Background on this error at: http://sqlalche.me/e/gkpj)

Is there a way around the chicken-egg problem with SqlAlchemy and Postgres without having to make one side nullable? Alternatively is there another way to model this with the same referential requirements?

Jay
  • 73
  • 2
  • 6
  • Possibly helpful: https://dba.stackexchange.com/questions/102903/is-it-acceptable-to-have-circular-foreign-key-references-how-to-avoid-them – match Feb 18 '18 at 14:19
  • @match thanks, I tried to follow that but struggled to get it working with SqlAlchemy. – Jay Feb 18 '18 at 20:53
  • Also possibly helpful https://dba.stackexchange.com/questions/34151/how-to-have-a-one-to-many-relationship-with-a-privileged-child, https://stackoverflow.com/questions/8394177/complex-foreign-key-constraint-in-sqlalchemy/8395021#8395021 – snakecharmerb Aug 22 '21 at 13:38

0 Answers0