Create创建表-约束的使用

最新推荐文章于 2024-09-30 11:29:14 发布

陈道长

最新推荐文章于 2024-09-30 11:29:14 发布

阅读量1.1k

点赞数

分类专栏： oracle 文章标签：约束

本文链接：https://blog.csdn.net/chen12302asd/article/details/82868910

版权

oracle 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

主键约束

添加主键约束

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;