Oracle 数据库表和约束条件的增删改查-阿里云开发者社区

Oracle 数据库表和约束条件的增删改查

2023-05-27 197

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

简介： Oracle 数据库表和约束条件的增删改查

1、创建数据表

1.1 语法

CREATE TABLE <表明>(

字段1 数据类型 [列级别约束条件] [默认值],

字段2 数据类型 [列级别约束条件] [默认值],

......

[表级别约束条件]

);

常用的数据:整数,浮点数,精确小数,二进制,日期/时间,字符串等数据类型

示例一

创建TB_EMP1表

CREATE TABLE TB_EMP1 (

ID NUMBER(11),

NAME VARCHAR2(25),

DEPTID NUMBER(11),

SALARY NUMBER(8,2)

);

1.2 查询表结构

DESC TB_EMP1;

2、主键约束语法 PRIMARY KEY CONSTRAINT

2.1 概念

主键约束: 数据唯一，并且不不允许空，分为单字段主键和多字段联合主键类似我们的身份证

2.2 单字段主键语法如下

2.2.1 字段名数据类型 PRIMARY KEY [默认值]

2.2.2 定义完所有列后采用以下语句

[CONSTRAINT <约束名>] PRIMARY KEY(字段名)

示例2

创建TB_EMP2表和TB_EMP3表，定义主键为ID

--2.2.1

CREATE TABLE TB_EMP2 (

ID NUMBER(11) PRIMARY KEY,

NAME VARCHAR2(25),

DEPTID NUMBER(11),

SALARY NUMBER(8,2)

);

--2.2.2

CREATE TABLE TB_EMP3 (

ID NUMBER(11),

NAME VARCHAR2(25),

DEPTID NUMBER(11),

SALARY NUMBER(8,2),

CONSTRAINT PK_ID PRIMARY KEY(ID)

);

`2.3 多字段联合主键`

[CONSTRAINT <约束名>] PRIMARY KEY(字段名1,字段2.....字段N)*/

示例3

CREATE TABLE TB_EMP4 (

ID NUMBER(11),

NAME VARCHAR2(25),

DEPTID NUMBER(11),

SALARY NUMBER(8,2),

CONSTRAINT PK_ID_NAME PRIMARY KEY(ID,NAME)

);

2.4 使用ALTER TABLE 语句为数据库修改主键约束和删除约束

语法如下

ALTER TABLE <表名> ADD [CONSTRAINTS <约束名称>] PRIMARY KEY(字段名称)

ALTER TABLE <表名> DROP CONSTRAINTS <约束名称>

示例4（主键和联合主键）

ALTER TABLE TB_EMP1 ADD CONSTRAINTS PK_ID1 PRIMARY KEY(ID,NAME);

ALTER TABLE TB_EMP1 DROP CONSTRAINTS SYS_C008827;

ALTER TABLE TB_EMP1 ADD PRIMARY KEY(ID,NAME);

2.5 查询表的约束

根据视图USER_CONSTRAINTS可以查询外键的类型

P 主键

R 外键

U 唯一

3、外键约束 FOREIGN KEY CONSTRAINT

3.1 概念

可以是空值，若不为空值时，必须等于另一个数据库中主键的某一个值,并且数据类型一致

主表:相关联字段中主键所在的表为主表

从表:相关联字段中外键所在的表为从表

3.2 语法如下

[CONSTRAINT <外键名>] FOREIGN KEY (字段) REFERENCES <主表名>(主键列)

示例5

CREATE TABLE TB_DEPT (

ID NUMBER(11) PRIMARY KEY,

NAME VARCHAR2(22),

LOCATION VARCHAR2(50)

);

CREATE TABLE TB_EMP5 (

ID NUMBER(11),

NAME VARCHAR2(25),

DEPTID NUMBER(11),

SALARY NUMBER(8,2),

CONSTRAINT ID2 PRIMARY KEY(ID),

CONSTRAINT FK_ID FOREIGN KEY(DEPTID) REFERENCES TB_DEPT(ID)

);

SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'TB_EMP5';

