数据库_days2_完整性约束条件测试

完整性约束条件测试

• PRIMARY KEY 主键
• AUTO_INCREMENT 自增长
• FOREIGN KEY 外键
• NOT NULL 非空
• UNIQUE KEY 唯一
• DEFAULT 默认值

测试主键

CREATE TABLE IF NOT EXISTS user1(
id INT PRIMARY KEY,
username VARCHAR(20)
);
SHOW CREATE TABLE user1;
INSERT user1 VALUES(1,'king');
INSERT user1 VALUES(13,'QUEEN');
SELECT * FROM user1;
DESC user1;

设置两个主键

CREATE TABLE IF NOT EXISTS user2(
id INT,
username VARCHAR(20),
card CHAR(18),
PRIMARY KEY(id,card)
);
INSERT user2 VALUES(1,'king','111');
INSERT user2 VALUES(1,'queen','112');
SELECT * FROM user2;
DESC user2;

测试自增长

初始位置开始

CREATE TABLE IF NOT EXISTS user5(
id SMALLINT KEY AUTO_INCREMENT,
username VARCHAR(20)
);
INSERT user5 VALUES(1,'KING');
INSERT user5(username) VALUES('queen1');
INSERT user5(username) VALUES('queen1');
INSERT user5 VALUES(111,'KING1');
INSERT user5 VALUES(NULL,'AAAA');
INSERT user5 VALUES(DEFAULT,'AAAA');
SELECT * FROM user5;

修改自增长值

CREATE TABLE IF NOT EXISTS user6(
id SMALLINT KEY AUTO_INCREMENT,
username VARCHAR(20)
)AUTO_INCREMENT=100;
INSERT user6(username) VALUES('queen1');
#修改自增长的值
ALTER TABLE user6 AUTO_INCREMENT =500;
INSERT user6(username) VALUES('quee');
SELECT*FROM user6;

测试非空NOT NULL

CREATE TABLE IF NOT EXISTS user7(
id INT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
password CHAR(32) NOT NULL,
age TINYINT UNSIGNED
);
INSERT user7(username,password) VALUES('KING','KING');
SELECT * FROM user7;

INSERT user7(username,password) VALUES('ws',NULL);
SELECT * FROM user7;

[SQL]INSERT user7(username,password) VALUES('ws',NULL);
[Err] 1048 - Column 'password' cannot be null

测试默认值DEFAULT

CREATE TABLE IF NOT EXISTS user8(
id INT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
password CHAR(32) NOT NULL,
age TINYINT UNSIGNED DEFAULT 18,
addr VARCHAR(50) NOT NULL DEFAULT '北京',
sex ENUM('男','女','保密') NOT NULL DEFAULT '男'
); 
INSERT user8(username,password) VALUES('KING','KING');
SELECT * FROM user8;

INSERT user8 VALUES(3,'QUEEN','QUEEN',29,'上海','保密');
SELECT * FROM user8;

测试唯一UNIQUE KEY

CREATE TABLE IF NOT EXISTS user9(
id TINYINT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL UNIQUE,
card CHAR(18) UNIQUE
);
desc user9;
INSERT user9(username) VALUES('A');
INSERT user9(username,card) VALUES('B','111');
SELECT * FROM user9;

mysql> INSERT user9(username,card) VALUES('B','112');
ERROR 1062 (23000): Duplicate entry 'B' for key 'username'

注意：NULL不算重复