SQL/MySQL/Table Index/Constraint

Setting CHECK Constraints

   <source lang="sql">

/* 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",
   ->    Age     INT CHECK (Age BETWEEN 20 and 30));

Query OK, 0 rows affected (0.07 sec) mysql> Describe Employee; +---------+-------------+------+-----+---------------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------------+-------+ | Name | varchar(50) | | PRI | | | | PhoneNo | varchar(15) | YES | | Unknown Phone | | | Age | int(11) | YES | | NULL | | +---------+-------------+------+-----+---------------+-------+ 3 rows in set (0.00 sec) mysql> INSERT INTO Employee (Name, Phone, Age) VALUES ("Joe Wang", "666 2323", 26 ); ERROR 1054 (42S22): Unknown column "Phone" in "field list" mysql> INSERT INTO Employee (Name, Age) VALUES ("John Doe", 31); 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 | Age | +----------+---------------+------+ | John Doe | Unknown Phone | 31 | +----------+---------------+------+ 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",
  Age     INT CHECK (Age BETWEEN 20 and 30));

Describe Employee; INSERT INTO Employee (Name, Phone, Age) VALUES ("Joe Wang", "666 2323", 26); INSERT INTO Employee (Name, Age) VALUES ("John Doe", 31); INSERT INTO Employee (Name, Phone) VALUES ("Joe Wang", NULL); Select * from Employee;

      </source>
   
  

Setting CHECK Constraints: age between

   <source lang="sql">

/* mysql> Drop TABLE Employee; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE Employee (

   ->    Name    VARCHAR(50) PRIMARY KEY NOT NULL,
   ->    Phone   VARCHAR(15) DEFAULT "Unknown Phone",
   ->    Age     INT,
   ->    CONSTRAINT CheckAge CHECK (Age BETWEEN 20 and 30));

Query OK, 0 rows affected (0.05 sec) mysql> Describe Employee; +-------+-------------+------+-----+---------------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------------+-------+ | Name | varchar(50) | | PRI | | | | Phone | varchar(15) | YES | | Unknown Phone | | | Age | int(11) | YES | | NULL | | +-------+-------------+------+-----+---------------+-------+ 3 rows in set (0.00 sec) mysql> INSERT INTO Employee (Name, Phone, Age) VALUES ("Joe Wang", "666 2323", 26 ); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO Employee (Name, Age) VALUES ("John Doe", 31); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO Employee (Name, Phone) VALUES ("Joe Wang", NULL); ERROR 1062 (23000): Duplicate entry "Joe Wang" for key 1 mysql> Select * from Employee; +----------+---------------+------+ | Name | Phone | Age | +----------+---------------+------+ | Joe Wang | 666 2323 | 26 | | John Doe | Unknown Phone | 31 | +----------+---------------+------+ 2 rows in set (0.00 sec)

• /

Drop TABLE Employee; CREATE TABLE Employee (

  Name    VARCHAR(50) PRIMARY KEY NOT NULL, 
  Phone   VARCHAR(15) DEFAULT "Unknown Phone",
  Age     INT,
  CONSTRAINT CheckAge CHECK (Age BETWEEN 20 and 30));
 

Describe Employee; INSERT INTO Employee (Name, Phone, Age) VALUES ("Joe Wang", "666 2323", 26); INSERT INTO Employee (Name, Age) VALUES ("John Doe", 31); INSERT INTO Employee (Name, Phone) VALUES ("Joe Wang", NULL); Select * from Employee;

      </source>
   
  

Setting CHECK Constraints: character by character

   <source lang="sql">

/* 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",
   ->    SSN     VARCHAR(15) NOT NULL,
   ->    CHECK (SSN LIKE "[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]"));

Query OK, 0 rows affected (0.07 sec) mysql> Describe Employee; +---------+-------------+------+-----+---------------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------------+-------+ | Name | varchar(50) | | PRI | | | | PhoneNo | varchar(15) | YES | | Unknown Phone | | | SSN | varchar(15) | | | | | +---------+-------------+------+-----+---------------+-------+ 3 rows in set (0.01 sec) mysql> INSERT INTO Employee (Name, Phone, SSN) VALUES ("Joe Wang", "666 2323", 22 2-22-2222); ERROR 1054 (42S22): Unknown column "Phone" in "field list" mysql> INSERT INTO Employee (Name, SSN) VALUES ("John Doe", 22-a2-2222); ERROR 1054 (42S22): Unknown column "a2" in "field list" mysql> INSERT INTO Employee (Name, Phone) VALUES ("Joe Wang", NULL); ERROR 1054 (42S22): Unknown column "Phone" in "field list" mysql> Select * from Employee; Empty set (0.00 sec)

• /

Drop TABLE Employee;

CREATE TABLE Employee (

  Name    VARCHAR(50) PRIMARY KEY NOT NULL, 
  PhoneNo VARCHAR(15) DEFAULT "Unknown Phone",
  SSN     VARCHAR(15) NOT NULL,
  CHECK (SSN LIKE "[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]"));

Describe Employee; INSERT INTO Employee (Name, Phone, SSN) VALUES ("Joe Wang", "666 2323", 222-22-2222); INSERT INTO Employee (Name, SSN) VALUES ("John Doe", 22-a2-2222); INSERT INTO Employee (Name, Phone) VALUES ("Joe Wang", NULL); Select * from Employee;

      </source>