SQL Basics : Constraints
To learn about constraints, primary keys, foreign keys and referential integrity.
What are Constraints?
Relational databases store data into multiple tables. There are relationships that exist between the tables. That's why it's called Relational database. Keys reference from one table to another. This is called referential integrity. In our example, when we store a record in the
line_items table, the
order_number references to the id of the orders table. There cannot be a
line_items record that does not have a
order_number. This would violate the referential integrity.
Relational databases enforce referential integrity by imposing constraints on database tables. You can create the constraints when you use the CREATE TABLE or ALTER TABLE commands.
A primary key ensures that values in a column are unique. So, you can uniquely identify each row in a table. This makes the UPDATE or DELETE safe to use, ie., they don't affect other rows. Let's create a
sqlite> CREATE TABLE credit_cards ...> ( ...> id int primary key not null, ...> type char(10) not null, ...> user_id int not null ...> );
The keyword primary key in the table definition defines id as the primary key of the
credit_cards table. We can see the new table.
sqlite> .tables credit_cards line_items products customers orders users
We can also see the table structure.
sqlite> .schema credit_cards CREATE TABLE credit_cards ( id int primary key not null, type char(10) not null, user_id int not null );
A foreign key is a column in a table that references the primary key in another table. They are used to enforce referential integrity.
Let's delete the table we just created.
sqlite> drop table credit_cards; sqlite> .tables customers line_items orders products users
Now lets create the credit_cards table again that will use a foreign key.
sqlite> CREATE TABLE credit_cards ...> ( ...> id int primary key not null, ...> type char(10) not null, ...> user_id int not null, ...> FOREIGN KEY (user_id) REFERENCES users(id) ...> );
We can see the schema of the new table.
sqlite> .schema credit_cards CREATE TABLE credit_cards ( id int primary key not null, type char(10) not null, user_id int not null, FOREIGN KEY (user_id) REFERENCES users(id) );
Here the table definition uses the FOREIGN KEY keyword to make the
user_id field a foreign key, it uses REFERENCES to indicate that it references the id field in users table. Now, we cannot create any records in the
credit_cards table that does not have a
user_id value that does not exist in the users table.
Here is an example that creates a new credit card for Yosemite Sam.
sqlite> INSERT INTO credit_cards(id, type, user_id) ...> VALUES (1, 'VISA', 10); sqlite> select * from credit_cards; id type user_id ---------- ---------- ---------- 1 VISA 10 sqlite> select * from users; id first_name last_name email ---------- ---------- ---------- ----------------- 1 bugs bunny email@example.com 2 daffy duck firstname.lastname@example.org 3 porky pig email@example.com 4 pluto dog firstname.lastname@example.org 5 pluto pet email@example.com 6 tasmanian devil firstname.lastname@example.org 7 tweety bird email@example.com 8 elmer fudd firstname.lastname@example.org 9 speedy gonzales email@example.com 10 yosemite sam firstname.lastname@example.org 11 sylvester email@example.com 12 bosko firstname.lastname@example.org
Here is an example that shows creating a row that violates the foreign key constraint results in an error.
sqlite> INSERT INTO credit_cards(id, type, user_id) ...> VALUES (1, 'VISA', 13); Error: column id is not unique
We can verify that there is only one record in the
sqlite> select * from credit_cards; id type user_id ---------- ---------- ---------- 1 VISA 10
In this article you learned about constraints, primary key and foreign key. You also learned about how they enforce referential integrity.
Ace the Technical Interview
- Easily find the gaps in your knowledge
- Get customized lessons based on where you are
- Take consistent action everyday
- Builtin accountability to keep you on track
- You will solve bigger problems over time
- Get the job of your dreams
Take the 30 Day Coding Skills Challenge
Gain confidence to attend the interview