Setup and Introduction to SQL Fundamentals

Create a Table with SQL Create

Add Data to a Table with SQL Insert

Query Data with the Select Command in SQL

Update Data in a Table with SQL Update

Removing Data with SQL Delete, Truncate, and Drop

Keep Data Integrity with Constraints 

Organize Table Data with Indexes

Select Grouped and Aggregated Data with SQL

Conditionally Select out Filtered Data with SQL Where

Combining Tables Together with SQL Join Statements

Subquery Dynamic Datasets in SQL

SQL Fundamentals

If the data in your tables do not have integrity, meaning there are missing rows, incorrect values, or duplicate rows, your table is not worth much. Data is king and there are simple ways we can make sure we keep this integrity. Constraints are ways to make sure our data stays in a healthy state. Not nulls, uniques, primary keys, and foreign keys, are all constraints we will review in this lesson to give you the tools to keep your data in check. 

https://app.egghead.io/api/v1/lessons/postgresql-keep-data-integrity-with-constraints/subtitles

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.

Keep Data Integrity with Constraints

Share this video with your friends

Course

SQL Fundamentals