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_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.完整的例子：
　　　　　 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 num">">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_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),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索引增强。 网管求职 创建主键约束使用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_salary>0);
　　　　　　 　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_columns　where ..;
查找表中主键名称得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';
３. 删除表.
　　　　 Drop　table 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_name
　　　　　　　　　Select ename,sal,12*sal+100 from emp
注:select count(dinstinct(deptno)) from emp
a.查询大于平均的：
sele