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

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约束是我们用到的最多的约束之一了。我们可以在创建表时让系统自动指定not null约束的名字来创建，也可以手动的的指定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_nullmodify(name not null);如果你执行了刚才我们的插入数据的操作，你执行这句话的时候可能会有以下错误：ORA-02296: 无法启用 (SYS.) - 找到空值这是因为我们刚才在插入数据时已经给name这一列增加了一个空值，现在又要在这一列上增加not null约束，显然是不行的。如果实际问题中我们真的遇到这种需求，例如对于某个字段我们认为原来可以为空，并且再该列插入了很多空值，后来我们发现实际上是不应该为空的，可能会需先将原来的空值全部更新到一个新的有统一意义的值，然后在进行空值约束的加入操作。例如我们可以这样做：update test_not_nullset name = 不知道where name is null;现在我们再增加列的not null约束是没有问题了。alter table test_not_nullmodify(name not null);表修改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 table create table DEPT ( DEPTNO NUMBER(2) not null, DNAME VARCHAR2(14), LOC VARCHAR2(13) ) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); - Create/Recreate primary, unique and foreign key constraints alter table DEPT add constraint PK_DEPT primary key (DEPTNO) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); c.规则 Alter table 命令有许多选项，一个记住语法的方法是Oracle执行这个操作需要的信息： 1）、你不得不告诉Oracle你准备alter什么表： Alter table table_name 2)、然后，你准备做什么？Adding 一个约束 ALTER TABLE table_name ADD CONSTRAINT 3）、强烈建议但不要求为约束定义个名字。约束名不需要放在引号里，但会以大写字母形式存储在数据字典里， ALTER TABLE temp ADD CONSTRAINT pk_temp 4）、表示约束类型将是Primary Key，Unique，Foreign Key，或Check约束 ALTER TABLE temp ADD CONSTRAINT pk_temp PRIMARY KEY 5)、跟在约束类型后面有几种特殊选项，Primary Key和Unique约束，需要指定约束的列；Check约束需要指定约束规则。 ALTER TABLE temp ADD CONSTRAINT pk_temp PRIMARY KEY (ID); 6)、Primary Key和Unique约束应当指定索引的表空间名，使用USING INDEX TABLESPACE子句. ALTER TABLE temp ADD CONSTRAINT pk_temp PRIMARY KEY (ID) USING INDEX TABLESPACE student_index; 创建表及索引: create table DEPT ( DEPTNO NUMBER(2) not null, 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 ) ) alter table DEPT add constraint PK_DEPT primary key (DEPTNO) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited );.修改表. Alter table table_name Add column_name type default expression 增加新列 Modify datatype default expression 修改已有列和属性Storage storage_clause 修改存储特征Drop drop_clause 删除约束条件a.改变表所在的表空间 alter table name move tablespace newtablespace 例：在表xs中添加两列.jsj,djsm Alter table xs add(jxj numimg src=img src=ber(1),djsm varchar(40) default 奖金1000)； 在表xs中修改名为djsm的列的默认值Alter talbe xs modify(djsm default 奖金800) 在表中删除名为jxj和djxm的列. Alter table xs drop column jxj; Alter table xs drop column djsm; 修改表xs_kc的存储特征Alter talbe xs pctfree 20 maxtrans 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 DATE; 更改列 语法：ALTER TABLE schema. table_name MODIFY column_name new_attributes; 例：ALTER TABLE orders MODITY (quantity number(10,3),status varchar2(15); .更改表名: alter table t rename to s; .更改列名 alter table t rename column n to s; 3.约束Oracle数据库的完整性约束规则 唯一性约束(Unique constraint) 唯一性约束可以保护表中多个数据列，保证在保护的数据列中任何两行的数据都不相同。唯一性约束与表一起创建，在唯一性约束创建后，可以使用ALTER TABLE语句修改。 语法： column_name data_type CONSTRAINT constraint_name UNIQUE 如果唯一性约束保护多个数据列，那么唯一性约束要作为表约束增加。语法如下： CONSTRAINT constraint_name (column) UNIQUE USING INDEX TABLESPACE (tablespace_name) STORAGE (stored clause) 唯一性约束由一个B-tree索引增强，所以可以在USING子串中为索引使用特殊特征，比如表空间或存储参数。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 unique_auto UNIQUE (policy_id,vin) bitsCN_com USING INDEX TABLESPACE index STORAGE (INITIAL 1M NEXT 10M PCTINCREASE 0) )用户可以禁用未以性约束，但他仍然存在，禁用唯一性约束使用ALTER TABLE 语句 ALTER TABLE insured_autos DISABLE CONSTRAIN unique_name; 删除唯一性约束，使用ALTER TABLE.DROP CONSTRAIN语句 ALTER TABLE insured_autos DROP CONSTRAIN unique_name; 注意用户不能删除在有外部键指向的表的唯一性约束。这种情况下用户必须首 先禁用或删除外部键（foreign key)。 中国网管联盟 删除或禁用唯一性约束通常同时删除相关联的唯一索引，因而降低了数据库性能。经常删除或禁用唯一性约束有可能导致丢失索引带来的性能错误。要避免这样错误，可以采取下面的步骤： 1、在唯一性约束保护的数据列上创建非唯一性索引。 2、添加唯一性约束 主键(Primary Key)约束 中国网管联盟 表有唯一的主键约束。表的主键可以保护一个或多个列，主键约束可与NOT NULL约束共同作用于每一数据列。NOT NULL约束和唯一性约束的组合将保证主键唯一地标识每一行。像唯一性约束一样，主键由B-tree索引增强。 09网管求职 创建主键约束使用CREATE TABLE语句与表一起创建，如果表已经创建了，可以使用ALTER TABLE语句。 CREATE TABLE policies (policy_id NUMBER CONSTRAINT pk_policies PRIMARY KEY, holder_name VARCHAR2(40), gender VARCHAR2(1), marital_status VARCHAR2(1), date_of_birth DATE ); 与唯一性约束一样，如果主键约束保护多个数据列，那么必须作为一个表约束创建。 中国网管联盟 CREATE TABLE 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 DROP PRIMARY KEY; ALTER TABLE policies DISABLE PRIMARY KEY;1、创建约束 CREATE TABLE students (student_id VARCHAR2(10) NOT NULL, student_name VARCHAR2(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 TABLE students ADD CONSTRAINT pk_students PRIMARY KEY (student_id) USING INDEX TABLESPACE student_index;3、创建Unique约束 ALTER TABLE students ADD CONSTRAINT uk_students_license UNIQUE (state, license_no) USING INDEX TABLESPACE student_index;4、创建Check约束 ALTER TABLE students ADD CONSTRAINT ck_students_st_lic CHECK (state IS NULL AND license_no IS NULL) OR (state IS NOT NULL AND license_no is NOT NULL); 添加check约束 alter table emp add constraint con check(dept_salary0); con 为约束名，dept_salary为字段名 5、创建外键约束 ALTER TABLE students ADD CONSTRAINT fk_students_state FOREIGN KEY (state) REFERENCES state_lookup (state);6. 约束. Alter table table_name add constrants BID primary key (bookno); ALERT TABLE table_name MODIFY( column1 PRIMARY KEY); 1、创建表的同时创建主键约束 （1）无命名 create table student (studentid int primary key not null, studentname varchar(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_columnswhere .; 查找表中主键名称得student表中的主键名为SYS_C002715 alter table student drop constraint SYS_C002715; （) 使约束失效: alter table tbl_employee disable constraint fk_emp; 删除约束: alter table tbl_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 where table_name=TBL_EMPLOYEE; . 删除表. Droptable table_name;、操作表数据 插入表记录： . a. insert into table_name col1,col2 values (val1,val2);例：Insrt into xs(xh,xm,) values (09,to_date(19860210,yyyymmdd); insert into depto values(100,xieyunchao,m,22,to_date(19861104,yyyy-mm-dd),10000) b.从一个表中向另一个表中插入数据Insert into table1(col1,col2,col3) select (col1,col2,col3)from othertable c.使用子查询插入数据 insert into employee (empno,ename,sal,deptno) select empno,ename,sal,depto from emp; d.INSERT INTO EMP (ENAME,HIREDATE) VALUE(AA,TO_DATE(1980-12-09,YYYY-MM-DD). 删除表数据：Delete from table_name where condition;. 修改表记录Update table_name set column_name=expression,where condition. 基于一张表修改另一张表的数据 UPDATE EMPLEE SET DEPTNO=(SELECT DENPNO FROM EMP WHERE EMPNO=7788) WHERE JOB=(SELECT JOB FROM EMP WHERE EMPNO=7788). 删除所有记录但保留表结构.Truncate table table_name;.查询数据 查询表结构:DESC table_nameSelect ename,sal,12*sal+100 from emp 注:select count(dinstinct(deptno) from emp a.查询大于平均的： select empno from emp a,(select avg(sal) as sal_sal from emp) b where a.salb.sal_sal;如果列中有空值时,则结果也为空（关于null值的处理（p）.如：select ename,name,12*sal+comm如果comm的值为null，结果也为null; 如上所示,comm为null时.则12*sal+comm也为null;解决方法是用nvl方法替换. 在两个表中查询:以下两种方式都是一样的. a.select t_phone_operation.operation_name from t_phone_operation ,t_phone_operation_charge where t_phone_operation.operation_id=t_phone_operation_charge.operation_id and t_phone_operation_charge.phone_num=159.; b.select operation_name from t_phone_operation where operation_id in (select operation_id from t_phone_operation_charge where phone_num=159.使用日期格式显示日期：select ename,to_char(hiredate,yyyymm-dd) from empselect ename where hiredateto_date(1999-12-31,yyyy-mm-dd); 使用别名的三种方式: a. select ename as name,sal salary,from emp b. select ename ” name”, sal*12 ”annual salary” 使用连接操作符: Select ename | job as “employees” from emp 用连接字符: Select ename | | is a | |job as “employee details” 限制重复的行： Select distinct deptno from emp 注意大小写: Select ename,job,deptno from emp where job=CLERK 使用between .and 运算符 Select ename,sal from emp where sal between 1000 and 1500; 使用in 运算符 Select empno,ename,sal,mgr from emp where mgr in(23,231,2345); Like运算符：(模糊查询) %代表至多任意字符 代表一个任意字符 如：select ename from emp where ename like s%; 显示第三个字符为大写A的所有信息 SELECT ENAME ,SAL FROM EMP WHERE ENAME LIKE _A%; 显示雇员名包含_的雇员信息(其中ESCAPE后的字符a为转义字符) SELECT ENAME,SAL WHERE ENAME LIKE %a_% ESCAPE a; Null运算符(关于null值的处理（p）：) 测试一个值是否为空： Select ename,mgr from 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+