Set foreign key to nullable=false?

Question

Do you set your foreign keys as nullable=false if always expect a foreign key on that column in the database?

I'm using sqlalchemy and have set my models with required foreign keys. This sometimes causes me to run session.commit() more often, since I need the parent model to have an id and be fully created in order to build a child object in the ORM. What is considered best practice? My models are below:

class Location(Base):
    __tablename__ = 'locations'

    id = Column(Integer, primary_key=True)
    city = Column(String(50), nullable=False, unique=True)

    hotels = relationship('Hotel', back_populates='location')


class Hotel(Base):
    __tablename__ = 'hotels'

    id = Column(Integer, primary_key=True)
    name = Column(String(100), nullable=False, unique=True)
    phone_number = Column(String(20))
    parking_fee = Column(String(10))
    location_id = Column(Integer, ForeignKey('locations.id'), nullable=False)

    location = relationship('Location', back_populates='hotels')

score 5 · Accepted Answer · answered Sep 09 '16 at 21:21

5

You don't need to do session.commit() to get an ID; session.flush() will do.

Even better, you don't need to get an ID at all if you set the relationship because SQLalchemy will figure out the order to do the INSERTs in. You can simply do:

loc = Location(city="NYC", hotels=[Hotel(name="Hilton")])
session.add(loc)
session.commit()

and it will work fine.

answered Sep 09 '16 at 21:21

univerio

19,548
3
66
68

Thanks. I refactored my code and was able to remove the session.commit() while keeping the foreign key required. – Casey Sep 10 '16 at 01:31

score 0 · Answer 2 · edited May 23 '17 at 12:17

I would suggest that you'd better not set nullable=False. Make foreign key nullable is very reasonable in many situations. In your scenario, for example, if I want to insert a hotel whose location is currently underdetermined, you can not accomplish this with the foreign key not null. So the best practice when using foreign key is to set it nullable.

See necessary nullable foreign key Any example of a necessary nullable foreign key?

Set foreign key to nullable=false?

2 Answers2