SQL/MySQL/Select Clause/Unique

Alter table: add unique

/*
mysql> Drop table Product;
mysql> CREATE TABLE Product
    -> (
    ->    ID SMALLINT UNSIGNED NOT NULL PRIMARY KEY,
    ->    ModelID SMALLINT UNSIGNED NOT NULL
    -> );
Query OK, 0 rows affected (0.05 sec)
mysql> ALTER TABLE Product
    -> ADD UNIQUE (ID, ModelID);
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> Describe Product;
+---------+----------------------+------+-----+---------+-------+
| Field   | Type                 | Null | Key | Default | Extra |
+---------+----------------------+------+-----+---------+-------+
| ID      | smallint(5) unsigned |      | PRI | 0       |       |
| ModelID | smallint(5) unsigned |      |     | 0       |       |
+---------+----------------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

*/
Drop table Product;

CREATE TABLE Product
(
   ID SMALLINT UNSIGNED NOT NULL PRIMARY KEY,
   ModelID SMALLINT UNSIGNED NOT NULL
);
ALTER TABLE Product
ADD UNIQUE (ID, ModelID);
Describe Product;

Create table: unique value and value from combined columns

/*
mysql> Drop table Product;
mysql> CREATE TABLE Product
    -> (
    ->    ID SMALLINT UNSIGNED NOT NULL,
    ->    ModelID SMALLINT UNSIGNED NOT NULL,
    ->    ModelDescrip VARCHAR(40),
    ->    PRIMARY KEY (ID),
    ->    UNIQUE (ID, ModelID)
    -> );
Query OK, 0 rows affected (0.06 sec)
mysql> Describe Product;
+--------------+----------------------+------+-----+---------+-------+
| Field        | Type                 | Null | Key | Default | Extra |
+--------------+----------------------+------+-----+---------+-------+
| ID           | smallint(5) unsigned |      | PRI | 0       |       |
| ModelID      | smallint(5) unsigned |      |     | 0       |       |
| ModelDescrip | varchar(40)          | YES  |     | NULL    |       |
+--------------+----------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

*/
Drop table Product;
  
CREATE TABLE Product
(
   ID SMALLINT UNSIGNED NOT NULL,
   ModelID SMALLINT UNSIGNED NOT NULL,
   ModelDescrip VARCHAR(40),
   PRIMARY KEY (ID),
   UNIQUE (ID, ModelID)
);
Describe Product;

Setting a Unique Constraint

/*
mysql> Drop TABLE Employee;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE Employee (
    ->    Name    VARCHAR(50) NOT NULL,
    ->    PhoneNo VARCHAR(15) DEFAULT "Unknown Phone" NOT NULL,
    ->    CONSTRAINT MyUniqueKey UNIQUE (Name));
Query OK, 0 rows affected (0.07 sec)
mysql> Describe Employee;
+---------+-------------+------+-----+---------------+-------+
| Field   | Type        | Null | Key | Default       | Extra |
+---------+-------------+------+-----+---------------+-------+
| Name    | varchar(50) |      | PRI |               |       |
| PhoneNo | varchar(15) |      |     | Unknown Phone |       |
+---------+-------------+------+-----+---------------+-------+
2 rows in set (0.00 sec)
mysql> INSERT INTO Employee (Name, Phone) VALUES ("Joe Wang", "666 2323");
ERROR 1054 (42S22): Unknown column "Phone" in "field list"
mysql> INSERT INTO Employee (Name) VALUES ("John Doe");
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO Employee (Name, Phone) VALUES ("Joe Wang", NULL);
ERROR 1054 (42S22): Unknown column "Phone" in "field list"
mysql> Select * from Employee;
+----------+---------------+
| Name     | PhoneNo       |
+----------+---------------+
| John Doe | Unknown Phone |
+----------+---------------+
1 row in set (0.00 sec)

*/
Drop TABLE Employee;       
       
CREATE TABLE Employee (
   Name    VARCHAR(50) NOT NULL, 
   PhoneNo VARCHAR(15) DEFAULT "Unknown Phone" NOT NULL,
   CONSTRAINT MyUniqueKey UNIQUE (Name));
Describe Employee;
INSERT INTO Employee (Name, Phone) VALUES ("Joe Wang", "666 2323");
INSERT INTO Employee (Name) VALUES ("John Doe");
INSERT INTO Employee (Name, Phone) VALUES ("Joe Wang", NULL);
Select * from Employee;