Join egghead, unlock knowledge.

Want more egghead?

This lesson is for members. Join us? Get access to all 3,000+ tutorials + a community with expert developers around the world.

Unlock This Lesson

Already subscribed? Sign In

Autoplay

    Enforce Custom Logic with Check Constraints in Postgres

    Brett CassetteBrett Cassette

    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.

    postgresPostgreSQL
    Code

    Code

    Become a Member to view code

    You must be a Member to view code

    Access all courses and lessons, track your progress, gain confidence and expertise.

    Become a Member
    and unlock code for this lesson
    Transcript

    Transcript

    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.

    Discuss

    Discuss