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

1、Oracle主键约束的创建，添加和删除2010-06-2710:40:16|分类：learning|字号大中小订阅oracle的主键约束添加删除1、创建表的同时创建主键约束一、无命名createtableaccounts(accounts_numbernumberprimarykey,accounts_balancenumber);二、有命名createtableaccounts(accounts_numbernumberprimarykey,accounts_balancenumber,constraintyyprimarykey(accounts_number)；2、删除表中已有的主键约束

2、一、无命名SELECT*FROMUSER_CONS_COLUMNSWHERETALBE_NAME='accounts'找出主键名ALTERTABLEACCOUNTSDROPCONSTRAINTSYS_C003063;二、有命名ALTERTABLEACCOUNTSDROPCONTRAINTyy;3、向表中添加主键约束ALTERTABLEACCOUNTSADDCONSTRAINTPK_ACCOUNTSPRIMARYKEY(ACCOUNTS_NUMBER);oracle中notnull约束是我们用到的最多的约束之一了。我们可以在创建表时让系统自动指定notnull约束的名字来创建，也

3、可以手动的的指定notnull约束的名字来创建，也可以在表创建好后手动的修改表已达到增加notnull约束的目的。下面是一个例子createtabletest_not_null(idnumbernotnull,namevarchar2(30),telvarchar2(20)constrainttest_not_null_telnotnull);在这里我们创建了一个表，并分别给id和tel两列创建了notnull的约束，其中id的约束是系统自动命名的，而tel的约束使我们手动命名的。对于地二个列name,oracle系统不会自动增加notnull的约束。我们可以在这个表里面插入一行数据，例如：i

4、nsertintotest_not_null(id,tel)values(123,'88888888');我们也还可以使用修改表的方式来增加notnull约束。例如刚才我们没有在test_not_null上建立空值约束，我们可以现在通过修改的方式来建立altertabletest_not_nullmodify(namenotnull);如果你执行了刚才我们的插入数据的操作，你执行这句话的时候可能会有以下错误：ORA-02296:无法启用(SYS.)-找到空值这是因为我们刚才在插入数据时已经给name这一列增加了一个空值，现在又要在这一列上增加notnull约束，显然是不行的。如

5、果实际问题中我们真的遇到这种需求，例如对于某个字段我们认为原来可以为空，并且再该列插入了很多空值，后来我们发现实际上是不应该为空的，可能会需先将原来的空值全部更新到一个新的有统一意义的值，然后在进行空值约束的加入操作。例如我们可以这样做：updatetest_not_nullsetname=不知道wherenameisnull;现在我们再增加列的notnull约束是没有问题了。1. altertabletest_not_nullmodify(namenotnull);表修改创建表：a. 创建xs表中计算机专业学生的备份Createtablexs_jsjasselect*fromxswherez

6、ym='计算机'；完整的例子：Createtabletest(xmchar(20)notnull,zyvarchar(30)default(计算机')；'-CreatetablecreatetableDEPT(DEPTNONUMBER(2)notnull,DNAMEVARCHAR2(14),LOCVARCHAR2(13)tablespaceUSERSpctfree10initrans1maxtrans255storage(initial64Kminextents1maxextentsunlimited)；-Create/Recreateprimary,uniqu

7、eandforeignkeyconstraintsaltertableDEPTaddconstraintPK_DEPTprimarykey(DEPTNO)usingindextablespaceUSERSpctfree10initrans2maxtrans255storage(initial64Kminextents1maxextentsunlimited)；c规则Altertable命令有许多选项，一个记住语法的方法是Oracle执行这个操作需要的信息：1) 、你不得不告诉Oracle你准备alter什么表：Altertabletable_name、然后，你准备做什么？Adding一个约束A

8、LTERTABLEtable_nameADDCONSTRAINT、强烈建议但不要求为约束定义个名字。约束名不需要放在引号里，但会以大写字母形式存储在数据字典里，ALTERTABLEtempADDCONSTRAINTpk_temp2) 、表示约束类型将是PrimaryKey,Unique,ForeignKey，或Check约束ALTERTABLEtempADDCONSTRAINTpk_tempPRIMARYKEY、跟在约束类型后面有几种特殊选项，PrimaryKey和Unique约束，需要指定约束的列；Check约束需要指定约束规则。ALTERTABLEtempADDCONSTRAINTpk_t

9、empPRIMARYKEY(ID);3) 、PrimaryKey和Unique约束应当指定索引的表空间名，使用USINGINDEXTABLESPACE子句.ALTERTABLEtempADDCONSTRAINTpk_tempPRIMARYKEY(ID)USINGINDEXTABLESPACEstudent_index;创建表及索引：createtableDEPT(DEPTNONUMBER(2)notnull,DNAMEVARCHAR2(14),LOCVARCHAR2(13)tablespaceUSERSpctfree10initrans1maxtrans255storage(initial64

10、Kminextents1maxextentsunlimited)；constraintemp_pkprimarycreatetableemployees(empnonumber(5),deptnonumber(3)notnull,key(empno,deptno)/有名的主键usingindextablespaceindexstorage(initial64Knext64K)altertableDEPTaddconstraintPK_DEPTprimarykey(DEPTNO)usingindextablespaceUSERSpctfree10initrans2maxtrans255stora

11、ge(initial64Kminextents1maxextentsunlimited);2.修改表.Altertabletable_name增加新列修改已有列和属性修改存储特征删除约束条件Addcolumn_nametypedefaultexpressionModifydatatypedefaultexpressionStoragestorage_clauseDropdrop_clause改变表所在的表空间altertablenamemovetablespacenewtablespacenum<imgsrc="<imgsrc="<img例：在表xs中添加

12、两列.jsj,djsm Altertablexsadd(jxjsrc="">">">ber(1),djsmvarchar(40)default奖金1000')在表xs中修改名为djsm的列的默认值Altertalbexsmodify(djsmdefault奖金800')在表中删除名为jxj和djxm的列. Altertablexsdropcolumnjxj;Altertablexsdropcolumndjsm;修改表xs_kc的存储特征Altertalbexspctfree20maxtrans200为表xs_jsj加上主

13、键Altertablexs_jsjadd(constraintapk_jsj"primarykey(xh);增加列 语法：ALTERTABLEschema.table_nameADDcolumn_definitiontype例：ALTERTABLEordersADDorder_dateDATE;更改列语法：ALTERTABLEschema.table_nameMODIFYcolumn_namenew_attributes; 例：ALTERTABLEordersMODITY(quantitynumber(10,3),statusvarchar2(15);.更改表名：altertable

14、trenametos;.更改列名altertabletrenamecolumnntos;3约束Oracle数据库的完整性约束规则唯一性约束(Uniqueconstraint)唯一性约束可以保护表中多个数据列，保证在保护的数据列中任何两行的数据都不相同。唯一性约束与表一起创建，在唯一性约束创建后，可以使用ALTERTABLE语句修改。语法：column_namedata_typeCONSTRAINTconstraint_nameUNIQUE如果唯一性约束保护多个数据列，那么唯一性约束要作为表约束增加。语法如下：CONSTRAINTconstraint_name(column)UNIQUEUSIN

15、GINDEXTABLESPACE(tablespace_name)STORAGE(storedclause)唯一性约束由一个B-tree索引增强，所以可以在USING子串中为索引使用特殊特征，比如表空间或存储参数。CREATETABLE语句在创建唯一性约束的同时也给目标数据列建立了一个唯一的索引。F国内最早的网管网站CREATETABLEinsured_autos(policy_idNUMBERCONSTRAINTpk_policiesPRIMARYKEY,vinVARCHAR2(10),coverage_beginDATE,coverage_termNUMBER,CONSTRAINuniqu

16、e_autoUNIQUE(policy_id,vin)bitsCN_comUSINGINDEXTABLESPACEindexSTORAGE(INITIAL1MNEXT10MPCTINCREASE0)用户可以禁用未以性约束，但他仍然存在，禁用唯一性约束使用ALTERTABLE语句ALTERTABLEinsured_autosDISABLECONSTRAINunique_name;删除唯一性约束，使用ALTERTABLE.DROPCONSTRAIN语句ALTERTABLEinsured_autosDROPCONSTRAINunique_name;注意用户不能删除在有外部键指向的表的唯一性约束。这种

17、情况下用户必须首先禁用或删除外部键(foreignkey)。中国网管联盟删除或禁用唯一性约束通常同时删除相关联的唯一索引，因而降低了数据库性能。经常删除或禁用唯一性约束有可能导致丢失索引带来的性能错误。要避免这样错误，可以采取下面的步骤：1、在唯一性约束保护的数据列上创建非唯一性索引。2、添加唯一性约束主键(PrimaryKey)约束中国网管联盟表有唯一的主键约束。表的主键可以保护一个或多个列，主键约束可与NOTNULL约束共同作用于每一数据列。NOTNULL约束和唯一性约束的组合将保证主键唯一地标识每一行。像唯一性约束一样，主键由B-tree索引增强。网管求职创建主键约束使用CREATETA

18、BLE语句与表一起创建，如果表已经创建了，可以使用ALTERTABLE语句。CREATETABLEpolicies(policy_idNUMBERCONSTRAINTpk_policiesPRIMARYKEY,holder_nameVARCHAR2(40),genderVARCHAR2(1),marital_statusVARCHAR2(1),date_of_birthDATE);与唯一性约束一样，如果主键约束保护多个数据列，那么必须作为一个表约束创建。中国网管联盟CREATETABLEinsured_autos(policy_idNUMBER,vinVARCHAR2(40),coverage

19、_beginDATE,coverage_termNUMBER,CONSTRAINTpk_insured_autosPRIMARYKEY(policy_id,vin)USINGINDEXTABLESPACEindexSTORAGE(INITIAL1MNEXT10MPCTINCREASE0);禁用或删除主键必须与ALTERTABLE语句一起使用ALTERTABLEpoliciesDROPPRIMARYKEY;ALTERTABLEpoliciesDISABLEPRIMARYKEY;1、创建约束CREATETABLEstudents(student_idVARCHAR2(10)NOTNULL,stud

20、ent_nameVARCHAR2(30)NOTNULL,college_majorVARCHAR2(15)NOTNULL,statusVARCHAR2(20)NOTNULL,stateVARCHAR2(2),license_noVARCHAR2(30)TABLESPACEstudent_data;2、创建主键ALTERTABLEstudentsADDCONSTRAINTpk_studentsPRIMARYKEY(student_id)USINGINDEXTABLESPACEstudent_index;3、创建Unique约束ALTERTABLEstudentsADDCONSTRAINTuk_s

21、tudents_licenseUNIQUE(state,license_no)USINGINDEXTABLESPACEstudent_index;4、创建Check约束ALTERTABLEstudentsADDCONSTRAINTck_students_st_licCHECK(stateISNULLANDlicense_noISNULL)OR(stateISNOTNULLANDlicense_noisNOTNULL);添加check约束altertableempaddconstraintconcheck(dept_salary>0);con为约束名，dept_salary为字段名5、创建

22、外键约束ALTERTABLEstudentsADDCONSTRAINTfk_students_stateFOREIGNKEY(state)REFERENCESstate_lookup(state);6、约束.Altertabletable_nameaddconstrantsBIDprimarykey(bookno);ALERTTABLEtable_nameMODIFY(column1PRIMARYKEY);1、创建表的同时创建主键约束(1) 无命名createtablestudent(studentidintprimarykeynotnull,studentnamevarchar(8),age

23、int);(2) 有命名createtablestudents(studentidint,studentnamevarchar(8),ageint,constraintyyprimarykey(studentid);2、删除表中已有的主键约束(1) 有命名altertablestudentsdropconstraintyy;(2) 无命名可用SELECT*fromuser_cons_columnswhere.;查找表中主键名称得student表中的主键名为SYS_C002715altertablestudentdropconstraintSYS_C002715;(3)使约束失效：alterta

24、bletbl_employeedisableconstraintfk_emp;删除约束：altertabletbl_departmentdropconstraintpk_dept;查询约束：selectCONSTRAINT_NAMEfromuser_constraintswheretable_name='TBL_EMPLOYEE'selectCONSTRAINT_NAME,COLUMN_NAMEfromuser_cons_columnswheretable_name='TBL_EMPLOYEE'3. 删除表.Droptabletable_name;4、操作表数据

25、插入表记录：.a.insertintotable_namecol1,col2values(val1,val2);例：Insrtintoxs(xh,xm,)values('09',to_date('19860210','yyyymmdd);insertintodeptovalues('100','xieyunchao','m','22',to_date('19861104','yyyy-mm-dd'),10000)从一个表中向另一个表中插入数据Insertint

26、otable1(col1,col2,col3)select(col1,col2,col3)fromothertable使用子查询插入数据insertintoemployee(empno,ename,sal,deptno)selectempno,ename,sal,deptofromemp; INSERTINTOEMP(ENAME,HIREDATE)VALUE('AA',TO_DTE('19802-09','YYMIM-DD>).删除表数据： Deletefromtable_namewherecondition;.修改表记录Updatetable_n

27、amesetcolumn_name=expression,-wherecondition.基于一张表修改另一张表的数据UPDATEEMPLEESETDEPTNO=(SELECTDENPNOFROMEMPWHEREEMPNO=7788) WHEREJOB=(SELECTJOBFROMEMPWHEREEMPNO=7788).删除所有记录但保留表结构.Truncatetabletable_name;.查询数据查询表结构：DESCtable_nameSelectename,sal,12*sal+100fromemp注:selectcount(dinstinct(deptno)fromempa.查询大于

28、平均的：selectempnofromempa,(selectavg(sal)assal_salfromemp)bwherea.sal>b.sal_sal;如果列中有空值时，则结果也为空(关于null值的处理(p47).如：selectename,name,12*sal+comm如果comm的值为null,结果也为null;如上所示,comm为null时.则12*sal+comm也为null;解决方法是用nvl方法替换.在两个表中查询：以下两种方式都是一样的.a.selectt_phone_operation.operation_namefromt_phone_operation,t_p

29、hone_operation_chargewheret_phone_operation.operation_id=t_phone_operation_charge.operation_idandt_phone_operation_charge.phone_num='159.；b.selectoperation_namefromt_phone_operationwhereoperation_idin(selectoperation_idfromt_phone_operation_chargewherephone_num='159.'使用日期格式显示日期：selectena

30、me,to_char(hiredate,mm-yyyy)fromempselectenamewherehiredate>to_date(1999-12-31'','yyym-dd');使用别名的三种方式a.selectenameasname,salsalary,fromempb.selectename使用连接操作符：Selectename|jobas用连接字符：Selectename|'限制重复的行：name",sal*12"annualsalary"?"employees"fromemp|'

31、;isa'|''|jobasaemployeedetailsSelectdistinctdeptnofromemp注意大小写：Selectename,job,deptnofromempwherejob='CLERK使用between-.andl算符Selectename,salfromempwheresalbetween1000and1500;使用in运算符Selectempno,ename,sal,mgrfromempwheremgrin(23,231,2345);Like运算符：(模糊查询)%代表至多任意字符代表一个任意字符如：selectenamefrom

32、empwhereenamelike's%'；显示第三个字符为大写A的所有信息SELECTENAME,SALFROMEMPWHEREENAMELIKE'_A%'显示雇员名包含"_"的雇员信息(其中ESCAPE后的字符a为转义字符)SELECTENAME,SALWHEREENAMELIKE'%a_%ESCAPE'a'Null运算符(关于null值的处理(p47):)测试一个值是否为空：Selectename,mgrfromempwheremgrisnull(注意是isnull,不是=null)使用NVL函数处理NULL值N

33、VL函数用于将NULL转换为实际值，其语法为NVL(exp1,exp2).如果是exp1是null,则返回exp2，如果exp1不为null,则返回exp1,参数exp1和exp2是任意相同的数据类型.如：SELECTENAME,SAL,SAL+NVL(COM,0)AS”月收入"fromEMP;使用NVL2函数处理NULL值语法为NVL(exp1,exp2,exp3),如果exp1不是null,则返回exp2,如果exp1是null,则返回exp3;用WHERE语句限制日期示例一.符合默认日期格式select*fromempwherehiredate>'01-1月-82

34、'示例2:不符合默认日期格式(需要用TO_DATE函数转换)SELECT*FROMEMPWHEREHIREDATE>TO_DATE('1994-9-24','yyyy-mm-dd');逻辑操作符：AndornotOrderby子句a.使用orderby子句对查询数据排序asc升序，默认desc降序：依据多列进行排序：Selectename,deptno,salfromemporderbydeptno,saldesc总结：SELECTDISTINCT*,COLUMNALIAS,-./DISTINCT不重复FROMtablenameWHEREcondi

35、tionORDERBYcolumn,expr,aliasASC|DESC分组查询：常用分组函数：MAX,MIN,AVG,SUM,COUNT,VARIANCE(取方差),STDDEV(取标准差)当使用分组函数时，分组函数只能出现在选择列表，orderby，和having子句中，而不能出现在where和groupby子句中.当执行SELECT语句时，如果选择列表同时包含列，表达式，和分组函数，那么这些列和表达式必须出现在GROUPBY子句中.selectdeptno,avg(sal),count(*)fromempgroupbydeptno;havingavg(sal)<2000;做口果在s

36、elect语句中同时包含groupby,having，以及orderby子句，则必须将orderby子句放在最后.呻限制分组结果时，必须要使用having,而不能在WHERE子句中使用分函数限制分组显示结果.子查询：子查询的作用：在insert或createtable语句中使用子查询，可以将表数据插入到目标表中通过update子句中使用子查询，可以修改一列事多列数据通过在where,having,startwith子句中使用子查询，可以提供条件值单行子查询：selectename,sal,deptnofromempwheredeptno=(selectdeptnofromempwhereename='scott多行子查询：selectename,job,sal,deptno