--删除数据表

DROP TABLE TB_EMP5;

--重新创建表经过测试语法[CONSTRAINT <外键名>] 是可以忽略不写

CREATE TABLE TB_EMP5 (

ID NUMBER(11),

NAME VARCHAR2(25),

DEPTID NUMBER(11),

SALARY NUMBER(8,2),

PRIMARY KEY(ID),

FOREIGN KEY(DEPTID) REFERENCES TB_DEPT(ID)

);

3.3 修改数据表时进行外键的添加

语法如下

ALTER TABLE <表名> ADD [CONSTRAINTS <约束名称>] FOREIGN KEY(外键约束的字段名称) REFERENCE <主表名>(字段名称)[ON DELETE CASCADE]

示例6

CREATE TABLE TB_EMP6 (

ID NUMBER(11),

NAME VARCHAR2(25),

DEPTID NUMBER(11),

SALARY NUMBER(8,2),

PRIMARY KEY(ID)

);

ALTER TABLE TB_EMP6 ADD CONSTRAINTS FK_ID2 FOREIGN KEY (DEPTID) REFERENCES TB_DEPT(ID);

3.4 移除外键约束

语法如下

ALTER TABLE <表名> DROP CONSTRAINTS <约束名称>

示例7

ALTER TABLE TB_EMP6 DROP CONSTRAINTS FK_ID2;

3.5 修改约束键名称

语法如下

ALTER TABLE <表名> RENAME CONSTRAINTS <旧约束名> TO <新约束名>

示例8

ALTER TABLE TB_EMP5 RENAME CONSTRAINTS TEST1 TO TEST2;

4、唯一约束 UNIQUE CONSTRAINT

4.1 概念

要求该列唯一,允许为空,可以确保一列或者多列

4.2 语法如下

4.2.1 在定义好的列之后直接指定唯一约束

字段名数据类型 UNIQUE

4.2.2 在指定的所有列之后指定唯一约束

[CONSTRAINT <约束名>] UNIQUE (字段1,字段2...字段N)

多字段联合唯一保证是两个字段值不能相同可以AB AA BA BB,但是不能两个字段都相同

示例9

--4.2.1

CREATE TABLE TB_DEPT1 (

ID NUMBER(11) PRIMARY KEY,

NAME VARCHAR2(22) UNIQUE,

LOCATION VARCHAR2(50)

);

--测试插入两台NAME字段相同的值

INSERT INTO TB_DEPT1 VALUES(1,'1','1');

INSERT INTO TB_DEPT1 VALUES(2,'1','1');--ORA-00001:违反唯一约束条件(TEST.SYS_C008835) SYS_C008835 约束名称

SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'TB_DEPT1'; --SYS_C008835

INSERT INTO TB_DEPT1 VALUES(2,NULL,'1');

INSERT INTO TB_DEPT1 VALUES(3,NULL,'1');

INSERT INTO TB_DEPT1 VALUES(4,NULL,'1');

select * from TB_DEPT1;

desc TB_DEPT1;

-- 4.2.2

CREATE TABLE TB_DEPT2 (

ID NUMBER(11) PRIMARY KEY,

NAME VARCHAR2(22),

LOCATION VARCHAR2(50),

UNIQUE (NAME,LOCATION)

);

CREATE TABLE TB_DEPT3 (

ID NUMBER(11) PRIMARY KEY,

NAME VARCHAR2(22),

LOCATION VARCHAR2(50),

CONSTRAINT UNIQUE_NAEM_LOCA UNIQUE (NAME,LOCATION)

);

SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'TB_DEPT3';

INSERT INTO TB_DEPT3 VALUES(1,NULL,'1');

INSERT INTO TB_DEPT3 VALUES(2,NULL,'1');

INSERT INTO TB_DEPT3 VALUES(3,NULL,'2');

INSERT INTO TB_DEPT3 VALUES(4,'1','2');

INSERT INTO TB_DEPT3 VALUES(13,'1','1');

INSERT INTO TB_DEPT3 VALUES(14,'2','1');

