> ## Documentation Index
> Fetch the complete documentation index at: https://notes.kodekloud.com/llms.txt
> Use this file to discover all available pages before exploring further.
# Sqlalchemy Foreign Keys
> Learn to establish foreign key relationships in SQLAlchemy to enhance data integrity and manage cascading deletions effectively.
In this lesson, you'll learn how to establish foreign key relationships in SQLAlchemy. This approach not only defines table columns but also automatically sets up the relationships between them, enhancing data integrity.
────────────────────────────────────────────
## Defining Models with a Foreign Key
Begin by opening your `models.py` file where your table schemas are defined. In the `Post` model, add a new column named `owner_id` to reference the user who owns the post. This column should have the same data type as the primary key in the `users` table (i.e., an integer). Additionally, set a foreign key constraint to reference the `id` column of the `users` table with a cascading delete option. This ensures that if a user is removed, all associated posts will be automatically deleted. Mark the field as non-nullable so that every post must have an owner.
Here is the final code implementation for the models:
```python theme={null}
from .database import Base
from sqlalchemy import Column, Integer, String, Boolean, TIMESTAMP, text, ForeignKey
class Post(Base):
__tablename__ = "posts"
id = Column(Integer, primary_key=True, nullable=False)
title = Column(String, nullable=False)
content = Column(String, nullable=False)
published = Column(Boolean, server_default='TRUE', nullable=False)
created_at = Column(TIMESTAMP(timezone=True), nullable=False, server_default=text('now()'))
owner_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), nullable=False)
class User(Base):
__tablename__ = "users"
id = Column(Integer, primary_key=True, nullable=False)
email = Column(String, nullable=False, unique=True)
password = Column(String, nullable=False)
created_at = Column(TIMESTAMP(timezone=True), nullable=False, server_default=text('now()'))
```
────────────────────────────────────────────
## How It Works
1. The `owner_id` column in the `posts` table is defined as an `Integer` to mirror the `id` column in the `users` table.
2. The parameter `ForeignKey("users.id", ondelete="CASCADE")` creates the relationship between the tables. The `CASCADE` option ensures that if a user is deleted, all their associated posts are removed automatically.
3. By setting `nullable=False`, you enforce that every post must be linked to an existing user.
────────────────────────────────────────────
## Updating the Database Schema
When you restart your application, SQLAlchemy verifies if the table already exists. If the `posts` table is already present, the new column won’t be added automatically. In production environments, it is recommended to use a migration tool, such as Alembic, to update table schemas safely. However, during development, you can manually drop the `posts` table using PgAdmin or executing SQL, then restart the application to let SQLAlchemy create a new table based on the updated models.
Remember to always back up your data before performing manual database schema modifications.
────────────────────────────────────────────
## Verifying the Changes in PostgreSQL
After restarting your application, ensure that the new schema is applied. Use your PostgreSQL query tool to run the following command:
```sql theme={null}
select * from posts;
```
You should now see an `owner_id` column defined as non-null. Additionally, if you inspect the column constraints, the foreign key will include the "on delete cascade" action.
────────────────────────────────────────────
## Testing the Foreign Key Constraint
Before testing, verify that there are entries in the `users` table:
```sql theme={null}
select * from users;
```
Next, insert a new post with a valid `owner_id` (make sure it matches an existing user ID):
```sql theme={null}
-- Example SQL insertion (the actual insertion may depend on your application's method)
insert into posts (title, content, published, created_at, owner_id)
values ('Post One', 'Some sample content', TRUE, now(), 21);
```
Attempting to insert a post with a missing or invalid `owner_id` (e.g., using `NULL` or a non-existent user ID) will result in a database error due to the non-null constraint or a foreign key violation.
────────────────────────────────────────────
## Testing the "On Delete Cascade" Functionality
To verify the cascading delete behavior, delete a user whose ID is referenced by one or more posts. For example, if a user with an ID of `20` exists, execute:
```sql theme={null}
DELETE FROM users WHERE id = 20;
```
Then check the `posts` table with the query below:
```sql theme={null}
select * from posts;
```
Any post associated with an `owner_id` of `20` should have been automatically deleted because of the cascade rule.
Be cautious when using cascading deletes in production. Unintended deletions may occur if proper validations and backups are not in place.
────────────────────────────────────────────
## Final Notes
Hard-coding your schema means that adding new fields to an existing table may trigger errors elsewhere in your application until corresponding updates are made to your codebase. For production environments, always use a migration tool like Alembic to manage schema changes and ensure a smooth transition.
This lesson has demonstrated how to set up foreign key relationships in SQLAlchemy, enforce data integrity with cascaded deletions, and verify schema changes in PostgreSQL. For further details, check out the [SQLAlchemy Documentation](https://docs.sqlalchemy.org/) and learn more about [PostgreSQL](https://www.postgresql.org/).
Happy coding!