The next thing I'll show you is that we can set up our own constraints that use multiple columns.
So this example here has a constraint that makes sure that the name and address combination are unique.
So in the company's table, the name itself doesn't have to be unique because companies could have the
same name, and the address doesn't have to be unique because there may be multiple companies housed
in the same building, the same office, for example, or run from the same home.
But the combination of the two is what needs to be unique name and address.
So let me show you this.
If I try running it and I think I'll tweak it to just make it simpler.
So let's not even bother with an ID, Let's not bother with a phone number or primary key.
Let's just do name and address, neither of which can be null.
But they don't have to be unique on their own.
They have to be unique as a combination.
So let's create this table and then let's insert into companies name and address.
And I'm going to keep names and addresses super simple.
Our first name will be Blackbird Auto.
Okay, sure.
And then address will just be one, two, three Spruce Street OC.
That works fine.
No problem.
Now, if I did the same thing, but this time maybe there's a different company located at one, two,
three spruce called Luigi's Pies.
Also fine.
So one, two, three.
Spruce is duplicated, but that doesn't matter.
However, if I then have some business like Luigi's Pies at 1 to 3 spruce, which is already in there,
so the same business name at the same location, we get an error duplicate entry.
Luigi's Pies.
One, two, three spruce for this key name address.
So I could also give this a better name instead of just name address.
I could do name, address, combo, unique name address, something like that.
Now this is using unique.
This is not a check constraint, right?
This is using an existing constraint like unique.
We can also do our own custom check constraints that use multiple columns.
Like let's say that we flip houses so create table houses and we keep track of the price we bought a
house for.
So the purchase price is let's just make it an integer.
I guess we could worry about sense as well, but let's just keep it as an integer.
Just to make this short and simple.
It can't be null.
And then we'll have the sale price, which is also an integer, can't be null.
And I can add my own constraint that says the sale price has to be greater than the purchase price.
Technically, you could sell a house and lose money.
It happens all the time.
But let's just say for this example, our system only lets you set a sale price that is greater than
or equal to what you purchased it for.
So this is a multi column check constraint.
I would do constraint, come up with some name like I don't know how about just purchase price or rather
sale price greater than purchase price.
I mean that is confusing to look at.
But just for the sake of brevity, we'll start with that.
Or maybe se price greater than p price.
Sure.
And then we write check.
And then what are we checking?
We're checking to see that purchase price is less than or equal to sale price.
Although to be consistent, I would reverse that and say sale price has to be greater than or equal
to purchase price only because that's the name of this constraint.
I just think it makes more logical sense to keep that in the same order.
Okay, so let's make this table.
And then if I insert into houses, purchase price first, sell price, second values, and let's do
really simple prices.
Like we bought the house for $100, we sold it for $200.
Great profit.
Okay, that works.
But if we bought the house for five or $300 and we sold it for $250, oh no.
We get an error check constraint sale price greater than purchase price is violated.
So just another example of a check constraint in this example used two columns and you're not limited
to simple comparisons like greater than, although that is what is probably most common.
Comparing equality greater than less than.
But as we saw, you can do things like use string functions and date functions and time functions and
all of that.
So you could write a constraint to see that a birthday came before some data, after some date, or
that a time is between one, I don't know, noon in five or it's during the workday, it's between 8
a.m. and five or whatever your workday is.
So anyway, you can write constraints, give them names.
It's pretty powerful.
That's it for constraints.
Next up, we're going to move on to Alter Table.