When data is split into separate tables to reduce repetition, each record needs a dependable identity. A primary key is a column whose value is unique across its table. Foreign keys use those identities to reconnect related records. section 10.3.2
Choose an identifier that will remain useful as the application changes:
- An existing unique value, such as a social security number, can serve as the key. A separate ID column is often easier and more future-proof because it does not depend on the stored data remaining unique. section 10.3.2
- An auto-incrementing integer provides a unique value as each new row is inserted. section 10.3.2
- A UUID provides an identifier unique beyond one table, which can help when database structures and uses change. section 10.3.2
Load related records
In a CSV-to-database migration, create customers, addresses, and products first. Committing those records generates their primary-key values; the values are then available when creating orders and their associated items. section 10.7.1
class User(UserMixin, db.Model): #1
__tablename__ = "user" #2
user_uid = db.Column(db.String, primary_key=True, default=get_uuid) #3
first_name = db.Column(db.String, nullable=False) #4
last_name = db.Column(db.String, nullable=False) #4
email = db.Column(db.String, nullable=False, #4
unique=True, index=True) #4
hashed_password = db.Column("password", #4
db.String, nullable=False) #4
active = db.Column(db.Boolean, nullable=False, default=False) #4
created = db.Column(db.DateTime, nullable=False, #5
default=datetime.utcnow) #5
updated = db.Column(db.DateTime, nullable=False, #5
default=datetime.utcnow, onupdate=datetime.utcnow) #5
def get_id(self):
return self.user_uid
@property
def password(self):
raise AttributeError("user password can’t be read")
@password.setter
def password(self, password):
self.hashed_password = generate_password_hash(password)
def verify_password(self, password):
return check_password_hash(self.hashed_password, password)
def __repr__(self):
return f"""
user_uid: {self.user_uid}
name: {self.first_name} {self.last_name}
email: {self.email}
active: {'True' if self.active else 'False'}
"""
The User model marks user_uid as the primary key and supplies a UUID-generating default. Its email column is separately marked unique: uniqueness alone does not make that column the chosen primary key. section 8.2.1
The practical choice is not just whether a value is unique today. Pick a key that lets other records refer to it reliably as the data and application evolve. section 10.3.2
