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 |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+