Ensure Uniqueness in Postgres

Brett Cassette
InstructorBrett Cassette
Share this video with your friends

Social Share Links

Send Tweet
Published 7 years ago
Updated 5 years ago

Let’s say we have a bank. Our bank wants to give each account for each user a unique name, for instance, “Personal” or “Checking.” How can we make sure each account has a unique name for each user?

[00:00] I'm going to talk about enforcing uniqueness constraints today. We're going to insert into Movies...Actually, let's just insert into Directors because it's going to be easier. Name and we'll see the values Quentin Tarantino. We saw this in the past.

[00:18] Oh, no, we have two Quentin Tarantinos in the database and that seems like a mistake, doesn't it? We already have the table. We're going to perform an alter table statement to add the uniqueness constraint.

[00:31] We're going to add a constraint with this uniqueness is a constraint. We are going to alter the Directors table actually, so the constraint is going to have a name as well. We'll name this Directors name unique. We're going to be unique over the name.

[00:52] We'll run that and it will tell us that we can't create the syntax because we already have a duplicate key name like Quentin Tarantino. We're going to delete from the Directors table where and equals four because that's the second Tarantino.

[01:10] I can see that he's gone now. If we add the constraint everything is going to go OK. It didn't report anything special to us. To really see this in action we're going to insert into Directors name values Quentin Tarantino.

[01:33] Now, we get this scary error, "Duplicate key violates this constraint." That's great. It tells us Quentin Tarantino already exists and we can't insert him into the database no matter how many times we try to do that.

[01:47] We can also do this constraint on table creating. I like to live on the wild side, so we'll drop the Directors table. Remember we should never do this in a production setting. Now, we can do a create table Directors ID serial primary key and our name is going to be Farchar. It's going to be unique, awesome.

[02:18] Let's insert into Directors name values Quentin and we'll try to insert two Quentins in this time. Already the second one is going to fail us, so we can't do that. We'll try doing it one time. That works fine. Second time doesn't work. Select star from directors. It's going to show us just one Quentin Tarantino.

[02:46] The Movies table will probably be a little different because there are some movies that have the same title. We don't really want to enforce this constraint on the Movies table instead we want to say, "Alter table movies, add constraint."

[03:03] Let's say, "Unique title and release." The combination of these two fields is what's going to be unique. We'll say its unique constraint and it's going to be title release date. That's really the primary key, so again it doesn't complain because we don't have any violations right now.

[03:22] If we insert into the Movies table, "Title release date count stars director ID." We're just going to give it the same thing Kill Bill and 10-10-2003. It doesn't matter what these other ones are. That's going to be upset because it violates this combination uniqueness constraint.

egghead
egghead
~ 24 minutes ago

Member comments are a way for members to communicate, interact, and ask questions about a lesson.

The instructor or someone from the community might respond to your question Here are a few basic guidelines to commenting on egghead.io

Be on-Topic

Comments are for discussing a lesson. If you're having a general issue with the website functionality, please contact us at support@egghead.io.

Avoid meta-discussion

  • This was great!
  • This was horrible!
  • I didn't like this because it didn't match my skill level.
  • +1 It will likely be deleted as spam.

Code Problems?

Should be accompanied by code! Codesandbox or Stackblitz provide a way to share code and discuss it in context

Details and Context

Vague question? Vague answer. Any details and context you can provide will lure more interesting answers!

Markdown supported.
Become a member to join the discussionEnroll Today