в 13:46, 26 мая 2010

2010-05-26T13:46:00Z

Admin: 1 версия

2010-05-26T10:17:25Z

1 версия

Новая страница

==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>

← Предыдущая	Версия 13:46, 26 мая 2010
(нет различий)

SQL/MySQL/Table Index/Constraint - История изменений

в 13:46, 26 мая 2010

Admin: 1 версия