oracle——数据表的相关操作——约束——各种约束详解

分手后的思念是犯贱 2023-10-02 08:16 52阅读 0赞

约束
    
    
    1、主键约束 : primary key
    
    select * from  student08;
    
    create table student08 ( id number primary key,             /*创建主键约束*/
                             name varchar2(20),
                             sex varchar2(20),
                             age number,
                             address varchar2(20)
                        ) tablespace test
                        
                        
                        
    insert into student08(id,name,sex,age,address) values(1,'yuanling','nv',27,'zhejiang');
                        
                        
    insert into student08(id,name,sex,age,address) values(1,'yuan','nv',26,'jiang');    /*违反了主键唯一性约束*/
    
    drop table student08;
    
    ----------------------------------------------------------------------------------------------------------------------
    
    显性的为主键取别名
    
    
    select * from  student08;
    
    create table student08 ( id number constraint pk_student08 primary key,             /*创建主键约束*/
                             name varchar2(20),
                             sex varchar2(20),
                             age number,
                             address varchar2(20)
                        ) tablespace test
                        
                        
                        
    insert into student08(id,name,sex,age,address) values(1,'yuanling','nv',27,'zhejiang');
                        
                        
    insert into student08(id,name,sex,age,address) values(1,'yuan','nv',26,'jiang');    /*违反了主键唯一性约束*/
    
    drop table student08;
    
    
    -----------------------------------------------------------------------------------------------------------------------
    
    select * from  student08;
    
    create table student08 ( id number,             /*创建主键约束*/
                             name varchar2(20),
                             sex varchar2(20),
                             age number,
                             address varchar2(20),
                             constraint pk_student08 primary key( id )
                        ) tablespace test
                        
                        
                        
    insert into student08(id,name,sex,age,address) values(1,'yuanling','nv',27,'zhejiang');
                        
                        
    insert into student08(id,name,sex,age,address) values(1,'yuan','nv',26,'jiang');                    
                        
    drop table student08; 
    
    ----------------------------------------------------------------------------------------------------------------------------------
    
    select * from  student08;
    
    create table student08 ( id number,             /*创建主键约束*/
                             name varchar2(20),
                             sex varchar2(20),
                             age number,
                             address varchar2(20),
                             constraint pk_student08 primary key( id,name )
                        ) tablespace test
                        
                        
                        
    insert into student08(id,name,sex,age,address) values(1,'yuanling','nv',27,'zhejiang');
                        
    insert into student08(id,name,sex,age,address) values(1,'yuanling01','nv',27,'zhejiang');
    
    insert into student08(id,name,sex,age,address) values(1,'yuanling','nv',27,'zhejiang');                 
    
    insert into student08(id,name,sex,age,address) values(1,'yuanling01','nv',27,'zhejiang');  
          
    drop table student08;
    
    ----------------------------------------------------------------------------------------------------
    如果一个表创建时，并没有指定主键，那么在表创建之后，照样可以添加主键
    
    
    select * from  student08;
    
    create table student08 ( id number,
                             name varchar2(20),
                             sex varchar2(20),
                             age number,
                             address varchar2(20)
                        ) tablespace test
                        
    修改列属性                    
    
    ---------   alter table student08 modify( id number primary key);  /*modify选项实际为修改列属性，并在修改列属性的同时，将该列指定为主键列*/
    
    为表添加多列(而不是修改列属性)
    
    alter table student08 add constraint pk_student08 primary key ( id,name );
                        
    insert into student08(id,name,sex,age,address) values(1,'yuanling','nv',27,'zhejiang');
                        
    insert into student08(id,name,sex,age,address) values(1,'yuanling01','nv',27,'zhejiang');
    
    insert into student08(id,name,sex,age,address) values(1,'yuanling','nv',27,'zhejiang');                 
                        
    drop table student08; 
    
    删除主键：alter table student08 drop primary key;
    
    利用删除约束的语法删除主键，因为表的约束可能有好多了，所以指定名称
    
    alter table student08 drop constraint pk_student08;
    
    
    +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    select * from  student08;
    
    create table student08 ( id number,
                             name varchar2(20),
                             sex varchar2(20),
                             age number,
                             address varchar2(20)
                        ) tablespace test
                        
    
    alter table student08 add constraint pk_student08 primary key ( id,name );
    
    alter table student08 drop constraint pk_student08;
    
    
    
    ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    
    
    禁用/启用 修改主键名 主键约束
    
    
    select * from  student08;
    
    create table student08 ( id number constraint pk_student08 primary key,             /*创建主键约束*/
                             name varchar2(20),
                             sex varchar2(20),
                             age number,
                             address varchar2(20)
                        ) tablespace test
                        
                        
                        
    insert into student08(id,name,sex,age,address) values(1,'yuanling','nv',27,'zhejiang');
    
    
    alter table student08 disable primary key;
    
    alter table student08 enable primary key;
                        
                        
    insert into student08(id,name,sex,age,address) values(1,'yuan','nv',26,'jiang');    /*违反了主键唯一性约束*/
    
    drop table student08;
    
    
    alter table student08 rename constraint pk_student08 to zj;
    alter table student08 rename constraint zj to pk_student08;
    
    
    
    +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    
    
    
    2、外键约束
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    =======================================================================================================================
    3、唯一性约束
    
    select * from  student08;
    
    create table student08 ( id number primary key,             /*创建主键约束*/
                             name varchar2(20) unique,             /*创建唯一性约束*/
                             sex varchar2(20),
                             age number,
                             address varchar2(20)
                        ) tablespace test
    
    insert into student08(id,name,sex,age,address) values(1,'yuanling','nv',27,'zhejiang');
    
    
    insert into student08(id,name,sex,age,address) values(2,'yuanling','nan',20,'jiangsu');  /*违反了name设置的唯一性*/
    
    
    insert into student08(id,name,sex,age,address) values(2,'yuanling01','nan',20,'jiangsu');
    
    insert into student08(id,name,sex,age,address) values(3,'','nan',25,'jiangsu');
    
    insert into student08(id,name,sex,age,address) values(4,'','nan',28,'jiangsu09');
    
    ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    
    
    已经存在的表中添加唯一性约束
    
    注意：与主键不同，一个唯一性约束只能建立在一个列上，因此，小括号内的列名只能有一个，不能是多个列名的列表；
    
    要想在多个列上建立唯一性约束，只能针对每一个列分别创建；
    
    
    select * from  student08;
    
    create table student08 ( id number primary key,             /*创建主键约束*/
                             name varchar2(20),             /*创建唯一性约束*/
                             sex varchar2(20),
                             age number,
                             address varchar2(20)
                        ) tablespace test
    ----------------------------------------------------------------------------------------------
    新建一个唯一性约束：
    
    alter table student08 add constraint wyxys unique ( name );
    
    
    重命名唯一性约束：
    
    alter table student08 rename constraint wyxys to wyx_ys;
    
    alter table student08 rename constraint wyx_ys to wyxys;
    
    
    禁用唯一性约束：
    
    alter table student08 disable constraint wyxys;
    
    或者
    
    alter table student08 modify constraint wyxys enable;
    
    
    启用唯一性约束：
    
    alter table student08 enable constraint wyxys;
    
    删除唯一性约束：
    
    alter table student08 drop constraint wyxys;
    ---------------------------------------------------------------------------
    
    select * from student08;
    
    insert into student08(id,name,sex,age,address) values(1,'yuanling','nv',27,'zhejiang');
    
    
    insert into student08(id,name,sex,age,address) values(2,'yuanling','nan',20,'jiangsu');  /*违反了name设置的唯一性*/
    
    
    insert into student08(id,name,sex,age,address) values(2,'yuanling01','nan',20,'jiangsu');
    
    insert into student08(id,name,sex,age,address) values(3,'','nan',25,'jiangsu');
    
    insert into student08(id,name,sex,age,address) values(4,'','nan',28,'jiangsu09');
    
    =======================================================================================================================================
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    4、检查约束
    
    select * from  student08;
    
    drop table student08;
    
    create table student08 ( id number primary key,             /*创建主键约束*/
                             name varchar2(20),             /*创建唯一性约束*/
                             sex varchar2(20),
                             age number,
                             address varchar2(20),
                             constraint chk_age check ( age between 20and 50)
                        ) tablespace test
    
    
    insert into student08(id,name,sex,age,address) values(1,'yuanling','nv',27,'zhejiang');
    
    insert into student08(id,name,sex,age,address) values(2,'yuanling','nan',50,'jiangsu'); 
     
    insert into student08(id,name,sex,age,address) values(3,'yuanling','nan',19,'jiangsu');  
    
    insert into student08(id,name,sex,age,address) values(4,'yuanling','nan',51,'jiangsu');  
    
    
    ------------------------------------------------------------------------------------------------
    
    
    ---------------------------------------------------------------------------------------------------------
    
    create table employees ( id number primary key,
                             name varchar2(50),
                             grade varchar2(50),
                             salary number,
                             constraint chk_salary check ( grade in ('manager','leader','staff') and ( grade = 'manager' and salary <= 8000 or grade = 'leader' and salary <= 5000 or grade = 'staff' and salary <= 4000 ))
                           ) tablespace test
    
    select * from employees;
    
    insert into employees( id,name,grade,salary) values (1,'shen','manager',7800);
    
    insert into employees( id,name,grade,salary) values (2,'zhou','leader',4800);
    
    insert into employees( id,name,grade,salary) values (3,'yuan','staff',3200);
    
    
    插入非法数据：
    insert into employees( id,name,grade,salary) values (4,'ling','staff',5000);
    
    
    ----------------------------
    
    重新命名约束：
    
    alter table employees rename constraint chk_salary to cksy;
    
    alter table employees rename constraint cksy to chk_salary;
    
    
    禁用约束：
    
    
    alter table employees disable constraint chk_salary;
    或者
    alter table employees modify constraint chk_salary disable;
    
    
    
    启用约束：
    
    alter table employees enable constraint chk_salary;
    或者
    alter table employees modify constraint chk_salary enable;
    
    删除约束：
    
    alter table employees drop constraint chk_salary;
    
    
    重新添加约束：
    
    alter table employees add constraint chk_salary check ( grade in ('manager','leader','staff') and ( grade = 'manager' and salary <= 8000 or grade = 'leader' and salary <= 5000 or grade = 'staff' and salary <= 4000 ));
    
    
    ===========================================================================================================================================================================================================================================
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    5、默认值约束
    
    select * from  student08;
    
    drop table student08;
    
    create table student08 ( id number,             /*创建主键约束*/
                             name varchar2(20),             /*创建唯一性约束*/
                             sex varchar2(20),
                             age number default 22,
                             address varchar2(20) default 'abc'
                        ) tablespace test
    
    insert into student08(id,name,sex,age,address) values(1,'yuanling','nv',20,'jiangsu');
    
    insert into student08(id,name,sex) values(2,'shen','nan');
    ---------------------------------------------------------------------------------------------------------
    使用函数作为默认值
    
    create table sx ( id number,
                      name varchar2(50),
                      xssj date default sysdate
                     ) tablespace test
    
    select * from sx;
    
    insert into sx (id,name) values (1,'sww');
    
    ---------------------------------------------------------------
    
    create table sx02 ( id number,
                      name varchar2(50),
                      xssj date
                     ) tablespace test
    
    select * from sx02;
    
    
    添加默认值约束：
    
    alter table sx02 modify xssj default sysdate;
    
    insert into sx02 (id,name) values (1,'sww');
    ---------------------------------------------------
    alter table sx02 modify name default 'abc';
    
    insert into sx02 (id) values (2);
    
    
    修改默认值：
    
    alter table sx02 modify name default '2wsdc';
    
    insert into sx02 (id) values (3);
    
    
    删除默认值：
    
    alter table sx02 modify name varchar2(50) default null;
    
    insert into sx02 (id) values (8);
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    6、非空约束( not null )
    
    
    create table sx03 ( id number,
                        name varchar2(50),
                        sex varchar2(10) not null
                     ) tablespace test
    
    select * from sx03;
    
    insert into sx03 (id,name) values (1,'sww');
    
    insert into sx03 (id,name,sex) values (1,'sww','nv');
    
    删除非空约束后，可以向表中插入数据(只需要把表的指定列的属性修改为null即可)
    
    alter table sx03 modify ( sex null);
    
    insert into sx03 (id,name) values (2,'123ew2');
    ----------------------------------------------------------------------
    为已经存在的表，增加非空约束
    
    create table sx04 ( id number,
                        name varchar2(50),
                        sex varchar2(10)
                     ) tablespace test
    
    select * from sx04;
    
    
    
    insert into sx04 (id,name,sex) values (1,'sww','nv');
    
    
    alter table sx04 modify ( sex not null);
    
    insert into sx04 (id,name,sex) values (2,'edcx','nan');
    
    insert into sx04 (id,name) values (1,'sww');