SQL/MySQL/Select Clause/Unique

Alter table: add unique

   <source lang="sql">

/* 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;

      </source>
   
  

Create table: unique value and value from combined columns

   <source lang="sql">

/* 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;

      </source>
   
  

Setting a Unique Constraint

   <source lang="sql">

/* 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;

      </source>