From A to SQL — a deeper dive into database design and management
Welcome back to the next installment of my learning journey. In this chapter, I’ll be unlocking the door to the world of database design and management, using primary and foreign keys. Don’t worry, I didn’t just press random keyboard buttons there; those are real SQL terms, and it turns out they are quite important.
So, let’s continue to proactively improve SQL coding skills, one query at a time. If you want a refresher on foundation concepts, and read an introduction into databases, check out Part A and Part B of my learning journey.
If you are working within the world of SQL you may come across two acronyms: DDL and DML. DDL stands for Data Definition Language while DML stands for Data Manipulation Language.
What’s the difference between DDL and DML?
Let’s imagine you have just brought your first house. The house structure is primarily made up of walls, floor, and a roof. The house will hold all your furniture and belongings. If you want to change two of your bedrooms into one master bedroom, you will have to change the structure of the house by knocking down a wall.
Now remember that in the simplest form, a database is a container of data. The way data is organised is in tables which hold data, but on their own, they are just a shell. Therefore, if you want to change the structure of the database, for example by adding an additional column, you would use Data Definition Language SQL statements.
We now understand DDL, so we can return to our house example. You’ve made all the necessary changes to the house structure, and now you want to move the furniture from one room to the other. You are no longer making changes to the structure of your house, but to the belongings that your house holds. In the world of databases, if you want to make changes to the data held within the database structure, you would use Data Manipulation Language SQL statements.
The 4 main DDL SQL statements are 1) CREATE, 2) ALTER, 3) RENAME and 4) DROP. On the other hand, the 4 main DML SQL statements are 1) SELECT, 2) INPUT, 3) UPDATE and 4) DELETE.
I thought keys opened doors — how are they related to SQL?
Primary keys and foreign keys are two very important types of data constraints. A constraint is simply rules on the type of data that can be held in the database. For example, you could specify that Column 2 in your table is not allowed to have null values by adding the NOT NULL constraint.
The primary key is a unique identifier of a particular record of data. Usually, you will see it referred to the column which holds ID numbers for records of data. As you would expect with ID numbers, each value must be unique, and it cannot be empty.
On the other hand, a foreign key is a field in a table of data which matches another field in a different table. It’s like a link between the two tables, so that the constraints you place on one table are maintained in the related tables, which helps you to achieve data integrity in the database.
As you can see from the example above, there are three tables: Customers, Orders and Employee. The primary key (the unique record that identifies a table) is the Customer ID for the Customers table, the Order Number for the Orders table, and the Employee Number for the Employee table. In the Orders table, you also have the client ID which acts as a foreign key — it links the Orders table with the Customers table.
Why is this important? Well, if we wanted to change the Customer ID in the Customers table and add an ID which doesn’t exist in the Orders table, we would get an error. This stops inaccuracies in data.
Enough theory; let’s get coding.
Ready? Set? DROP.
I am going to use the same example as from Part 2 of my learning journey. If you want a refresher on how I made the tables which are referenced below, you can read over the syntax here.
Step 1 — Creating a new table called Orders and populating it with data
The syntax for creating and inserting data into tables is part of DML.
Step 2 — Updating the Customer table with new columns and new data
In the current Customer table, we have 4 columns: C_ID, C_NAME, SURNAME, LOCATION. Let’s add two more column to store a phone number and an email address for each of the Customers.
The syntax for adding columns to a table is:
ALTER TABLE <table_name>
ADD <column_name> <type>;
The syntax for updating values in an existing table is:
UPDATE <table_name> as alias
SET alias.colname1 = new_value
WHERE alias.colname2 = value;
Note: I couldn’t figure out a way to update all the values in one query, so I wrote it out individually for each phone number and email address value.
We now have an updated Customer table. However, notice anything weird? Two of the phone numbers are identical! We can use the same syntax to update the value to the correct number.
Step 3 — Adding primary and foreign keys to the tables to link them together
The primary keys for each of our tables are a) C_ID, b) I_ID, c) I_ID and d) ORDER_NR
The syntax to add a primary key to an existing table is:
ALTER TABLE <table_name>
ADD CONSTRAINT
<constraint_name>
<constraint_type>
(<col_that_it_applies_to>);
Similarly to the diagram we saw above, the Customer table and the Orders table is linked through the C_ID column. In this way we can add a foreign key to the Orders table.
The syntax to add a foreign key to an existing table is very similar to adding a primary key, but there is an extra bit of code:
ALTER TABLE <table_name>
ADD CONSTRAINT
<constraint_name>
<constraint_type>
(<col_that_it_applies_to>)
REFERENCES
<table_name2>
(<col2_that_it_applies_to>)
);
Remember, in our Customer table, we only have Customer ID numbers which range from C1 to C7. To demonstrate the importance of setting foreign keys for data integrity, I tried to update the orders table, and change the C_ID to C9, for the row where the order number was 07. As expected, there query did not run, and an error appeared, because there is no customer ID C9 in the Customer table.
Yay we just avoided an error!
That was a lot of new skills, but the key to success is practice and consistency!
Keep your eyes peeled for the next sequel of my learning journey :)
Go from SELECT * to interview-worthy project. Get our free 5-page guide.
