--6.1 主键约束
alter table 表名 add contraint 约束名 primary key (列名1,列名2);
--添加主键
alter table customersnew add constraint customersnew_pk primary key (customer_id);
--禁用/启用主键
alter table enable/disable primary key;
--重命名主键
alter table 表名 rename constraint 原主键名称 to 新主键名称;
--删除主键约束
alter table 表名 drop primary key;
--获得索引的信息
select table_name, index_name, index_type
from user_indexes
where table_name = 'CUSTOMERSNEW';
--查看索引建立在哪些列之上
select index_name, table_name, column_name
from user_ind_columns
where index_name = 'CUSTOMERSNEW_PK';
--6.2 外键约束
--6.2.1 添加外键
alter table 子表 add constraint 外键名 foreign key (列名) references 主表(列名) on delete cascade--级联删除
--添加外键示例
alter table customersnew add constraint cus_account_mgr_fk
foreign key (account_mgr_id)
references employees(employee_id);
--查询表的外键信息
select table_name, constraint_name, constraint_type, r_constraint_name
from user_constraints
where table_name ='CUSTOMERSNEW';
--根据外键查询表名、约束列名
select table_name, constraint_name, column_name
from user_cons_columns
where constraint_name = 'CUS_ACCOUNT_MGR_FK';
--6.2.2 删除外键约束
alter table 表名 drop constraint 外键约束名;
--删除外键示例
alter table customersnew drop constraint cus_account_mgr_fk;
--设置级联删除外键
alter table customersnew add constraint cus_account_mgr_fk
foreign key (account_mgr_id)
references employees(employee_id)
on delete cascade;
--查看表信息
select customer_id,cust_first_name,account_mgr_id from customersnew;
--删除主表信息
delete from employees where employee_id=100;
--***重要***---根据主键查询引用这个主键的从表表名及列名
SELECT *
FROM dba_cons_columns
where CONSTRAINT_NAME in
(select CONSTRAINT_NAME
from user_constraints
where R_CONSTRAINT_NAME in
(select CONSTRAINT_NAME
from user_constraints
where CONSTRAINT_TYPE = 'P'
and table_name = 'EMPLOYEES'));
--引用类型:
--cascade关键字,表示当父表中的被引用列的数据被删除时,子表中对应的外键数据也将被删除。
--set null关键字,表示当父表中的被引用列的数据被删除时,子表中对应的外键数据将被设置为null。
--restrict关键字,表示拒绝对父表的删除或更新操作。
--6.3 唯一约束
alter table 表名 add constraint 唯一性约束名 unique (列名);
--增加唯一约束示例
alter table customersnew add constraint uk_phone unique(phone_number);
--查看唯一约束信息
select constraint_name, constraint_type, table_name
from user_constraints
where constraint_name = 'UK_PHONE';
--查看customersnew表的信息
select customer_id,cust_first_name,city,phone_number from customersnew;
insert into customersnew (customer_id,cust_first_name,city,phone_number)
values (982,'Anne','shanxi','+86 10 012 3839');
--重命名唯一性约束
alter table 表名 rename constraint 原约束名 to 新约束名;
--示例
alter table customersnew rename constraint uk_phone to uk_ph;
--禁用/启用唯一性约束
alter table 表名 disable/enable constraint 约束名;
--禁用约束示例
alter table customersnew disable constraint uk_ph;
--删除唯一性约束
alter table 表名 drop constraint 唯一性约束名;
--示例
alter table customersnew drop constraint uk_ph;
--6.4 检查约束
alter table 表名 add constraint 检查约束名 check (条件);
--查看检查约束的详细信息
select constraint_name, constraint_type, table_name, search_condition
from user_constraints
where constraint_name = 'CH_CREDIT';
--查看约束的列名
select constraint_name, table_name, column_name
from user_cons_columns
where constraint_name = 'CH_CREDIT';
--多条件检查约束
alter table customersnew add constraint ch_id_city check (customer_id>0 and city is not null);
--重命名检查约束
alter table customersnew rename constraint ch_id_city to ck_id_city;
--禁用约束
alter table customersnew disable constraint ck_id_city;
--插入非法数据
insert into customersnew (customer_id,cust_first_name,city,credit_limit)
values (984,'lily',null,1000);
--启用检查约束
alter table customersnew enable constraint ck_id_city;
--删除检查约束
alter table 表名 drop constraint 检查约束名;
select constraint_name, constraint_type, table_name
from user_constraints
where constraint_name = 'CK_ID_CITY';
--非空约束
alter table 表名 modify 列名 not null;
--示例
alter table customersnew modify cust_first_name not null;
--查看非空约束信息
select column_name, data_type, nullable
from user_tab_columns
where table_name = 'CUSTOMERSNEW';
--验证非空,插入非法数据
insert into customersnew
(customer_id, cust_first_name, city, credit_limit)
values
(984, null, 'beijing', 1000);
--撤销非空约束
alter table 表名 modify 列名 null;
--示例
alter table customersnew modify cust_first_name null;
-------------------------------------------------------------------
--6.5 默认约束
alter table 表名 modify 列名 default <default value>
--添加默认约束示例
alter table customersnew modify status default 'Silver';
--验证默认约束,插入信息
delete from customersnew where customer_id=985;
--插入信息
insert into customersnew
(customer_id, cust_first_name, city, credit_limit)
values
(985, 'lily', 'chongqin', 1000);
--显示地指定默认值
insert into customersnew
(customer_id, cust_first_name, city, status)
values
(986, 'lily', 'chongqin', default);
--查询默认约束的详细信息
select table_name, column_name, data_type, data_length, data_default
from user_tab_columns
where table_name = 'CUSTOMERSNEW'
and column_name = 'STATUS';
--删除默认约束
alter table customersnew modify status default null;
alter table 表名 add contraint 约束名 primary key (列名1,列名2);
--添加主键
alter table customersnew add constraint customersnew_pk primary key (customer_id);
--禁用/启用主键
alter table enable/disable primary key;
--重命名主键
alter table 表名 rename constraint 原主键名称 to 新主键名称;
--删除主键约束
alter table 表名 drop primary key;
--获得索引的信息
select table_name, index_name, index_type
from user_indexes
where table_name = 'CUSTOMERSNEW';
--查看索引建立在哪些列之上
select index_name, table_name, column_name
from user_ind_columns
where index_name = 'CUSTOMERSNEW_PK';
--6.2 外键约束
--6.2.1 添加外键
alter table 子表 add constraint 外键名 foreign key (列名) references 主表(列名) on delete cascade--级联删除
--添加外键示例
alter table customersnew add constraint cus_account_mgr_fk
foreign key (account_mgr_id)
references employees(employee_id);
--查询表的外键信息
select table_name, constraint_name, constraint_type, r_constraint_name
from user_constraints
where table_name ='CUSTOMERSNEW';
--根据外键查询表名、约束列名
select table_name, constraint_name, column_name
from user_cons_columns
where constraint_name = 'CUS_ACCOUNT_MGR_FK';
--6.2.2 删除外键约束
alter table 表名 drop constraint 外键约束名;
--删除外键示例
alter table customersnew drop constraint cus_account_mgr_fk;
--设置级联删除外键
alter table customersnew add constraint cus_account_mgr_fk
foreign key (account_mgr_id)
references employees(employee_id)
on delete cascade;
--查看表信息
select customer_id,cust_first_name,account_mgr_id from customersnew;
--删除主表信息
delete from employees where employee_id=100;
--***重要***---根据主键查询引用这个主键的从表表名及列名
SELECT *
FROM dba_cons_columns
where CONSTRAINT_NAME in
(select CONSTRAINT_NAME
from user_constraints
where R_CONSTRAINT_NAME in
(select CONSTRAINT_NAME
from user_constraints
where CONSTRAINT_TYPE = 'P'
and table_name = 'EMPLOYEES'));
--引用类型:
--cascade关键字,表示当父表中的被引用列的数据被删除时,子表中对应的外键数据也将被删除。
--set null关键字,表示当父表中的被引用列的数据被删除时,子表中对应的外键数据将被设置为null。
--restrict关键字,表示拒绝对父表的删除或更新操作。
--6.3 唯一约束
alter table 表名 add constraint 唯一性约束名 unique (列名);
--增加唯一约束示例
alter table customersnew add constraint uk_phone unique(phone_number);
--查看唯一约束信息
select constraint_name, constraint_type, table_name
from user_constraints
where constraint_name = 'UK_PHONE';
--查看customersnew表的信息
select customer_id,cust_first_name,city,phone_number from customersnew;
insert into customersnew (customer_id,cust_first_name,city,phone_number)
values (982,'Anne','shanxi','+86 10 012 3839');
--重命名唯一性约束
alter table 表名 rename constraint 原约束名 to 新约束名;
--示例
alter table customersnew rename constraint uk_phone to uk_ph;
--禁用/启用唯一性约束
alter table 表名 disable/enable constraint 约束名;
--禁用约束示例
alter table customersnew disable constraint uk_ph;
--删除唯一性约束
alter table 表名 drop constraint 唯一性约束名;
--示例
alter table customersnew drop constraint uk_ph;
--6.4 检查约束
alter table 表名 add constraint 检查约束名 check (条件);
--查看检查约束的详细信息
select constraint_name, constraint_type, table_name, search_condition
from user_constraints
where constraint_name = 'CH_CREDIT';
--查看约束的列名
select constraint_name, table_name, column_name
from user_cons_columns
where constraint_name = 'CH_CREDIT';
--多条件检查约束
alter table customersnew add constraint ch_id_city check (customer_id>0 and city is not null);
--重命名检查约束
alter table customersnew rename constraint ch_id_city to ck_id_city;
--禁用约束
alter table customersnew disable constraint ck_id_city;
--插入非法数据
insert into customersnew (customer_id,cust_first_name,city,credit_limit)
values (984,'lily',null,1000);
--启用检查约束
alter table customersnew enable constraint ck_id_city;
--删除检查约束
alter table 表名 drop constraint 检查约束名;
select constraint_name, constraint_type, table_name
from user_constraints
where constraint_name = 'CK_ID_CITY';
--非空约束
alter table 表名 modify 列名 not null;
--示例
alter table customersnew modify cust_first_name not null;
--查看非空约束信息
select column_name, data_type, nullable
from user_tab_columns
where table_name = 'CUSTOMERSNEW';
--验证非空,插入非法数据
insert into customersnew
(customer_id, cust_first_name, city, credit_limit)
values
(984, null, 'beijing', 1000);
--撤销非空约束
alter table 表名 modify 列名 null;
--示例
alter table customersnew modify cust_first_name null;
-------------------------------------------------------------------
--6.5 默认约束
alter table 表名 modify 列名 default <default value>
--添加默认约束示例
alter table customersnew modify status default 'Silver';
--验证默认约束,插入信息
delete from customersnew where customer_id=985;
--插入信息
insert into customersnew
(customer_id, cust_first_name, city, credit_limit)
values
(985, 'lily', 'chongqin', 1000);
--显示地指定默认值
insert into customersnew
(customer_id, cust_first_name, city, status)
values
(986, 'lily', 'chongqin', default);
--查询默认约束的详细信息
select table_name, column_name, data_type, data_length, data_default
from user_tab_columns
where table_name = 'CUSTOMERSNEW'
and column_name = 'STATUS';
--删除默认约束
alter table customersnew modify status default null;