INSERT INTO TB_DEPT3 VALUES(15,'2','2');

INSERT INTO TB_DEPT3 VALUES(16,'1','2');

select * from TB_DEPT3;

4.3 在修改数据表时添加和删除唯一约束

语法如下

ALTER TABLE <表名> ADD [CONSTRAINTS <约束名称>] UNIQUE (字段1,字段2...字段n);

ALTER TABLE <表名> DROP CONSTRAINTS <约束名称>;

示例10

CREATE TABLE TB_DEPT4 (

ID NUMBER(11) PRIMARY KEY,

NAME VARCHAR2(22),

LOCATION VARCHAR2(50)

);

ALTER TABLE TB_DEPT4 ADD CONSTRAINTS UNIQUE_NAME_LOCATION UNIQUE(NAME,LOCATION);

ALTER TABLE TB_DEPT4 DROP CONSTRAINTS UNIQUE_NAME_LOCATION;

ALTER TABLE TB_DEPT4 ADD UNIQUE(NAME,LOCATION);

总结

UNIQUE 和 PRIMARY KEY区别

UNIQUE 数据表字段中可以拥有多个UNIQUE 字段，但是PRIMARY KEY 只能拥有一个

UNIQUE 数据表字段中可以为NULL,PRIMARY KEY不能为空

5、检查约束 CHECK

5.1 概念

规定每一列能够输入的值,

5.2 语法如下

[CONSTRAINT <约束名称>] CHECK (检查条件)

示例11

CREATE TABLE TB_EMP7 (

ID NUMBER(11),

NAME VARCHAR2(25),

GENDER VARCHAR2(2),

AGE NUMBER(2),

PRIMARY KEY(ID),

CONSTRAINT CHECK_GENDER CHECK (GENDER = '男' OR GENDER = '女')

);

CREATE TABLE TB_EMP8 (

ID NUMBER(11),

NAME VARCHAR2(25),

GENDER VARCHAR2(2),

AGE NUMBER(2),

PRIMARY KEY(ID),

CHECK (GENDER = '男' OR GENDER = '女')

);

INSERT INTO TB_EMP7 VALUES(1,'梁自祥','男',30);

INSERT INTO TB_EMP7 VALUES(2,'梁自祥',NULL,30);

INSERT INTO TB_EMP8 VALUES(1,'梁自祥',null,30);--可以null值

INSERT INTO TB_EMP8 VALUES(2,'梁自祥','哈',30);--OCR-02290:违反检查约束条件

5.3 在修改表数据时添加和删除检查约束

语法如下

