Setting CHECK Constraints: age between : Constraint « Table Index « SQL / MySQL

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

Setting CHECK Constraints: age between

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



           
         
  

Related examples in the same category