1. The not-null and unique constraints
In the last part of this chapter, you'll get to know two special attribute constraints: the not-null and unique constraints.
2. The not-null constraint
As the name already says, the not-null constraint disallows any "NULL" values on a given column. This must hold true for the existing state of the database, but also for any future state. Therefore, you can only specify a not-null constraint on a column that doesn't hold any "NULL" values yet. And: It won't be possible to insert "NULL" values in the future.
3. What does NULL mean?
Before I go on explaining how to specify not-null constraints, I want you to think about "NULL" values. What do they actually mean to you? There's no clear definition. "NULL" can mean a couple of things, for example, that the value is unknown, or does not exist at all. It can also be possible that a value does not apply to the column. Let's look into an example.
4. What does NULL mean? An example
Let's say we define a table "students". The first two columns for the social security number and the last name cannot be "NULL", which makes sense: this should be known and apply to every student. The "home_phone" and "office_phone" columns though should allow for null values – which is the default, by the way. Why? First of all, these numbers can be unknown, for any reason, or simply not exist, because a student might not have a phone. Also, some values just don't apply: Some students might not have an office, so they don't have an office phone, and so forth. So, one important take away is that two "NULL" values must not have the same meaning. This also means that comparing "NULL" with "NULL" always results in a "FALSE" value.
5. How to add or remove a not-null constraint
You've just seen how to add a not-null constraint to certain columns when creating a table. Just add "not null" after the respective columns. But you can also add and remove not-null constraints to and from existing tables.
To add a not-null constraint to an existing table, you can use the "ALTER COLUMN SET NOT NULL" syntax as shown here. Similarly, to remove a not-null constraint, you can use "ALTER COLUMN DROP NOT NULL".
6. The unique constraint
The unique constraint on a column makes sure that there are no duplicates in a column. So any given value in a column can only exist once. This, for example, makes sense for university short names, as storing universities more than once leads to unnecessary redundancy. However, it doesn't make sense for university cities, as two universities can co-exist in the same city.
Just as with the not-null constraint, you can only add a unique constraint if the column doesn't hold any duplicates before you apply it.
7. Adding unique constraints
Here's how to create columns with unique constraints. Just add the "UNIQUE" keyword after the respective table column. You can also add a unique constraint to an existing table. For that, you have to use the "ADD CONSTRAINT" syntax. This is different from adding a "NOT NULL" constraint. However, it's a pattern that frequently occurs. You'll see plenty of other examples of "ADD CONSTRAINT" in the remainder of this course.
8. Let's apply this to the database!
Okay, time now to apply the not-null and unique constraints to some of the columns of your current university affiliations database.