oracle 修改主键_Oracle数据库-五大约束

本章主要介绍oracle数据库中的五大约束，以及它们的设置与删除方法。

1.约束的作用

定义规则，对数据库中数据进行限制，确保数据正确性、有效性、完整性。

2.非空约束

1.在创建表时设置非空约束

格式：CREATE TABLE table_name

( Column_name datatype NOT NULL,…);

SQL> create table userinfo1

(id number(10,0),

Username varchar2(20) not null,

Userpwd varchar(30) not null);

创建表userinfo1，设置用户名和密码非空。向该表插入数据时，若用户名或密码为空，就会显示：无法将 NULL插入…发生报错。

2.在修改表时添加非空约束

格式：ALTER TABLE table_name

MODIFY column_name datatype NOT NULL;

SQL> alter table userinfo

Modify username varchar2(20) not null;

备注：若在添加非空约束之前，表中有空值，则会报错。

3.在修改表时去除非空约束

格式：ALTER TABLE table_name

MODIFY column_name datatype NULL;

SQL> alter table userinfo

Modify userpwd varchar2(30) NULL;

3.主键约束

确保表中每一行数据唯一，一张表只能设计一个主键约束。主键约束可以有多个字段构成，称为联合主键或复合主键。

1.创建表时设置主键约束

格式：CREATE TABLE table_name(

Column_name datatype PRIMARY KEY,…)

SQL>create table userinfo_prim1

(id number(10,0) primary key,

Username avrchar2(30),

Usrpwd varchar2(30));

备注：此时主键的名称未指定，是由系统生成的。

格式：CONSTRAINT constraint_name

PRIMARY KEY(column_name1,…)

SQL>create table userinfo_prim2

(id number(10,0),

Username avrchar2(30),

Usrpwd varchar2(30),

Constraint pk_id_username primary key (id,username));

备注：制定主键名称，且联合主键默认非空。

2.查找约束名称

SQL>desc user_constraints

Select constraint_name from user_constraints

Where table_name = 'userinfo_prim2';

3.在修改表时添加主键约束

格式：ADD CONSTRAINT constraint_name

PRIMARY KEY(column_name1,…);

SQL> alter table userinfo

Add constraint pk_id primary key(id);

备注：在添加主键之前，该列的元素必须唯一且非空，若表中没有数据，则无此问题。

2.更改约束名称

格式：RENAME CONSTRAINT old_name

TO new_name

SQL>alter table userinfo

Rename constraint pk_id to new_pk_id;

3.删除主键约束

格式：DISABLE|ENABLE CONSTRAINT constraint_name

SQL> alter table userinfo

Disable constraint new_pk_id;

备注：禁用|启用约束

格式：DROP CONSTRAINT constraint_name

SQL> alter table userinfo

Drop constraint new_pk_id;

备注：删除制定约束。

格式：DROP PRIMARY KEY[CASCADE]

备注：cascade可选项，删除级联约束

4.外键约束

1.在创建表时添加外键约束

格式：CREATE TABLE table1

(column_name datatype REFERENCES

Table2(column_name),…);

备注：table1是从表，table2是主表。设置外键约束时，主表字段必须是主键，主从表相应字段是同一数据类型。从表中外键字段值必须来自主表相应字段值，或者为空。

SQL>create table orderinfo

(oderid varchar2(20) primary key,

Ordername varchar2(30));

create table userinfo_o

(id vrchar2(20) primary key,

Username varchar2(30),

Orderid_new varchar2(20) references orderinfo(orderid));

2.在创建表时设置外键约束

格式：COMSTRAINT constraint_name FOREIGN

KEY(column_name) REFERENCES

Table_name(column_name)[ON DELETE CASCADE]

SQL>create table userinfo_1

(id varchar2(10) primary key,

Username varchar2(30),

Orderid varchar2(20),

Constraint fk_orderid_1 foreign key(ordered_new)references orderinfo(oderid));

3.在修改表时添加外键约束

格式：ADD CONSTRAINT constraint_name FOREIGN

KEY(column_name) REFERENCES

Table_name(column_name)[ON DELETE CASCADE]

SQL>create table userinfo_2

(id varchar2(20) primary key,

Username varchar2(30),

Typeid_new varchar2(20));

Alter table userinfo_2

Add constraint fk_typeid_2 foreign key (typeid_new) references typeinfo(typeid);

5.删除外键约束

删除外键约束有两种方式，一是先禁用外键约束，后续可恢复，二是完全删除外键约束。

格式：DISABLE|ENABLE CONSTRAINT constraint_name

先从user_constraints数据字典中查找约束的名称、类型和状态

SQL> select constraint_name,constraint_type,status from user_constraints

Where table name='USERINFO_2';

-> CONSTRAINT_NAME C STATUS

---------------------------- - ---------

SYS_CXXXXXXX P ENABLED

FK_TYPEID_2 R ENABLED

备注：SYS_CXXXXXXX是主键名称，由系统指定，XXXXXXX是数字。

禁用外键约束，可用如下代码

SQL>alter table userinfo_2

Disable constraint fk_typeid_2;

