[Oracle]测试联合主键得唯一性.sql

[Oracle]测试联合主键得唯一性.sql

create table test1 ( col1 integer not null, col2 integer );

create index test1_col1_ix on test1 (col1);

alter table test1 
add constraint test1_pk primary key (col1) 
using index test1_col1_ix;

insert into test1  values (1,1);

insert into test1  values (1,1);

select * from user_indexes where table_name = 'TEST1';

select * from user_constraints where table_name = 'TEST1';

10:29:31 hotel@ET_HOTEL> insert into test1  values (1,1);

已创建 1 行。

10:29:39 hotel@ET_HOTEL> insert into test1  values (1,1);
insert into test1  values (1,1)
*
第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (HOTEL.TEST1_PK)

alter table test1 drop constraint sys_c003750;

select 'alter table '||owner||'.'||table_name||' drop constraint '||constraint_name||' ;'
from dba_constraints
where constraint_type in ('U', 'P')
   and (index_owner, index_name) in
       (select owner, segment_name
          from dba_segments
		 where table_name = 'TEST1');

alter table HOTEL.TEST1 drop constraint TEST1_PK;

drop index TEST1_COL1_IX;

-----------测试联合主键得唯一性----------
create table test1 ( col1 integer not null, col2 integer, D_DAY_OF_MONTH date);

insert into test1  values (1,2,'2011-01-01');

insert into test1  values (1,3,'2011-01-01');

create index test1_col1_ix on test1 (col1,col2,D_DAY_OF_MONTH);
索引已创建。

alter table test1 
add constraint test1_pk primary key (col1,col2,D_DAY_OF_MONTH) 
using index test1_col1_ix;
表已更改。