Creating Votes Table Pgadmin

In this lesson, you'll learn how to create a new votes table in pgAdmin using standard PostgreSQL commands and later through SQLAlchemy. We'll also cover defining foreign key constraints to enforce referential integrity.

Step 1: Verifying the Existing Posts Table

Before adding the votes table, verify the contents of the existing posts table by executing the following query:

SELECT * FROM posts;

Step 2: Creating the Votes Table

Within the tables section of pgAdmin, create a new table called "votes." Define the following columns:

• post_id: Stores the associated post's ID.
• user_id: Stores the ID of the user casting the vote.

Both columns should be of integer type. Configure these columns as a composite primary key to ensure that each combination of post_id and user_id remains unique.

Step 3: Defining Foreign Key Constraints

Establish foreign key constraints to maintain proper relationships among tables.

Adding the Foreign Key for post_id

Set a foreign key constraint on the post_id column so it references the ID column in the posts table. Be sure to apply the "ON DELETE CASCADE" rule, which ensures that deleting a post will also remove its associated votes.

During configuration, select the local column (post_id) in the votes table and reference it to the ID column in the posts table. If an error occurs stating that the columns for the foreign key need specification, double-check that you have correctly selected the referencing column.

Adding the Foreign Key for user_id

Next, add a foreign key constraint for the user_id column, setting it to reference the ID column in the users table. Again, configure the "ON DELETE CASCADE" rule to ensure that deleting a user also removes the associated votes.

After configuring both foreign keys, save your changes.

Step 4: Verifying and Testing the Votes Table

To review the data in the votes table, right-click on the table in pgAdmin and select "View/Edit Data." You can also open a new query window and execute the following SQL command to display the votes ordered by post_id and user_id:

SELECT * FROM public.votes
ORDER BY post_id ASC, user_id ASC;

Additionally, re-run these queries to check the contents of the posts and users tables:

SELECT * FROM posts;

SELECT * FROM users;

If you experience any issues while editing data (such as input bugs within pgAdmin), use the "View/Edit Data" feature for a smoother experience.

Next Steps

After confirming that the votes table functions correctly, you can remove it if it was created solely for testing purposes. In the next lesson, you will learn how to define your model in SQLAlchemy so that the framework can automatically generate the table as part of your application development.

Happy coding!