Oracle主键约束的创建，添加和删除

Oracle主键约束的创建，添加和删除 

2010-06-27 10:40:16|  分类：learning |  标签：|字号大中小 订阅

oracle的主键约束添加删除

1、创建表的同时创建主键约束
一、无命名
create table accounts (
accounts_number number primary key,
accounts_balance number
);
二、有命名
create table accounts (
accounts_number number primary key,
accounts_balance number,
constraint yy primary key(accounts_number)
);

2、删除表中已有的主键约束
一、无命名
SELECT * FROM USER_CONS_COLUMNS WHERE TALBE_NAME='accounts';
找出主键名
ALTER TABLE ACCOUNTS DROP CONSTRAINT SYS_C003063;
二、有命名
ALTER TABLE ACCOUNTS DROP CONTRAINT yy;

3、向表中添加主键约束
ALTER TABLE ACCOUNTS ADD CONSTRAINT PK_ACCOUNTS PRIMARY KEY(ACCOUNTS_NUMBER);

oracle中not null约束是我们用到的最多的约束之一了。我们可以在创建表时让系统自动指定notnull约束的名字来创建，也可以手动的的指定not null约束的名字来创建，也可以在表创建好后手动的修改表已达到增加not null约束的目的。

下面是一个例子
create table test_not_null
(id number not null,
name varchar2(30) ,
tel varchar2(20) constraint test_not_null_tel not null);

在这里我们创建了一个表，并分别给id 和 tel两列创建了not null的约束，其中id的约束是系统自动命名的，而tel的约束使我们手动命名的。对于地二个列name，oracle系统不会自动增加not null的约束。

我们可以在这个表里面插入一行数据，例如：
insert into test_not_null(id,tel) values(123,’88888888′);

我们也还可以使用修改表的方式来增加not null约束。例如刚才我们没有在test_not_null上建立空值约束，我们可以现在通过修改的方式来建立
alter table test_not_null
modify(name not null);

如果你执行了刚才我们的插入数据的操作，你执行这句话的时候可能会有以下错误：
ORA-02296: 无法启用 (SYS.) - 找到空值

这是因为我们刚才在插入数据时已经给name这一列增加了一个空值，现在又要在这一列上增加not null约束，显然是不行的。
如果实际问题中我们真的遇到这种需求，例如对于某个字段我们认为原来可以为空，并且再该列插入了很多空值，后来我们发现实际上是不应该为空的，可能会需先将原来的空值全部更新到一个新的有统一意义的值，然后在进行空值约束的加入操作。例如我们可以这样做：
update test_not_null
set name = ‘不知道’
where name is null;

现在我们再增加列的not null约束是没有问题了。
alter table test_not_null
modify(name not null);

表修改

1.创建表：
       a. 创建xs表中计算机专业学生的备份
           Create table xs_jsj as select* from xs where zym=’计算机’;
       b.完整的例子：
