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

1、Oracle主键约束的创建，添加和删除2010-06-27 10:40:16| 分类：learning | 字号大中小订阅oracle的主键约束添加删除1、创建表的同时创建主键约束一、无命名create table acco unts (acco unts_nu mber nu mber primary key,acco un ts_bala nee nu mber);二、有命名create table acco unts (acco unts_nu mber nu mber primary key,acco un ts_bala nee nu mber,con stra int yy pri

2、mary key(acco unts_nu mber)；2、删除表中已有的主键约束一、无命名SELECT * FROM USER_CONS_COLUMNS WHERE TALBE_NAME='accou nts：找出主键名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);orac

3、le中not null约束是我们用到的最多的约束之一了。我们可以在创建表时让系统自动指定not null约束的名字来创建，也可以手动的的指定 not null约束的名字来创建，也可以在 表创建好后手动的修改表已达到增加not null约束的目的。下面是一个例子create table test_ not_n ull(id nu mber not n ull,n ame varchar2(30),tel varchar2(20) con stra int test_ not_n ull_tel not n ull);在这里我们创建了一个表，并分别给 id和tel两列创建了 not null的约束

4、，其中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_nullmodify (n ame not nu II);如果你执行了刚才我们的插入数据的

5、操作，你执行这句话的时候可能会有以下错误：ORA-02296:无法启用(SYS.)-找到空值这是因为我们刚才在插入数据时已经给name这一列增加了一个空值，现在又要在这一列上增加not null约束，显然是不行的。如果实际问题中我们真的遇到这种需求，例如对于某个字段我们认为原来可以为空，并且再该列插入了很多空值，后来我们发现实际上是不应该为空的，可能会需先将原来的空值全部更新到一个新的有统一意义的值，然后在进行空值约束的加入操作。例如我们可以这样做：update test_ not_n ullset name = 不知道'where n ame is n ull;现在我们再增加列的no

6、t null约束是没有问题了。alter table test_ not_nullmodify (n ame not nu II);表修改1创建表：a. 创建xs表中计算机专业学生的备份Create table xs_jsj as select * from xs where zym='计算机 '；b. 完整的例子：Create table test (xm char(20) not null,zy varchar(30) default ( 计算机')；'-Create tablecreate table DEPT(DEPTNO NUMBER(2) not n

7、 ull,DNAME V ARCHAR2(14),LOC VARCHAR2(13)tablespace USERSpctfree 10in itra ns 1maxtra ns 255storage(in itial 64Kmi nexte nts 1maxexte nts un limited)；-Create/Recreate primary, unique and foreig n key con stra intsalter table DEPTadd con strai nt PK_DEPT primary key (DEPTNO)using in dextablespace USE

8、RSpctfree 10in itra ns 2maxtra ns 255 storage(in itial 64Kmi nexte nts 1maxexte nts un limited)；c. 规则Alter table命令有许多选项，一个记住语法的方法是Oracle执行这个操作需要的信息：1) 、你不得不告诉 Oracle你准备alter什么表：Alter table table_ name2) 、然后，你准备做什么？Addi ng 个约束ALTER TABLE table_ name ADD CONSTRAINT3) 、强烈建议但不要求为约束定义个名字。约束名不需要放在引号里，但 会以

9、大写字母形式存储在数据字典里，ALTER TABLE temp ADD CONSTRAINT pk_temp4) 、表示约束类型将是 Primary Key， Unique , Foreign Key，或 Check 约束ALTER TABLE temp ADD CONSTRAINT pk_temp PRIMARY KEY5) 、跟在约束类型后面有几种特殊选项，Primary Key和Unique约束，需要指定约束的列；Check约束需要指定约束规则。ALTER TABLE temp ADD CONSTRAINT pk_temp PRIMARY KEY (ID);6) 、Primary Key

