Setting CHECK Constraints: character by character : Constraint « Table Index « SQL / MySQL

1. SQL / MySQL
2. Table Index
3. Constraint

Setting CHECK Constraints: character by character

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


           
         
  

Related examples in the same category