数据库约束-添加约束

列级约束

案例：

CREATE TABLE major(

 id INT PRIMARY key,

 majorName VARCHAR(20)

);

CREATE TABLE stuinfo(

 id INT PRIMARY key,

 stuName VARCHAR(20) NOT NULL,

 gender CHAR(1),

 seat INT UNIQUE,

 age INT DEFAULT 18,

 majorId INT REFERENCES major(id)

);

表级约束

案例

DROP TABLE IF EXISTS stuinfo;

CREATE TABLE stuinfo(

id INT,

stuName VARCHAR(20),

gender CHAR(1),

seat INT,

age INT,

majorid INT,

CONSTRAINT pk PRIMARY KEY(id),

CONSTRAINT uq UNIQUE(seat),

CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)

);

约束名可省略，省略后使用默认定义

CREATE TABLE stuinfo(

id INT,

stuName VARCHAR(20),

gender CHAR(1),

seat INT,

age INT,

majorid INT,

PRIMARY KEY(id),

UNIQUE(seat),

FOREIGN KEY(majorid) REFERENCES major(id)

);

SHOW INDEX FROM stuinfo;    # 查看索引

通用写法