想要恢复外键约束，将上述代码改成enable即可

SQL>alter table userinfo_2

enable constraint fk_typeid_2;

要彻底删除外键约束，要用drop语句

格式：DROP CONSTRAINT constraint_name;

SQL> alter table userinfo_2

Drop constraint fk_typeid_2;

-> CONSTRAINT_NAME C STATUS

---------------------------- - ---------

SYS_CXXXXXXX P ENABLED

6.唯一约束

唯一约束和主键约束有两大区别，首先主键字段值必须非空而唯一约束允许一个空值存在，再者主键约束在每张表中只有一个而唯一约束可以有多个。

1.在创建表时设置唯一约束

在列级设置唯一约束

格式：CREATE TABLE table_name

(column_name datatype UNIQUE,…)

SQL>create table userinfo_un1

(id varchar(20) primary key,

Username varchar2(30) unique,

Userpwd varchar2(30));

在表级设置唯一约束

格式：CONSTRAINT constraint_name

UNIQUE(column_name);

SQL>create table userinfo_un2

(id varchar(20) primary key,

Username varchar2(30),

Constraint un_username unique(username));

2.在修改表时添加唯一约束

格式：ADD CONSTRAINT constraint_name

UNIQUE(column_name);

SQL> create table userinfo_un3

(id varchar(20) primary key,

Username varchar2(30));

Alter table userinfo_un3

Add constraint un_username_1

Unique(username);

3.删除唯一约束

删除唯一约束同样有两种方法，一种是禁用唯一约束，另一种是完全删除唯一约束。

格式：DISABLE|ENABLE CONSTRAINT constraint_name

先从user_constraints数据字典中查找约束的名称、类型和状态

SQL> select constraint_name,constraint_type,status from user_constraints

Where table name='USERINFO_UN3';

-> CONSTRAINT_NAME C STATUS

---------------------------- - ---------

UN_USERNAME_1 U ENABLED

SYS_CXXXXXXX P ENABLED

SQL>alter table userinfo_un3

Disable constraint UN_USERNAME_1;

要完全删除唯一约束，仍然是使用DROP 语句

格式：DISABLE|ENABLE CONSTRAINT constraint_name

DROP CONSTRAINT constraint_name;

SQL>alter table userinfo_un3

Drop constraint UN_USERNAME_1;

7.检查约束

设置检查约束后，添加数据时会检查该数据是否满足约束逻辑，使数据具有实际意义。检查约束在表中可以有多个，可以在列级和表级进行设置，检查约束名由数据库统一生成。

1.在创建表时设置检查约束

列级设置检查约束

格式：CREATE TABLE table_name

(column_name datatype CHECK(expressions),…)

SQL>create table userinfo_ch1

(id varchar2(20) primary key,

Username varchar2(30),

Salary number(6,0) check(salary>0));

若尝试向该表中插入一条工资为负数的数据

SQL>insert table userinfo_ch1

Values(1,'kamuel',-10);

此时数据库会报错，违反检查约束条件。

表级设置检查约束

格式：CONSTRAINT constraint_name

CHECK(expressions)

SQL>create table userinfo_ch2

(id varchar2(20) primary key,

Username varchar2(30),

Salary number(6,0),

Constraint ck_salary check(salary>0));

2.在修改表时添加检查约束

格式：ADD CONSTRAINT constraint_name

CHECK(expressions);

SQL>create table userinfo_ch3

(id varchar2(20) primary key,

Username varchar2(30),

Salary number(6,0));

Alter table userinfo_ch3

Add constraint ck_salary_new check(salary>0);

3.删除检查约束

删除检查约束同样有两种方法，一种是禁用检查约束，另一种是完全删除检查约束。

格式：DISABLE|ENABLE CONSTRAINT constraint_name

先从user_constraints数据字典中查找约束的名称、类型和状态

SQL> select constraint_name,constraint_type,status from user_constraints

Where table name='USERINFO_CH3';

-> CONSTRAINT_NAME C STATUS

---------------------------- - ---------

SYS_CXXXXXXX U ENABLED

CK_SALARY_NEW C ENABLED

SQL>alter table userinfo_ch3

Disable constraint CK_SALARY_NEW;

完全删除检查约束要用DROP语句

格式：DROP CONSTRAINT constraint_name;

SQL>alter table userinfo_ch3

Drop constraint CK_SALARY_NEW;

8.五大约束总结

五大约束分别是：非空约束、主键约束、外键约束、唯一约束和检查约束。

其中，主键约束在一张表中是唯一的，且可由多个字段构成，而外键约束涉及两张表之间的关系。

在创建表时设置约束或修改表时添加约束，只有非空约束只能在列级设置，不能在表级设置。其他约束既可以在列级设置，也可以在表级设置。

要更改约束的名称，可以从user_constraints中查找，然后用RENAME语句重命名。

要删除约束时，对于非空约束，只需要在对应MODIFY语句中将NOT NULL改为NULL。对于其他约束，有禁用约束(DISABLE)和完全删除DROP两种方法。要删除主键约束可使用DROP PRIMARY KEY语句。