　　　　　 Createtable test (xm char(20) not null,zy varchar(30) default (‘计算机’));
                -- Create table
                create table DEPT
                                (
                                   DEPTNO NUMBER(2) not null,
                                    DNAMEVARCHAR2(14),
                                   LOC    VARCHAR2(13)
                                  )
                               tablespace USERS
                                pctfree10
                                initrans 1
                                maxtrans255
                        storage
                                (
                                 initial64K
                                minextents 1
                                 maxextents unlimited
                                 );
                -- Create/Recreateprimary, unique and foreign key constraints
                            alter tableDEPT
                             addconstraint PK_DEPT primary key (DEPTNO)
                             using index
                             tablespaceUSERS
                             pctfree 10
                             initrans 2
                             maxtrans 255
                             storage
                             (
                                initial64K
                               minextents 1
                               maxextents unlimited
                 );
         c.规则
         　　　　Alter table 命令有许多选项，一个记住语法的方法是Oracle执行这个操作需要的信息：
                1）、你不得不告诉Oracle你准备alter什么表：
                         Alter tabletable_name
                2)、然后，你准备做什么？Adding 一个约束
                         ALTER TABLEtable_name ADD CONSTRAINT
                3）、强烈建议但不要求为约束定义个名字。约束名不需要放在引号里，但会以大写字母形式存储在数据字典里，
                         ALTER TABLE tempADD CONSTRAINT pk_temp
                4）、表示约束类型将是Primary Key，Unique，Foreign Key，或Check约束
                         ALTER TABLE tempADD CONSTRAINT pk_temp PRIMARY KEY
                5)、跟在约束类型后面有几种特殊选项，Primary Key和Unique约束，需要指定约束的列；Check约束需要指定约束规则。
                        ALTER TABLE tempADD CONSTRAINT pk_temp PRIMARY KEY (ID);
                6)、Primary Key和Unique约束应当指定索引的表空间名，使用USING INDEXTABLESPACE子句.
                        ALTER TABLE tempADD CONSTRAINT pk_temp PRIMARY KEY (ID) USING INDEX TABLESPACE student_index;
                        创建表及索引:
                            create tableDEPT                             
                                           (
                                             DEPTNO NUMBER(2) notnull,
                                            DNAME VARCHAR2(14),
                                            LOC    VARCHAR2(13)
                                           )
                                            tablespace USERS
                                            pctfree 10
                                            initrans 1
                                            maxtrans 255
                                            storage
                                            (
                                               initial 64K
                                               minextents 1
                                               maxextents unlimited
                                             );
                                           
                                           create table employees
                                           (empno number(5),
                                           deptno number(3) not null,
                                                                               constraint emp_pk primary          key(empno,deptno) 　　／/有名的主键
                                           using index tablespace index
                                            storage (initial 64K
                                           next 64K
                                           )
                                           )
                                          
                                                                                 altertable DEPT              
add constraint PK_DEPT primary key (DEPTNO)
                                            using index
                                            tablespace USERS
                                             pctfree10
                                            initrans 2
                                            maxtrans 255
                                            storage
                                             (
                                               initial 64K
                                               minextents 1
                                               maxextents unlimited
                                            );
２.修改表.
        Alter table table_name
        Add column_name type [defaultexpression]    增加新列
　　        Modify datatype default expression           修改已有列和属性
　　　　Storagestorage_clause 　　　　　　          修改存储特征
　　　　Dropdrop_clause                             删除约束条件
　　　　　　　a.改变表所在的表空间
                                　　　　alter   table  name   move   tablespace  newtablespace
        例：　①在表xs中添加两列.jsj,djsm
                   Alter table xs add(jxjnum<img src="<img src="<imgsrc="">">">ber(1),djsm varchar(40) default ‘奖金1000’)；
             ②在表xs中修改名为djsm的列的默认值
　　　　　　　　　Altertalbe xs modify(djsm default ‘奖金800’)
            　③在表中删除名为jxj和djxm的列.
                　        Alter table xs drop column jxj;
               　 Alter table xs drop column djsm;
            　④修改表xs_kc的存储特征
　　　　　　　　　Altertalbe xs pctfree 20 maxtrans 200
           　 ⑤为表xs_jsj加上主键
　　　　　　　　　Altertable xs_jsj add (constraint “pk_jsj” primary key(xh));
              ⑥ 增加列
　                语法：ALTER TABLE [schema.] table_name ADD column_definition　type
　                例：　ALTER TABLE orders ADD order_date DATE;
             ⑦更改列
　　　　　      语法：ALTER TABLE [schema.] table_name MODIFY column_name new_attributes;
　　　　　        例：　ALTER TABLE orders MODITY (quantity number(10,3),statusvarchar2(15));
              ⑧.更改表名:
                 alter table t rename tos;
              ⑨.更改列名
                 alter table t renamecolumn n to s;
 
 
