When it comes to unique constraints, most people's brains go automatically to primary key, which is good. Primary keys must be unique in addition to being not null, but you can add a unique constraint to a different column or to multiple columns, and so we'll take a look at that now. Declaring this as a primary key, like I said, does two things, not null and unique. That's the two constraints that we've talked about so far, and primary key handles both of those.

Now, when it comes to something else, you can say that this is text, and it must be unique. If we run that, and then we insert into products values of, I'm just gonna put DEFAULT here for the primary key. We made that text, so we can put ABC123, and we can see, say, ABC New product. We'll capitalize it, ABC New Product, and it costs 9.99. If we run that, we see that it works. If we run it again, we get our error back. Product number ABC123 already exists. Pretty cool. Here's something. If we were to... We did not declare. We did not declare this as a not null column. If we were to run that, huh, that's interesting. We declared. We said that that product number column must be unique. So, select * from products, and look, boy, that feels like duplicates to me, doesn't it?

Well, remember when we said nulls are weird? Null = null. Null, dunno. Does this secret hidden value equal this secret hidden value? Dunno. Impossible to say. So you have two options here, provided this is not what you want. This might be exactly what you want. You might want all nulls to be totally distinct from each other, and you can put in as many nulls as you want, but as soon as you put a value in, it must be unique. Totally valid use case. I'm not saying that one is right or wrong. Provided you don't like this, you do have two options. Let's go ahead and drop table products. We'll get rid of that guy. Products does not exist. It's already gone. Product number, you can just say not null.

So you can make it not null unique, and then you're gonna be stopped at the front door. Let's drop this again, and we'll say unique. Here's your other option. You can say unique nulls not distinct. And if you run that, and then you insert that, that is interesting. You are allowed to insert a null value, but you cannot insert two because now nulls are not distinct. We're treating nulls as if they're equal, which is odd behavior. It's nonstandard behavior. But we have explicitly declared that nulls are not distinct. This is a kind of an interesting use case.

If you declare it not null, you'll never get a null in the table at all. If you declare it nullable with nulls not distinct, you are allowed one, you are allowed one sweet, sweet null value. And that might be exactly what you're looking for. It can be pretty useful. You might have one unknown, and the rest are all filled out being distinct. I wanna show you two more things. We're gonna drop this table, and then we're gonna get rid of that, and we'll go ahead and make this text not null. And let's say that we have a brand up here of text not null as well. You can put a uniqueness constraint over to columns by saying unique(brand, product_number). Now we need a comma. Now what's gonna happen is, let's come down here and say that this is a brand of ABC, and it is a product number of 123. That totally works. And then we want, let's say DEF of 123. That totally works.

But if we go back to ABC of 123, here you see that our composite unique already exists. If we look at this, you see, well, there are two 123s. In fact, let's put two ABCs in there. There are two ABCs. There are two 123s. But the combination, the combination of the two must be unique. That's really cool. And the other thing that I wanted to show you down here is you can declare a uniqueness constraint as a table constraint.

So even if you just did... Even if you just did the one column, so we can drop the table, you can still put it down there as a table constraint. That's totally fine. You can also give it... Let's drop it again. You can also give it a... You can give it a name by doing constraint must_be_unique unique. I don't know that that adds very much, not in my opinion. I think the error message was totally fine in the first place. But you see, "Violates unique constraint 'must_be_unique.'" Maybe that's a poorly chosen name on my part, but if you wanted to give it a name, you could.

If you wanted to make it a table constraint, you could. If you wanted to make it across multiple columns, you could. If you wanna make nulls distinct, you could. You have a lot of options with unique. Remember that primary key does declare that column as not null and unique, but you are free to add unique constraints on any other column or set of columns in your table.