表级约束

最新推荐文章于 2025-07-24 20:01:35 发布

原创最新推荐文章于 2025-07-24 20:01:35 发布 · 1k 阅读

5 ·

CC 4.0 BY-SA版权

文章标签：

#mysql

#添加表级约束
/*
语法：在各个字段的最下面
【constraint 约束名】 约束类型(字段名)

*/
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 ck CHECK(gender='男' OR gender='女'),
       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),
       CHECK(gender='男' OR gender='女'),
       FOREIGN KEY(majorid) REFERENCES major(id)
      
);
SHOW INDEX FROM stuinfo;
#通用的写法：
CREATE TABLE IF NOT EXISTS stuinfo(
        id INT PRIMARY KEY,
        stuname VARCHAR(20) NOT NULL,
        sex CHAR(1),
        age INT DEFAULT 18,
        seat INT UNIQUE,
        majorid INT,
        CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)

);