ALTER TABLE <表名> ADD [CONSTRAINTS <约束名称>[ CHECK (检查条件)

ALTER TABLE <表名> DROP CONSTRAINTS <约束名称>

示例12

CREATE TABLE TB_EMP9 (

ID NUMBER(11),

NAME VARCHAR2(25),

GENDER VARCHAR2(2),

AGE NUMBER(2),

PRIMARY KEY(ID)

);

ALTER TABLE TB_EMP9 ADD CONSTRAINTS CHECK_GENDER_1 CHECK (GENDER = '男' OR GENDER = '女');

ALTER TABLE TB_EMP9 DROP CONSTRAINTS CHECK_GENDER_1;

ALTER TABLE TB_EMP9 ADD CHECK (GENDER = '男' OR GENDER = '女');

总结

CONSTRAINTS <约束名称> 可以省略不写

主键、外键、唯一、检查约束删除语法一致，为 ALTER TABLE <表名> DROP CONSTRAINTS <约束名称>

6、非空约束 NOT NULL CONSTRAINT

6.1 概念

指定的字段不能为空,

6.2 语法如下

字段名数据类型 NOT NULL

示例13

CREATE TABLE TB_DEPT5 (

ID NUMBER(11) PRIMARY KEY,

NAME VARCHAR2(22) NOT NULL ,

LOCATION VARCHAR2(50)

);

INSERT INTO TB_DEPT5(ID,NAME,LOCATION) VALUES(1,'2','11');

INSERT INTO TB_DEPT5(ID,NAME,LOCATION) VALUES(2,NULL,'11');--ORA-01400:无法将NULL插入

INSERT INTO TB_DEPT5(ID,NAME,LOCATION) VALUES(2,'','11');--ORA-01400:无法将NULL插入

6.3 在修改表数据时添加和删除非空约束

语法如下

ALTER TABLE <表名> MODIFY <字段名> NOT NULL

ALTER TABLE <表名> MODIFY <字段名> NULL*/

示例14

CREATE TABLE TB_DEPT6 (

ID NUMBER(11) PRIMARY KEY,

NAME VARCHAR2(22) NOT NULL ,

LOCATION VARCHAR2(50)

);

DESC TB_DEPT6;

--删除非空约束

ALTER TABLE TB_DEPT6 MODIFY NAME NULL;

INSERT INTO TB_DEPT6(ID,NAME,LOCATION) VALUES(3,NULL,'11');

--添加非空约束

ALTER TABLE TB_DEPT6 MODIFY NAME NOT NULL;--ORA-02296:无法启用非空约束，提示空值，删除数据后重新启用

INSERT INTO TB_DEPT6(ID,NAME,LOCATION) VALUES(3,NULL,'11');--ORA-01400:无法将NULL插入

Oracle 数据库表和约束条件的增删改查

1、创建数据表

1.1 语法

1.2 查询表结构

2、主键约束语法 PRIMARY KEY CONSTRAINT

2.1 概念

2.2 单字段主键语法如下

2.2.1 字段名数据类型 PRIMARY KEY [默认值]

2.2.2 定义完所有列后采用以下语句

`2.3 多字段联合主键`

2.4 使用ALTER TABLE 语句为数据库修改主键约束和删除约束

2.5 查询表的约束

3、外键约束 FOREIGN KEY CONSTRAINT

3.1 概念

3.2 语法如下

3.3 修改数据表时进行外键的添加

3.4 移除外键约束

3.5 修改约束键名称

4、唯一约束 UNIQUE CONSTRAINT

4.1 概念

4.2 语法如下

4.2.1 在定义好的列之后直接指定唯一约束

4.2.2 在指定的所有列之后指定唯一约束

4.3 在修改数据表时添加和删除唯一约束

5、检查约束 CHECK

5.1 概念

5.2 语法如下

5.3 在修改表数据时添加和删除检查约束

6、非空约束 NOT NULL CONSTRAINT

6.1 概念

6.2 语法如下

6.3 在修改表数据时添加和删除非空约束

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

探索云世界

热门

云计算

大数据

云原生

人工智能

数据库

开发与运维

活动广场

任务中心

训练营

直播

乘风者计划

下载

镜像站

技术资料

Oracle 数据库表和约束条件的增删改查

1、创建数据表

1.1 语法

1.2 查询表结构

2、主键约束语法 PRIMARY KEY CONSTRAINT

2.1 概念

2.2 单字段主键语法如下

2.2.1 字段名 数据类型 PRIMARY KEY [默认值]

2.2.2 定义完所有列后采用以下语句

2.3 多字段联合主键

2.4 使用ALTER TABLE 语句为数据库修改主键约束和删除约束

2.5 查询表的约束

3、外键约束 FOREIGN KEY CONSTRAINT

3.1 概念

3.2 语法如下

3.3 修改数据表时进行外键的添加

3.4 移除外键约束

3.5 修改约束键名称

4、唯一约束 UNIQUE CONSTRAINT

4.1 概念

4.2 语法如下

4.2.1 在定义好的列之后直接指定唯一约束

4.2.2 在指定的所有列之后指定唯一约束

4.3 在修改数据表时添加和删除唯一约束

5、检查约束 CHECK

5.1 概念

5.2 语法如下

5.3 在修改表数据时添加和删除检查约束

6、非空约束 NOT NULL CONSTRAINT

6.1 概念

6.2 语法如下

6.3 在修改表数据时添加和删除非空约束

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

2.2.1 字段名数据类型 PRIMARY KEY [默认值]

`2.3 多字段联合主键`