SQL/MySQL/Key/Primary Key

Define and use primary key

/*
mysql> Drop table Attributes;
mysql> CREATE TABLE Attributes
    -> (
    ->    ID SMALLINT NOT NULL PRIMARY KEY,
    ->    Settings TINYINT UNSIGNED NOT NULL
    -> );
Query OK, 0 rows affected (0.06 sec)
mysql> INSERT INTO Attributes VALUES (101, 58),
    ->                               (102, 73),
    ->                               (103, 45);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> select * from Attributes;
+-----+----------+
| ID  | Settings |
+-----+----------+
| 101 |       58 |
| 102 |       73 |
| 103 |       45 |
+-----+----------+
3 rows in set (0.00 sec)
*/
Drop table Attributes;
CREATE TABLE Attributes
(
   ID SMALLINT NOT NULL PRIMARY KEY,
   Settings TINYINT UNSIGNED NOT NULL
);

INSERT INTO Attributes VALUES (101, 58), 
                              (102, 73), 
                              (103, 45);
select * from Attributes;

Setting a Primary Key 1

/*
mysql> Drop TABLE Employee;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE Employee (
    ->    Name    VARCHAR(50) NOT NULL,
    ->    Phone   VARCHAR(15) DEFAULT "Unknown Phone" NOT NULL,
    ->    CONSTRAINT MyPrimaryKey PRIMARY KEY (Name));
Query OK, 0 rows affected (0.07 sec)
mysql> Describe Employee;
+-------+-------------+------+-----+---------------+-------+
| Field | Type        | Null | Key | Default       | Extra |
+-------+-------------+------+-----+---------------+-------+
| Name  | varchar(50) |      | PRI |               |       |
| Phone | varchar(15) |      |     | Unknown Phone |       |
+-------+-------------+------+-----+---------------+-------+
2 rows in set (0.01 sec)
mysql> INSERT INTO Employee (Name, Phone) VALUES ("Joe Wang", "666 2323");
Query OK, 1 row affected (0.02 sec)
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 1048 (23000): Column "Phone" cannot be null
mysql> Select * from Employee;
+----------+---------------+
| Name     | Phone         |
+----------+---------------+
| Joe Wang  | 666 2323      |
| John Doe | Unknown Phone |
+----------+---------------+
2 rows in set (0.00 sec)
*/
Drop TABLE Employee;
  
CREATE TABLE Employee (
   Name    VARCHAR(50) NOT NULL, 
   Phone   VARCHAR(15) DEFAULT "Unknown Phone" NOT NULL,
   CONSTRAINT MyPrimaryKey PRIMARY KEY (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;

Setting a Primary Key 2

/*
mysql> Drop TABLE Employee;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE Employee (
    ->    Name    VARCHAR(50) NOT NULL,
    ->    Phone VARCHAR(15) DEFAULT "Unknown Phone" NOT NULL,
    ->    PRIMARY KEY (Name));
Query OK, 0 rows affected (0.06 sec)
mysql> Describe Employee;
+-------+-------------+------+-----+---------------+-------+
| Field | Type        | Null | Key | Default       | Extra |
+-------+-------------+------+-----+---------------+-------+
| Name  | varchar(50) |      | PRI |               |       |
| Phone | varchar(15) |      |     | Unknown Phone |       |
+-------+-------------+------+-----+---------------+-------+
2 rows in set (0.00 sec)
mysql> INSERT INTO Employee (Name, Phone) VALUES ("Joe Wang", "666 2323");
Query OK, 1 row affected (0.00 sec)
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 1048 (23000): Column "Phone" cannot be null
mysql> Select * from Employee;
+----------+---------------+
| Name     | Phone         |
+----------+---------------+
| Joe Wang  | 666 2323      |
| John Doe | Unknown Phone |
+----------+---------------+
2 rows in set (0.00 sec)

*/
Drop TABLE Employee;
CREATE TABLE Employee (
   Name    VARCHAR(50) NOT NULL, 
   Phone VARCHAR(15) DEFAULT "Unknown Phone" NOT NULL,
   PRIMARY KEY (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;

Setting a Primary Key 3

/*
mysql> Drop TABLE Employee;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE Employee (
    ->    Name    VARCHAR(50) PRIMARY KEY NOT NULL,
    ->    PhoneNo VARCHAR(15) DEFAULT "Unknown Phone" NOT NULL);
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) PRIMARY KEY NOT NULL, 
   PhoneNo VARCHAR(15) DEFAULT "Unknown Phone" NOT NULL);

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;