oracle PK问题
我列出我全部的做法:
table a 有id1, str1, str2, str3
开始的pk是id1, str1, str2
希望改成id1, str1, str3
--问题
小弟先有如下问题:
一个表原来的PK是 id1+str1+str2 列
先修改成id1+str1+str3列
而这三列现在当前数据库的数据有重复的情况, 小弟现在用sql:
ALTER table a add
constraint PK_a primary key (id1,str1,str3)
USING INDEX
TABLESPACE ts
ENABLE NOVALIDATE;
但是还是提示会出错,哪位大虾给帮忙一下~~
急啊!在线等!!!!
我的步骤:
Alter table a drop primary key drop index;
ALTER table a add
constraint PK_a primary key (id1, str1, str3)
USING INDEX
TABLESPACE ts
ENABLE NOVALIDATE;
提示的错误:
SQL>
ALTER table evanpk add
2 constraint PK_evanpk primary key
(id1, str1, str2)
USING INDEX TABLESPACE WBXOBJ_LARGE_IDX
3 4 ENABLE NOVALIDATE;
constraint PK_evanpk primary
key (id1, str1, str2)
*
ERROR at line 2:
ORA-02437: cannot validate (TEST.PK_EVANPK) - primary key violated
回答----原因分析
SQL> create table t1(c1 number,c2 varchar2(10));
表已创建。
SQL> insert into t1 values(1,1);
已创建 1 行。
SQL> insert into t1 values(1,2);
已创建 1 行。
SQL> alter table t1 add constraint pk_t1 primary key(c1) enable
novalidate;
alter table t1 add constraint pk_t1 primary key(c1)
enable novalidate
*
第 1 行出现错误:
ORA-02437: 无法验证 (TEST.PK_T1) - 违反主键
SQL> create
index idx_t1 on t1(c1);
索引已创建。
SQL> alter table
t1 add constraint pk_t1 primary key(c1) using index idx_t1 enab
le
novalidate;
表已更改。
相关文档:
UpSert功能:
MERGE INTO
USING
ON ()
WHEN MATCHED THEN
WHEN NOT MATCHED THEN ;
MultiTable Inserts功能:
Multitable inserts allow a single INSERT INTO .. SELECT statement to ......
Oracle JDeveloper 10g Release Download
Oracle JDeveloper 10g (version 9.0.5.1, build 1605) for Windows NT/2000/X, Linux, Solaris, and HP-UX.
http://download.oracle.com/otn/java/jdeveloper/905/jdev9051.zip
http://download-east.oracle.com/otn/java/jdeveloper/905/jdev9051.zip
http://download-west ......
--日期转换毫秒
SELECT TO_NUMBER(TO_DATE('2005-03-29 12:30:45', 'YYYY-MM-DD HH24:MI:SS') -
TO_DATE('1970-01-01 8:0:0', 'YYYY-MM-DD HH24:MI:SS')) * 24 * 60 * 60 * 1000
from DUAL;
--毫秒转换日期
SELECT TO_CHAR(1112070645000 / (1000 * 60 * 60 * 24) +
TO_ ......
过程、函数
create or replace procedure p1
is
empname emp.ename%type;
begin
select ename into empname from emp where empno=7788;
dbms_output.put_line(empname);
end;
SQL> ed
SQL> /
Procedure created
SQL> exec p1; ......
Oracle SQL PLUS 基本操作1
登录
c:\>sqlplus "sys/test1234 as sysdba" 以sysdba身份登录
c:\>sqlplus/nolog 以nolog身份登录
sql> connect sys/test1234 as sysdba
Connected.
启动
SQL> startup &nb ......