常用约束

1.默认值约束

CREATE TABLE nstudent(
    id INT,
    NAME VARCHAR(20),
    -- 默认值约束
    address VARCHAR(20) DEFAULT '无锡国家软件园'  
) ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO nstudent(id,NAME) VALUES(100,'张三');
SELECT * FROM nstudent;

2.非空约束

CREATE TABLE nstudent(
    id INT,
    NAME VARCHAR(20),
    -- gender字段不能为null
    gender VARCHAR(3) NOT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO nstudent(id,NAME) VALUES(100,'占山');

3.唯一约束 UNIQUE 指定字段的value值只能由一个，但是可以由多个null

CREATE TABLE nstudent(
    id INT,
    NAME VARCHAR(20) UNIQUE,
    gender VARCHAR(3)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO nstudent(id,NAME) VALUES(100,'占山');
INSERT INTO nstudent(id,NAME) VALUES(100,NULL);
INSERT INTO nstudent(id,NAME) VALUES(100,NULL);

4.主键约束 PRIMARY KEY = 非空 + 唯一 主键约束一般情况下是添加在id字段上面

CREATE TABLE nstudent(
    id INT PRIMARY KEY,
    NAME VARCHAR(20),
    gender VARCHAR(3)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO nstudent(id,NAME) VALUES(100,'占山');
INSERT INTO nstudent(id,NAME) VALUES(101,'占山');

5.自增长约束 AUTO_INCREMENT 一般情况下是添加在主键上面

CREATE TABLE nstudent(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20),
    gender VARCHAR(3)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO nstudent(NAME) VALUES('占山');
INSERT INTO nstudent(NAME) VALUES('占山');
INSERT INTO nstudent(NAME) VALUES('占山');
INSERT INTO nstudent(NAME) VALUES('占山');
SELECT * FROM nstudent;