Great job! In the previous exercise, we introduced a column with a UNIQUE constraint on it. You can also enforce UNIQUEness in a column with a special kind of index. Given the following table ...
CREATE TABLE subway_station ( id integer PRIMARY KEY, line varchar(3), zone integer, name varchar(64), opened_year integer, passengers_daily integer );
... you can create a UNIQUE index on the column name in the following way:
CREATE UNIQUE INDEX name_unique_index ON subway_station(name);
As you can see, the only difference is that we use CREATE UNIQUE INDEX instead of CREATE INDEX. A UNIQUE index works just like a UNIQUE constraint – it makes sure no duplicate values are allowed in that column.
However, enforcing UNIQUEness with UNIQUE indexes isn't recommended. Even though this syntax exists, the vast majority of database developers use UNIQUE constraints instead.
We've recreated the table named invoice with the following definitions. Notice there's no constraint on the number column:
CREATE TABLE invoice ( id integer PRIMARY KEY, number varchar(18), issue_date date, customer_id integer, amount decimal(10, 2), currency char(3) );
Then, we created a UNIQUE index like this:
CREATE UNIQUE INDEX number_unique_index ON invoice(number);
Finally, we added a row:
INSERT INTO invoice (id, number) VALUES (1, 'GH43');
Now, try to add another row with a different id value but with the same number value.
As you can see, the query fails.
For instance, type:
INSERT INTO invoice (id, number) VALUES (2, 'GH43');