3.约束
Oracle数据库的完整性约束规则
            唯一性约束(Unique constraint)
            唯一性约束可以保护表中多个数据列，保证在保护的数据列中任何两行的数据都不相同。唯一性约束与表一起创建，在唯一性约束创建后，可以使用ALTER TABLE语句修改。
            语法：
                column_name data_typeCONSTRAINT constraint_name UNIQUE
                如果唯一性约束保护多个数据列，那么唯一性约束要作为表约束增加。语法如下：
                                CONSTRAINT constraint_name (column) UNIQUE
                                 USING INDEXTABLESPACE (tablespace_name)
                                 STORAGE(stored clause)
    唯一性约束由一个B-tree索引增强，所以可以在USING子串中为索引使用特殊特征，比如表空间或存储参数。CREATE TABLE语句在创建唯一性约束的同时也给目标数据列建立了一个唯一的索引。Feedom.net国内最早的网管网站
                                CREATE TABLEinsured_autos (policy_id NUMBER CONSTRAINT pk_policies PRIMARY KEY, vinVARCHAR2(10), coverage_begin DATE, coverage_term NUMBER, CONSTRAIN unique_autoUNIQUE (policy_id,vin) bitsCN_com
                                USINGINDEX TABLESPACE index STORAGE (INITIAL 1M NEXT 10M PCTINCREASE 0) )用户可以禁用未以性约束，但他仍然存在，禁用唯一性约束使用ALTERTABLE 语句
            ALTER TABLE insured_autosDISABLE CONSTRAIN unique_name;                  
删除唯一性约束，使用ALTERTABLE....DROP CONSTRAIN语句                      ALTER TABLE insured_autos DROPCONSTRAIN unique_name;           注意用户不能删除在有外部键指向的表的唯一性约束。这种情况下用户必须首            先禁用或删除外部键（foreign key)。 bitscn.com中国网管联盟
        删除或禁用唯一性约束通常同时删除相关联的唯一索引，因而降低了数据库性能。经常删除或禁用唯一性约束有可能导致丢失索引带来的性能错误。要避免这样错误，可以采取下面的步骤：                                         
            1、在唯一性约束保护的数据列上创建非唯一性索引。
            2、添加唯一性约束
                    主键(Primary Key)约束 bitscn.com中国网管联盟                                    表有唯一的主键约束。表的主键可以保护一个或多个列，主键约束可与NOTNULL约束共同作用于每一数据列。NOTNULL约束和唯一性约束的组合将保证主键唯一地标识每一行。像唯一性约束一样，主键由B-tree索引增强。 09hr.com网管求职        创建主键约束使用CREATE TABLE语句与表一起创建，如果表已经创建了，可以使用ALTER TABLE语句。
                                CREATETABLE policies (policy_id NUMBER CONSTRAINT pk_policies PRIMARY KEY,holder_name VARCHAR2(40), gender VARCHAR2(1), marital_status VARCHAR2(1),date_of_birth DATE );
                                　　与唯一性约束一样，如果主键约束保护多个数据列，那么必须作为一个表约束创建。 bitscn.com中国网管联盟
                                CREATETABLE insured_autos (policy_id NUMBER, vin VARCHAR2(40), coverage_begin DATE,coverage_term NUMBER, CONSTRAINT pk_insured_autos PRIMARY KEY (policy_id,vin)USING INDEX TABLESPACE index STORAGE (INITIAL 1M NEXT 10M PCTINCREASE 0));            
                                　　禁用或删除主键必须与ALTER TABLE 语句一起使用
                                ALTER TABLE policies DROPPRIMARY KEY;
                                ALTERTABLE policies DISABLE PRIMARY KEY;
1、创建约束
                            CREATE TABLEstudents
                            (student_id    VARCHAR2(10) NOTNULL,
                             student_nameVARCHAR2(30) NOT NULL,
                            college_major VARCHAR2(15) NOT NULL,
                             status        VARCHAR2(20) NOT NULL,
                             state         VARCHAR2(2),
                            license_no    VARCHAR2(30))TABLESPACE student_data;