10、和Unique约束应当指定索引的表空间名，使用USINGINDEX TABLESPACE 子句.ALTER TABLE temp ADD CONSTRAINT pk_temp PRIMARY KEY (ID) USING INDEX TABLESPACE stude nt_in dex;创建表及索引：create table DEPT(DEPTNO NUMBER(2) not nu II,DNAME V ARCHAR2(14),LOC VARCHAR2(13)tablespace USERSpctfree 10in itra ns 1maxtra ns 255 storage (in itia

11、l 64Kmi nexte nts 1maxexte nts un limited)；con stra int emp_pk primarycreate table employees (emp no nu mber(5), dept no nu mber(3) not n ull,key(emp no ,dept no)/ / 有名的主键using in dex tablespace in dex storage (in itial 64Kn ext 64K)alter table DEPTadd con strai nt PK_DEPT primary key (DEPTNO)using

12、in dex tablespace USERS pctfree 10in itra ns 2maxtra ns 255 storage (in itial 64K mi nexte nts 1 maxexte nts un limited);2 .修改表.Alter table table_ name增加新列修改已有列和属性修改存储特征删除约束条件Add colu mn_n ame type default expressi onModify datatype default expressi onStorage storage_clauseDrop drop_clausea. 改变表所在的表

13、空间altertable n ame movetablespacen ewtablespacenu m<imgsrc="<imgsrc="<img例：在表xs中添加两列.jsj,djsmAlter table xs add(jxj src="">">">ber(1),djsm varchar(40) defau lt 奖金 1000 '； 在表xs中修改名为djsm的列的默认值Alter talbe xs modify(djsm default奖金 80') 在表中删除名为jxj和d

14、jxm的列.Alter table xs drop colu mn jxj;Alter table xs drop colu mn djsm; 修改表xs_kc的存储特征Alter talbe xs pctfree 20 maxtra ns 200 为表xs_jsj加上主键Alter table xs_jsj add (constraint“ pk_jsj ” primary key(xh); 增加列语法：ALTER TABLE schema. table_name ADD column_definitiontype例：ALTER TABLE orders ADD order_date DAT

15、E;更改列语法：ALTER TABLE schema. table_name MODIFY column_namen ew_attributes;例：ALTER TABLE orders MODITY (quantity number(10,3),statusvarchar2(15); .更改表名：alter table t ren ame to s; .更改列名alter table t ren ame colu mn n to s;3约束 Oracle数据库的完整性约束规则唯一性约束(Unique constraint)唯一性约束可以保护表中多个数据列，保证在保护的数据列中任何两行的数据都

16、不相同。唯一性约束与表一起创建，在唯一性约束创建后，可以使用ALTER TABLE语句修改。语法：column_ name data_type CONSTRAINT con strai nt_n ame UNIQUE如果唯一性约束保护多个数据列，那么唯一性约束要作为表约束增加。语法如下：CONSTRAINT con strai nt_n ame (colum n) UNIQUE USING INDEX TABLESPACE (tablespace_ name) STORAGE (stored clause)唯一性约束由一个 B-tree索引增强，所以可以在 USING子串中为索引使用特殊特征，

17、 比如表空间或存储参数。CREATE TABLE语句在创建唯一性约束的同时也给目标数据列建 立了一个唯一的索引。F国内最早的网管网站CREATE TABLE insured_autos (policy_id NUMBER CONSTRAINT pk_policies PRIMARY KEY, vin VARCHAR2(10), coverage_begin DATE, coverage_term NUMBER, CONSTRAIN uni que_auto UNIQUE (policy_id,vi n) bitsCN_comUSING INDEX TABLESPACE index STORAG

18、E (INITIAL 1M NEXT 10M PCTINCREASE 0)用户可以禁用未以性约束，但他仍然存在，禁用唯一性约束使用 ALTER TABLE 语句ALTER TABLE in sured_autos DISABLE CONSTRAIN uni que_ name;删除唯一性约束，使用 ALTER TABLE.DROP CONSTRAIN 语句 ALTER TABLE insured_autos DROP CONSTRAIN unique_name;注意用户不能删除在有外部键指向的表的唯一性约束。这种情况下用户必须首先禁用或删除外部键(foreign key)。 中国网管联盟删除或

19、禁用唯一性约束通常同时删除相关联的唯一索引，因而降低了数据库性能。经常删除或禁用唯一性约束有可能导致丢失索引带来的性能错误。要避免这样错误，可以采取下面的步骤：1、在唯一性约束保护的数据列上创建非唯一性索引。2、添加唯一性约束主键(Primary Key)约束 中国网管联盟表有唯一的主键约束。表的主键可以保护一个或多个列，主键约束可与NOT NULL约束共同作用于每一数据列。NOT NULL约束和唯一性约束的组合将保证主键唯一地标识每一行。 像唯一性约束一样，主键由B-tree索引增强。 网管求职创建主键约束使用CREATE TABLE语句与表一起创建， 如果表已经创建了， 可以使用ALTER

20、 TABLE 语句。CREATE TABLE policies (policy_id NUMBERCONSTRAINT pk_policies PRIMARY KEY, holder_ name VARCHAR2(40),gen derVARCHAR2(1), marital_status V ARCHAR2(1), date_of_birth DATE );与唯一性约束一样，如果主键约束保护多个数据 列，那么必须作为一个表约束创建。中国网管联盟CREATE TABLE insured_autos (policy_id NUMBER, vin VARCHAR2(40),coverage_beg

21、i n DATE, coverage_term NUMBER, CONSTRAINTpk_insured_autos PRIMARY KEY (policy_id,vin) USING INDEX TABLESPACE index STORAGE (INITIAL 1M NEXT 10M PCTINCREASE 0);禁用或删除主键必须与ALTER TABLE语句一起使用ALTER TABLE policies DROP PRIMARY KEY;ALTER TABLE policies DISABLE PRIMARY KEY;1、创建约束CREATE TABLE stude nts(stude

22、 nt_id VARCHAR2(10) NOT NULL, stude nt_n ame VARCHAR2(30) NOT NULL, college_major VARCHAR2(15) NOT NULL, statusVARCHAR2(20) NOT NULL,stateVARCHAR2(2),lice nse_ noVARCHAR2(30) TABLESPACEstude nt_data;2、创建主键ALTER TABLE stude ntsADD CONSTRAINTpk_stude nts PRIMARY KEY(stude nt_id)USING INDEX TABLESPACE

23、stude nt_in dex;3、创建Unique约束ALTER TABLE stude ntsADD CONSTRAINT uk_stude nts_lice nseUNIQUE (state, lice nse_no) USING INDEX TABLESPACE stude nt_in dex;4、创建Check约束ALTER TABLE stude ntsADD CONSTRAINT ck_stude nts_st_licCHECK (state IS NULL AND lice nse_no IS NULL)OR(state IS NOT NULL AND license_no i

24、s NOT NULL);添加check约束alter table emp add con stra int con check(dept_salary>0);con为约束名，dept_salary为字段名5、创建外键约束ALTER TABLE stude ntsADD CONSTRAINT fk_stude nts_stateFOREIGN KEY (state) REFERENCES state_lookup (state);6、约束.Alter table table_ name add con stra nts BID primary key (book no);ALERT TAB

25、LE table_ name MODIFY( colu mn1 PRIMARY KEY);1、创建表的同时创建主键约束(1) 无命名 create table stude nt (stude ntid int primary key not null,stude ntn ame varchar(8),age in t);(2) 有命名 create table students (studentid int ,studentname varchar(8),age int,constraint yy primary key(stude ntid);2、删除表中已有的主键约束(1) 有命名 alt

26、er table stude nts drop con stra int yy;(2) 无命名 可用 SELECT * from user_cons_columns where .;查找表中主键名称得stude nt表中的主键名为SYS_C002715alter table stude nt drop con strai nt SYS_C002715;(3 )使约束失效：alter table tbl_employee disable con stra int fk_emp;删除约束：alter table tbl_departme nt drop con stra int pk_dept;查

27、询约束：select CONSTRAINT_NAME from user_co nstrai nts where table_ name='TBL_EMPLOYEE'select CONSTRAINT_NAME,COLUMN_NAMEfromuser_co ns_colu mnswheretable_ name='TBL_EMPLOYEE'3. 删除表.Drop table table_ name;4、操作表数据插入表记录： .a. in sert into table_ name col1,col2 values (val1,val2);例：Insrt int

28、o xs(xh,xm,) values ( 09' ,to_date( 19860210' , ' yyyymmdd );insertintodeptovalues('100','xieyu nchao','m','22',to_date('19861104','yyyy-mm-dd'),10000)b. 从一个表中向另一个表中插入数据In sert into table1(col1,col2,col3) select (col1,col2,col3)from otherta

29、blec. 使用子查询插入数据in sert into employee (emp no,en ame,sal,dept no)select emp no,en ame,sal,depto from emp;d. IN SERTINTOEMP(ENAME,HIREDATE)VALUE( ' AA' ,TO_DTE( '19802-09' , ' YYMMI- DD ) .删除表数据：Delete from table_ name where con diti on; .修改表记录Update table_ name set colu mn_n ame=e

30、xpressi on,where con diti on.基于一张表修改另一张表的数据UPDATE EMPLEE SET DEPTNO=(SELECT DENPNO FROM EMPWHERE EMPNO=7788)WHERE JOB=(SELECT JOB FROM EMP WHERE EMPNO=7788) .删除所有记录但保留表结构.Trun cate table table_ name; .查询数据查询表结构：DESC table_nameSelect en ame,sal,12*sal+100 from emp注:select coun t(d in sti nct(dept no)

31、 from empa.查询大于平均的：select emp no from emp a,(select avg(sal) as sal_sal from emp) bwhere a.sal>b.sal_sal;如果列中有空值时，则结果也为空(关于 null值的处理(p 4 7).女口： select ename,name,12*sal+comm 如果 comm 的值为 null，结果也为 null;如上所示,comm为null时.则12*sal+comm也为null;解决方法是用 nvl方法替换.在两个表中查询：以下两种方式都是一样的.a.select t_pho ne_operati

32、on. operati on_n amefromt_pho ne_operatio n ,t_pho ne_operati on _chargewhere'159.'t_pho ne_operatio n. operatio n_id=t_pho ne_operati on _charge.operati on_id and t_pho ne_operatio n_charge.ph one_num=b.select operatio n_n ame from t_ph on e_operati on where operati on_id in (select operati

33、 on_idfromt_pho ne_operati on _charge wherephone_num='159.' 使用日期格式显示日期：select en ame,to_char(hiredate,m'-yydy ) from empselect ename where hiredate>to_date( 1999-12-31'' , ' yyym-dd');使用别名的三种方式：a. select en ame as n ame,sal salary,from empb. select en ame使用连接操作符：Select

34、 en ame | job as用连接字符：Select en ame |'限制重复的行：Select disti net dept no from emp注意大小写：Select en ame,job,dept no from emp where job= 使用between .anc运算符Select en ame,sal from emp where sal betwee n 1000 and 1500; 使用in运算符Select emp no,en ame,sal,mgr from emp where mgr in(23,231,2345); Like运算符：(模糊查询)%代

35、表至多任意字符代表一个任意字符女口： select ename from emp where ename like显示第三个字符为大写 A的所有信息ENAME ,SAL FROM EMP WHERESELECTname ” , sal*12 ” annual salary ”“ employees ” from emp| ' is a ' | '' |job as'CLERK“ employee details ”'s%'；ENAME LIKE'_A%'a为转义字符)显示雇员名包含SELECT"_"的雇

36、员信息(其中ESCAPE后的字符ENAME,SAL WHERE ENAME LIKE '%a_% ESCAPE 'a'值的处理(p4 7):)Null运算符(关于null测试一个值是否为空：Select ename,mgr from emp where mgr is null ( 注意是 is null ,不是=null)函数处理NULL值函数用于将NULL转换为实际值，其语法为NVL(exp1,exp2).如果是使用NVLNVLexp1是null,则返回exp2，如果exp1不为null,则返回exp1,参数exp1和exp2是任意相同的数据类型如：SELECT EN

37、AME, SAL,SAL+NVL(COM,0) AS " 月收入 ” from EMP;使用NVL2函数处理NULL值语法为NVL(exp1,exp2,exp3),如果exp1不是null,则返回exp2,如果exp1是 null,则返回 exp3;用WHERE语句限制日期示例一 符合默认日期格式select * from emp where hiredate>'01-1 月-82'示例2:不符合默认日期格式(需要用TO_DATE函数转换)SELECT*FROMEMPWHEREHIREDATE>TO_DATE('1994-9-24',

38、9;yyyy-mm-dd');逻辑操作符：And or notOrder by 子句a. 使用order by子句对查询数据排序asc 升序，默认desc降序：重复总结：./DISTINCT 不SELECTDISTINCT *,COL UMNALIAS,FROMtable nameWHEREcon diti onORDER BYcolu mn ,expr,alias ASC|DESC依据多列进行排序:Select en ame,dept no ,sal from emp order by dept no ,sal desc分组查询：常用分组函数：MAX , MIN ,AVG ,SUM,

39、COUNT,V ARIANCE(取方差),STDDEV (取标准差)当使用分组函数时，分组函数只能出现在选择列表，order by，和 having子句中，而不能出现在where和group by子句中. 当执行SELECT语句时，如果选择列表同时包含列，表达式，和分组函数，那么这 些列和表达式必须出现在 GROUP BY子句中.select dept no ,avg(sal),co un t(*) from emp group by dept no;having avg(sal)<2000;如果在select语句中同时包含 group by , having ,以及order by子句

40、，则必须将 order by 子句放在最后.当限制分组结果时，必须要使用having，而不能在 WHERE子句中使用分函数限制分组显示结果.子查询：子查询的作用： 在insert或create table语句中使用子查询，可以将表数据插入到目标表中 通过update子句中使用子查询，可以修改一列事多列数据 通过在where ,having,start with子句中使用子查询，可以提供条件值单行子查询：select en ame ,sal,dept no from emp where dept no=(select dept no from emp where en ame=' scott多行子查询：select en ame ,job,sal,dept no from