mysql添加列级约束，修改约束

添加列级约束

主键接受列级约束和表级约束两种写法

#2.添加主键
列级约束
ALTER TABLE school MODIFY COLUMN id INT PRIMARY KEY;
#表级约束
ALTER TABLE school ADD PRIMARY KEY(id);

第一种方式

CREATE TABLE students(
id INT PRIMARY KEY, #主键
stuname VARCHAR(20) DEFAULT NULL,
seat INT UNIQUE, #唯一
gender CHAR(1) CHECK(gender='男' OR gender='
女'), #检查
magrorid INT REFERENCES major(id)); # 外键

CREATE TABLE major(
id INT PRIMARY KEY,
majorname VARCHAR(20));
SHOW INDEX FROM  students;

第二种方式

CREATE TABLE school(
id INT,
schname VARCHAR(20),
seat INT,
gender CHAR(1),
majorid INT,
CONSTRAINT pk PRIMARY KEY(id),
CONSTRAINT uq UNIQUE(seat),
CONSTRAINT ck CHECK(gender='男' OR gender='女'),
CONSTRAINT re_major FOREIGN KEY(majorid) REFERENCES major(id)
);

CREATE TABLE major(
id INT
);

修改表添加约束

#1.添加非空约束
alter table school modify column schname varchar(20) not null;
#2.添加默认约束
alter table school modify column age int default 18;
#2.添加主键
alter table school modify column id int primary key;

#4.添加唯一
#列级约束
ALTER TABLE school MODIFY COLUMN seat INT UNIQUE;
#表级约束
ALTER TABLE school ADD UNIQUE(seat);

修改表时添加标识列

ALTER TABLE id_identiy MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;