Name: Enforce Custom Logic with Check Constraints in Postgres
Uploaded: 2017-01-12T01:38:34-06:00
Description: What if your tables need custom constraints? A movie should only receive 1-5 stars, never 0 or 6. Price after tax should always equal the sum of the price plus tax. Check constraints help you enforce your own custom logic.

Create a Postgres Table

Insert Data into Postgres Tables

Filter Data in a Postgres Table with Query Statements

Update Data in Postgres

Delete Postgres Records

Group and Aggregate Data in Postgres

Sort Postgres Tables

Ensure Uniqueness in Postgres

Use Foreign Keys to Ensure Data Integrity in Postgres

Create Foreign Keys Across Multiple Fields in Postgres

Enforce Custom Logic with Check Constraints in Postgres

Speed Up Postgres Queries with Indexes

Find Intersecting Data with Postgres’ Inner Join

Select Distinct Data in Postgres

Get Started With PostgreSQL

What if your tables need custom constraints? A movie should only receive 1-5 stars, never 0 or 6. Price after tax should always equal the sum of the price plus tax. Check constraints help you enforce your own custom logic.

https://app.egghead.io/api/v1/lessons/postgresql-enforce-custom-logic-with-check-constraints-in-postgres-5abc3c22/subtitles

[00:00] We have a movies table. We don't have any kind of constraint that limits us on the data that we can actually put in here. Let's look at just a normal insert statement here, and we'll add in "Barton Fink" and we'll say it was released on this day. I don't think that's true.

[00:24] Let's give it a CountStars of 6, even though, we want CountStars to really just be 1 to 5. We don't have any kind of constraint here yet. We're going to insert that. Now, we can see Barton Fink violates our constraint.

[00:37] We could also have done it maybe even with stars of...or that was director ID of 0but we want stars of 0That's a shame, isn't it? Why don't we go ahead and delete from movies where the ID is 4, 5, or 6. We'll get rid of our Barton Finks and we'll add a constraint.

[01:03] Alter table, movies. We'll add a constraint. We have to name it, as always. We'll say CountStars greater than 0This is a check constraint, and then, we have to put the check in parentheses, so we'll say CountStars is greater than 0That will force us to do that. We'll also say that it's less than 6, because we don't want six stars for anything.

[01:34] We'll back up a bit and just get ourselves to the point where we had run this. Let's try it with 6 and that fails. Let's try it with 5. That's OK. Let's try it with 0That fails.

[01:48] Try it with 1. That's OK. Let's try it with an arbitrarily high number. 10. -1. We already have started to see that our constraints have fallen into place, and these are constraints that we define for our table.

Enforce Custom Logic with Check Constraints in Postgres

Share this video with your friends

Course

Get Started With PostgreSQL