2、创建主键
                            ALTER TABLEstudents
                             ADDCONSTRAINT pk_students PRIMARY KEY (student_id)
                             USING INDEXTABLESPACE student_index;
3、创建Unique约束
                            ALTER TABLEstudents
                             ADDCONSTRAINT uk_students_license
                             UNIQUE(state, license_no)
                             USING INDEXTABLESPACE student_index;
4、创建Check约束
                            ALTER TABLEstudents
                               ADDCONSTRAINT ck_students_st_lic
                                    CHECK((state IS NULL AND license_no IS NULL) OR
                                          (state IS NOT NULL ANDlicense_no is NOT NULL));
                            添加check约束
　　　　　      alter table emp add constraint concheck(dept_salary>0);
　　　　　　     　con 为约束名，　dept_salary为字段名
                               
5、创建外键约束
                             ALTER TABLEstudents
                             ADDCONSTRAINT fk_students_state
                             FOREIGN KEY(state) REFERENCES state_lookup (state);
6. 约束..
                    Alter tabletable_name add constrants BID primary key (bookno);
                    ALERT TABLEtable_name MODIFY( column1 PRIMARY KEY);
      1、创建表的同时创建主键约束
                        （1）无命名 create table student (studentid int  primary key not null,
                                                         studentnamevarchar(8),age int);
                        （2）有命名 create table students (studentid int ,studentname varchar(8),
                                                   age int,constraint yy primary key(studentid));
     2、删除表中已有的主键约束
                        （1）有命名 alter table students drop constraint yy;
                        （2）无命名可用 SELECT *　from user_cons_columns　where ..;
                             查找表中主键名称得student表中的主键名为SYS_C002715
                             alter tablestudent drop constraint SYS_C002715;
                    　　（３) 使约束失效:
                             alter tabletbl_employee disable constraint fk_emp;
                     删除约束:
                            alter tabletbl_department drop constraint pk_dept;
                     查询约束:
select CONSTRAINT_NAME from user_constraints where table_name='TBL_EMPLOYEE';
select CONSTRAINT_NAME,COLUMN_NAME from user_cons_columns wheretable_name='TBL_EMPLOYEE';
     ３. 删除表.
　　　　      Drop　table table_name;
４、操作表数据
           插入表记录：
            ①. a. insert into table_namecol1,col2 values (val1,val2);
例：Insrt intoxs(xh,xm,) values (‘09’,to_date(‘19860210’,’yyyymmdd’));
                    insert into    deptovalues('100','xieyunchao','m','22',to_date('19861104','yyyy-mm-dd'),10000)
                 b.从一个表中向另一个表中插入数据
　　　　　　　　　　　　　Insertinto table1(col1,col2,col3) select (col1,col2,col3)
　　　　　　　　　　　　　　　　　　　from othertable
                 c.使用子查询插入数据
                 　  insert into employee (empno,ename,sal,deptno)
                              selectempno,ename,sal,depto from emp;
                d.INSERT INTO EMP(ENAME,HIREDATE) VALUE(‘AA’,TO_DATE(‘1980-12-09’,’YYYY-MM-DD’))
　　　　　　③. 删除表数据：
　　　　　　　　　Deletefrom table_name where condition;
　　　　　　④. 修改表记录
　　　　　　　　　Updatetable_name set column_name=expression,…where condition.
                基于一张表修改另一张表的数据
                  UPDATE EMPLEE SETDEPTNO=(SELECT DENPNO FROM EMP WHERE EMPNO=7788)
                                 WHEREJOB=(SELECT JOB FROM EMP WHERE EMPNO=7788)
　　　　　　⑤. 删除所有记录但保留表结构.
　　　　　　　　　Truncatetable table_name;
　　　　　　⑥.查询数据
               查询表结构:DESC table_name
