Day 37: Adding constraints to a table

Implementing Constraints in SQL: Ensuring Data Integrity

Constraints in SQL are a way to set rules for our tables. For instance, if we want to ensure that we never forget to include the title of a book when inserting new information into our bookstore database, we can use constraints.

To do this, we first need to modify our current table using the ALTER keyword. We specify that we want to alter the 'books' table, then we add the constraint. We give the constraint a name, in this case 'title_not_null', and then we define our constraint. Here, we want to check that the title is not null. We use the CHECK clause and specify that the title column should not be null. This way, we can ensure that each time we enter new information into our table, the title column will always contain some kind of information.

Adding Constraints with SQL

Here's how you can add a 'NOT NULL' constraint to the 'title' column of the 'books' table using SQL and Python:

import sqlite3

# Connect to the bookstore.db database
conn = sqlite3.connect("bookstore.db")

# Create a cursor to execute SQL commands
cursor = conn.cursor()

# Add a NOT NULL constraint to the title column of the books table
cursor.execute("""
ALTER TABLE books
ADD CONSTRAINT title_not_null
CHECK (title IS NOT NULL)
""")

# Commit the changes to the database
conn.commit()

# Close the connection to the database
conn.close()

In this Python script, we connect to the bookstore.db database and create a cursor to execute SQL commands. We then use the ALTER TABLE statement to add a 'NOT NULL' constraint to the 'title' column of the 'books' table. Finally, we commit the changes and close the database connection.

By using constraints in SQL, we can ensure data integrity and improve the quality of our data.

Learn More

Want to learn more about SQL for Machine Learning? Check out the full course HERE.

---

Need help mastering Machine Learning?

Don't just follow along — join me! Get exclusive access to me, your instructor, who can help answer any of your questions. Additionally, get access to a private learning group where you can learn together and support each other on your AI journey.

Subscribe Now