Need clarity of UNIQUE and DEFAULT Constraints in SQL

Question

So I just started learning SQL online and while learning about constraints, below example was given for using DEFAULT constraint:

CREATE TABLE persons(
                    ID INT  NULL DEFAULT 100,
                    f_name VARCHAR(25),
                    l_name VCARCHAR(25),
                    UNIQUE(ID)
                    );

My question is, if ID is defaulted to 100, there can be multiple columns having 100 as ID, so wouldn't that contradict UNIQUE constraint, which ensures all columns to have different values?

Thank you for reading and your inputs!

Rohan

Answer 1

Though it's valid SQL and mysql allows this, it is a bad practice to define DEFAULT value on an column with UNIQUE constraint. This poor schema will lead to inconsistency in your data.

mysql> show create table persons;
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                           |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| persons | CREATE TABLE `persons` (
  `id` int(11) DEFAULT '100',
  `f_name` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `l_name` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Answer 2

You are right, the combination of DEFAULT 100 and UNIQUE makes no sense.

The column is defined as nullable, so there can be many rows with the value null. Only when a row has a value different from null, must it be unique.

In order to insert nulls, you'd explicitely have this in your INSERT statement. If you don't set null explicitly, the default 100 will be written. This works for the first row treated that way, but the second time the 100 will violate the unique constraint, just as you say.

Well, a nullable ID makes no sense either, and ideally an ID should be auto-incremented, so you don't have to worry about using an unused ID, especially in an environment where multiple processes may try to insert rows at the same time.

So, the given examle is just very bad.

Answer 3

The combination of DEFAULT 100 and UNIQUE makes sense.

This combination means that the newly inserted row should have explicitly specified ID column value primarily.

The scheme allows to insert one row without ID value specified. But only one row. If you need to insert another row with this default/generic ID value then you must edit existing row and alter its ID value previously (or delete it).

In practice - this allows to save raw, incomplete, row, and edit it completely in future. For example, you insert generic row, then calculate needed row parameters and set needed references, and finally you assign some definite ID value to this row. After this you may insert another generic row and work with it.

Of course this situation is rare. But it may be useful in some cases.