Create创建表-约束的使用_create 表约束 m 0-优快云博客

本文深入讲解SQL中的五种主要约束：主键、外键、条件、唯一性和非空约束。通过具体实例，演示如何在创建表时及后续修改中添加、删除这些约束，帮助读者掌握数据库表结构的设计与优化。

主键约束

添加主键约束

CREATE TABLE categoryinfo
(CategoryId varchar2(10),
CategoryName varchar2(20),
PRIMARY KEY (CategoryId)
);

修改表时添加主键约束

CREATE TABLE categoryinfo
(CategoryId varchar2(10),
CategoryName varchar2(20)
);

ALTER TABLE categoryinfo
ADD CONSTRAINTS pk_category PRIMARY KEY(CategoryId);

删除主键约束

ALTER TABLE categoryinfo
DROP CONSTRAINT pk_category;

外键约束

添加外键约束

CREATE TABLE t_productinfo1
(ProductId varchar2(10),
ProductName varchar2(20),
ProductPrice number(8,2),
Quantity number(10),
Category varchar2(10),
Desperation varchar2(1000),
Origin varchar2(10),
PRIMARY KEY (ProductId),
CONSTRAINT fk_pro FOREIGN KEY(Category)
REFERENCES categoryinfo(CategoryId)
ON DELETE CASCADE
);

修改表时添加外键约束

ALTER TABLE t_productinfo1
ADD CONSTRAINT fk_pro FOREIGN KEY(Category)
REFERENCES categoryinfo(CategoryId)
ON DELETE CASCADE;

删除外键约束

ALTER TABLE t_productinfo1
DROP CONSTRAINT fk_pro;

条件约束

添加约束

CREATE TABLE custominfo
(CustomId varchar2(10),
Name varchar2(10),
Age number(2),
Gender varchar2(2),
Tel varchar2(11),
Address varchar2(100),
CONSTRAINT CHK_AGE CHECK(Age>=18 AND Age<=30)
);

ALTER TABLE custominfo
ADD CONSTRAINT chk_gender CHECK(Gender='男' or Gender='女');

移除约束

ALTER TABLE custominfo
DROP CONSTRAINT chk_gender;

唯一性约束

添加约束

CREATE TABLE orderinfo
(OrderId varchar2(10),
CustomId varchar2(10),
ProductId varchar2(10),
OrderDate varchar2(10),
OrderQuantity number(10),
SendDate varchar2(10),
CONSTRAINT UNQ_ORDERID UNIQUE(OrderId)
);

移除约束

ALTER TABLE orderinfo
ADD CONSTRAINT UNQ_CUSTOMID UNIQUE(CustomId);

ALTER TABLE orderinfo
DROP CONSTRAINT UNQ_CUSTOMID;

非空约束


CREATE TABLE managerinfo
(ManagerID varchar2(10),
LoginName varchar2(10) NOT NULL,
LoginPassword varchar2(10) NOT NULL,
Name varchar2(10),
Tel varchar2(11)
);

ALTER TABLE managerinfo
MODIFY Name NOT NULL;