> ## 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.
# Votes With Sqlalchemy
> This article explains how to create a votes table model using SQLAlchemy, including foreign keys and composite primary keys for user and post IDs.
In this lesson, we will create the model for the votes table in our models.py file using SQLAlchemy. The model, named Vote, will extend Base and include two columns: one for the user ID and another for the post ID. Both columns are of type Integer, serve as foreign keys to the users and posts tables respectively, have "ON DELETE CASCADE" enabled, and together form a composite primary key.
Below is an example of how the models might be defined:
```python theme={null}
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)
owner = relationship("User")
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()'))
class Vote(Base):
__tablename__ = "votes"
user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), primary_key=True)
post_id = Column(Integer, ForeignKey("posts.id", ondelete="CASCADE"), primary_key=True)
```
After saving these changes, restart the application without errors. You can verify the new votes table in PgAdmin by refreshing the database view. In PgAdmin, inspect the table properties to confirm that:
* The votes table includes both the user\_id and post\_id columns.
* These columns together act as a composite primary key, ensuring that neither column is null.
* Both columns have correctly defined foreign keys with "CASCADE" on delete enabled.
Double-check the votes table in PgAdmin by inspecting its properties. The composite primary key and foreign key settings are essential for maintaining integrity between your posts and users tables.
To further test the setup, run the following SQL query. This query retrieves data from the votes table, sorted by user\_id and post\_id:
```sql theme={null}
SELECT * FROM public.votes
ORDER BY user_id ASC, post_id ASC;
```
This query will display vote records. For instance, if there is a vote with user\_id 21 and post\_id 10, it will appear in the results. Attempting to insert a record with an invalid user or post reference will produce an error, confirming that the foreign key constraints and cascade delete behavior are enforced correctly.
By following these steps, you ensure that your votes table is correctly modeled and seamlessly integrated with the posts and users tables using SQLAlchemy.