Keep Data Integrity with Constraints

Instructor: [00:00] One of relational databases best strengths is the ability to flatten data across many tables. They can be independent from each other, or we can connect them together. Adding constraints to tables is how we can connect them together, and make sure our data keeps its integrity, and as we scale it does so efficiently.

[00:18] Now let's create the users table. We'll give it create_date of date, a user_handle of a UUID, last_name of text, first_name of text, and then we're going to do constraint PK users, primary key, and then user_handle here.

[00:32] A primary key is a column or a group of columns used to identify a row uniquely in a table. It could be created by defining it as a constraint as we did here, or since we only used one column as the key, we could have written it before the closing comma of this line for user handle by writing the words primary key.

[00:51] A table can only have one primary key, and whatever is defined here, the value inserted into the column or columns needs to be not NULL and unique. What we're saying here, is that this table cannot have more than one distinct user handle in it, or we'll get an error. As you can see, this helps keep our data in check, because two users in our table with the same user handle could cause us some real issues down the road.

[01:17] The PK_users is the name of the key we're giving it. You don't have to give it a name, Postgres and most databases will automatically create a name for you if you don't give it one. As I mentioned before, we cannot insert a NULL value in a primary key. It's recommended to always give your table a primary key whenever you create a new table.

[01:35] Now if you wanted to make sure your users give you an email when they're signing up, but you don't want the email as part of your primary key, you can use the NOT NULL constraint. While we're at it, we probably also want to make sure we always have a create_date and a first_name as well. After dropping our users table, let's recreate it, but we're going to add this NOT NULL statement at the end of the columns we care about not being NULL.

[01:57] Let's go make create_date, user_handle and first_name. With these constraints in place, we can now rest easy at night knowing that our data in our tables is a little bit more secure. Also my own personal opinion is to avoid dealing with NULLs in databases as much as possible. They can cause problems without you even knowing. Similar to the primary key constraint, the UNIQUE constraint make sure that values stored in a column or a group of columns are unique across rows in the table.

[02:23] As you can see, we can combine concern constraints together, giving us the flexibility to make sure we keep our data in a maintainable state. Let's wrap up by recreating our users table and the desired output. Create_data is NOT NULL, the user_handle is UNIQUE and NOT NULL as our primary key. Users can have a NULL for a last_name, and our first_name is NOT NULL as well. We want to start tracking all the purchases our users make, so we'll create a purchases table.

[02:51] We want our data between the two tables to be true, meaning we should never have users make purchases that are not in our users table. How difficult would it be to find that people are making purchases and we don't have their data in our users table? We can do that by creating a foreign key between the two tables. By adding this references constraint here, we're adding a foreign key that states whatever values inserted here within purchases, it needs to exist on the users table as well.

[03:17] With that in place, if we try to insert a random user handle into purchases, we get an error. It tells us that we're violating our foreign key constraint. If you're curious, you can add constraints to already created tables through the alter table statement.