　　　　　　　　　Selectename,sal,12*sal+100 from emp
               注:select count(dinstinct(deptno))from emp
               a.查询大于平均的：
                  select empno from emp a,(select avg(sal) assal_sal from emp) b
                         wherea.sal>b.sal_sal;
　　　　　　　如果列中有空值时,则结果也为空（关于null值的处理（p４７））.
　　　　　　　　　如：selectename,name,12*sal+comm如果comm的值为null，结果也为null;
                      如上所示,comm为null时.则12*sal+comm也为null;解决方法是用nvl方法替换.
              在两个表中查询:以下两种方式都是一样的.
                  a.selectt_phone_operation.operation_name
                                               from t_phone_operation ,t_phone_operation_charge
                                                wheret_phone_operation.operation_id=t_phone_operation_charge.operation_id and
                                                                                   t_phone_operation_charge.phone_num=’159..’;
                                   b.select operation_name fromt_phone_operation where operation_id in (select operation_id fromt_phone_operation_charge where phone_num='159...'
使用日期格式显示日期：
select ename,to_char(hiredate,’yyyy—mm---dd’) from emp
select ename where hiredate>to_date(‘1999-12-31’,’yyyy-mm-dd’);
            
 
              使用别名的三种方式:
                  a.    select ename as name,sal salary,from emp
                  b.    select ename ” name”, sal*12 ”annualsalary”
              使用连接操作符:
                  Select ename || job as“employees” from emp
              用连接字符:
                  Select ename ||’ ’||’is a ’||’ ’||job as “employee details”
              限制重复的行：
                  Select distinct deptnofrom emp
              注意大小写:
                  Select ename,job,deptnofrom emp where job=’CLERK’
              使用between ….and 运算符
                  Select ename,sal fromemp where sal between 1000 and 1500;
              使用in 运算符
                  Selectempno,ename,sal,mgr from emp where mgr in(23,231,2345); 
              Like运算符：(模糊查询)
                  %代表至多任意字符
　                ＿代表一个任意字符
                        如：select ename from emp where enamelike ’s%’;
                  显示第三个字符为大写A的所有信息
                        SELECT ENAME ,SALFROM EMP WHERE ENAME LIKE '__A%';
                  显示雇员名包含"_"的雇员信息(其中ESCAPE后的字符a为转义字符)
                        SELECT ENAME,SALWHERE ENAME LIKE '%a_% ESCAPE 'a';
             Null运算符(关于null值的处理（p４７）：)
　               测试一个值是否为空：
                        Select ename,mgrfrom emp where mgr is null (注意是is null ,不是=null)
             使用NVL函数处理NULL值
                 NVL函数用于将NULL转换为实际值,其语法为NVL(exp1,exp2).如果是exp1 是null,则返回
                 exp2,如果exp1不为null,则返回exp1,参数exp1和exp2是任意相同的数据类型.
                 如:
                 SELECT ENAME, SAL,SAL+NVL(COM,0)AS "月收入" fromEMP;
             使用NVL2函数处理NULL值
                语法为NVL(exp1,exp2,exp3),如果exp1不是null,则返回exp2,如果exp1是null,则返回exp3;
             用WHERE语句限制日期
                示例一.符合默认日期格式
                  select * from emp wherehiredate>'01-1月-82'
                示例2:不符合默认日期格式(需要用TO_DATE函数转换)
                  SELECT * FROM EMPWHERE HIREDATE>TO_DATE('1994-9-24','yyyy-mm-dd');
            逻辑操作符：And or not
            Order by 子句
                       a.   使用order by 子句对查询数据排序
                             ―――asc：升序，默认
                             ―――desc：降序：
 
                       依据多列进行排序：
                             Selectename,deptno,sal from emp order by deptno,sal desc
                总结：
                SELECT    [DISTINCT] {*,COLUMN[ALIAS],…..}    //DISTINCT不重复
                FROM      tablename
                WHERE     condition
                ORDER BY   {column,expr,alias} [ASC|DESC]
            分组查询：
           常用分组函数：
                MAX , MIN ,AVG,SUM,COUNT,VARIANCE(取方差),STDDEV（取标准差）
●     当使用分组函数时，分组函数只能出现在选择列表，order by ,和having子句中，而不能出现在where和group by子句中.
●     当执行SELECT 语句时，如果选择列表同时包含列，表达式，和分组函数，那么这些列和表达式必须出现在GROUP BY子句中. 
select deptno,avg(sal),count(*) from emp group by deptno;
        having avg(sal)<2000;
●如果在select 语句中同时包含group by ,    having ,以及order by子句，则必须将order by子句放在最后.
●当限制分组结果时，必须要使用having,而不能在WHERE 子句中使用分函数限制分组显示结果.
子查询：
    子查询的作用：
●     在insert 或create table 语句中使用子查询，可以将表数据插入到目标表中.
●     通过update 子句中使用子查询，可以修改一列事多列数据.
●     通过在where ,having,start with 子句中使用子查询，可以提供条件值.
单行子查询：
select ename ,sal,deptno from emp where deptno=(select deptno from emp whereename=’scott’)
多行子查询：
select ename ,job,sal,deptno from emp where job in
 (select distinct job from emp wheredeptno=10)
使用比较符：
IN:匹配子查询结果中的任何一个就可以（见上例）.
ALL:必须要符合子查询结果中的所有值.
ANY:只要符合子查询结果中的任何一个就可以.
ALL的使用：
SELECT ENAME,SAL,DEPTNO FROM EMP WHERE SAL>ALL
       (SELECT SAL FROM EMP WHEREDEPTNO=30)
ANY 的使用：
   SELECT ENAME,SAL,DEPTNO FROM EMP WHERESAL>ANY
(SELECT SAL FROM EMP WHERE DEPT=’30’)
                多列子查询：
                    SELECT ENAME,JOB,SAL,DEPTNO FROM EMP WHERE (DEPTNO,JOB) =
                        (SELECTDEPTNO,JOB FROM EMP WHERE ENAME =’SMITH’)
SELECT ENAME,SAL ,COMM,DEPTNO FROM EMP WHERE (SAL,NVL(COMM,-1)) IN (SELECT     SAL,NVL(COMM,-1)FROM EMP WHERE DEPTNO=30)
          
 
 
 
 
 
            ⑦.删除表数据：
                    Delete fromtable_name where condition;
                    DELETE FROM EMP WHEREDEPTNO=(SELECT DEPTNO FROM DEPT WHERE DNAME=’SALES’)
            ⑧. 修改表记录
                    Update table_name setcolumn_name=expression,…where condition.
                    UPDATE TABLE_NAME SETCOL1=A*1.1,COL2=B*2 WHERE
                    更新日期：
                    UPDATE TABLE_NAME SETHIREDATE =TO_DATE(‘1987/12/1’,’YYYY/MM/DD’);
                    更新关连数据
                    UPDATE EMP SET(ENAME,SAL,COMM)=(SELECT ENAME,SAL,COMM FROM EMP WHERE ENAME=’XX’)WHEREENAME=’YY’
                    复制其他表数据
                    UPDATE EMPLEYEE SETDEPTNO=(SELECT DEPT FROM EMP WHERE EMPNO=7788)WHERE JOB=(SELECT JOB FROM EMPWHERE EMPNO=7788)
            ⑨.删除所有记录但保留表结构.
                    Truncate tabletable_name;
                  
            ⑩.事务
             a. 当出现下面的事件时,事务便结束
                  ①. commit or rollback 被执行时.
                  ②. Ddl or dcl被执行时.create or drop 等
                  ③. 用户退出.
                  ④. 系统中止或当机.
             b. 事务保存点.
    　　　        update ....
                  savepoint update_done
                  insert ....
                  rollback TO update_done