文章目录
1.完整性约束条件测试
1.1完整性约束条件有哪些
PRIMARY KEY 主键
AUTO_INCREMENT 自增长
FOREIGN KEY 外键
NOT NULL 非空
UNIQUE KEY 唯一
DEFAULT 默认值
1.1.1主键
主键:唯一标识符,身份证号和人的关系就相当于主键和记录的关系
一般加到无意义的字段上例如:编号字段
定义主键字段的要求:不能重复、被标志成主键的字段自动非空
分为单字段主键和多字段主键
--测试主键
CREATE TABLE IF NOT EXISTS user1(
id INT PRIMARY KEY,
username VARCHAR(20)
);
Query OK, 0 rows affected (0.09 sec)
DESC user1;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| username | varchar(20) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.04 sec)
--查看创建表的定义
SHOW CREATE TABLE user1;
+-------+----------------------------------------------------------------------------
---------------------------------------------------------+
| Table | Create Table
|
+-------+----------------------------------------------------------------------------
---------------------------------------------------------+
| user1 | CREATE TABLE `user1` (
`id` int(11) NOT NULL,
`username` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------
---------------------------------------------------------+
1 row in set (0.00 sec)
--主键值的唯一性
INSERT user1 VALUES(2,'QUEEN');
Query OK, 1 row affected (0.01 sec)
INSERT user1 VALUES(1,'KING');
Query OK, 1 row affected (0.00 sec)
INSERT user1 VALUES(1,'KING');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
--查看记录
SELECT * FROM user1;
+----+----------+
| id | username |
+----+----------+
| 1 | KING |
| 2 | QUEEN |
+----+----------+
2 rows in set (0.00 sec)
--查看id为1的记录
SELECT * FROM user1 WHERE id=1;
+----+----------+
| id | username |
+----+----------+
| 1 | KING |
+----+----------+
1 row in set (0.01 sec)
--设置两个主键
mysql> CREATE TABLE IF NOT EXISTS userb(
-> id INT,
-> username VARCHAR(20),
-> card CHAR(18),
-> PRIMARY KEY (id,card)
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> DESC userb;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | 0 | |
| username | varchar(