Oracle PL/SQL/Constraints/Unique
Содержание
- 1 Add unique constraints
- 2 Add unique containt to a varchar2 type column
- 3 A unique constraint can be extended over multiple columns
- 4 Create a table with "unique deferrable initially immediate"
- 5 if a column is not explicitely defined as not null, nulls can be inserted multiple times
- 6 ORA-00001: unique constraint (JAVA2S.JOB_UNIQUE_IN_TEAMID) violated
- 7 Setting a Unique Constraint
- 8 Unique value column
- 9 Vialate the unique contraint: try to insert the same value
Add unique constraints
<source lang="sql">
SQL> SQL> SQL> set echo off SQL> create table emp(
2 emp_id integer primary key using index (create index pk_idx on emp(emp_id) ) 3 ,lastname varchar2(20) constraint lastname_create_nn not null 4 ,firstname varchar2(15) constraint firstname_create_nn not null 5 ,midinit varchar2(1) 6 ,street varchar2(30) 7 ,city varchar2(20) 8 ,state varchar2(2) 9 ,zip varchar2(5) 10 ,shortZipCode varchar2(4) 11 ,area_code varchar2(3) 12 ,phone varchar2(8) 13 ,company_name varchar2(50) 14 ,constraint unique_emp_phone unique(phone) using index (create index emp_phone_u_idx on emp(phone)) 15 );
Table created. SQL> SQL> select index_name, table_name, column_name from user_ind_columns where table_name = "emp"; no rows selected SQL> SQL> select constraint_name, table_name, column_name from user_cons_columns where table_name = "emp"; no rows selected SQL> SQL> drop table emp cascade constraints; Table dropped.
</source>
Add unique containt to a varchar2 type column
<source lang="sql">
SQL> SQL> create table inventory(
2 partno number(4) constraint invent_partno_pk primary key, 3 partdesc varchar2(35) constraint invent_partdesc_uq unique);
Table created. SQL> SQL> SQL> SQL> drop table inventory; Table dropped. SQL>
</source>
A unique constraint can be extended over multiple columns
<source lang="sql">
SQL> -- A unique constraint can be extended over multiple columns: SQL> SQL> create table myTable (
2 a number, 3 b number, 4 c number, 5 unique (a,b) 6 );
Table created. SQL> SQL> insert into myTable values (4, 3, 5); 1 row created. SQL> insert into myTable values (4, 1, 5); 1 row created. SQL> insert into myTable values (4, 2, 5); 1 row created. SQL> insert into myTable values (4, 3, 5); insert into myTable values (4, 3, 5)
ERROR at line 1: ORA-00001: unique constraint (SYS.SYS_C004360) violated
SQL> SQL> select * from myTable;
A B C
---------- ----------
4 3 5
4 1 5
4 2 5
SQL> SQL> drop table myTable; Table dropped. SQL> SQL>
</source>
Create a table with "unique deferrable initially immediate"
<source lang="sql">
SQL> SQL> SQL> create table inventory(
2 partno number(4) constraint partno_pk primary key deferrable initially immediate, 3 partdesc varchar2(35) constraint partdesc_uq unique deferrable initially immediate);
Table created. SQL> SQL> SQL> drop table inventory; Table dropped. SQL> SQL> SQL>
</source>
if a column is not explicitely defined as not null, nulls can be inserted multiple times
<source lang="sql">
SQL> --Unique Key SQL> SQL> SQL> create table myTable (
2 a number unique, 3 b number 4 );
Table created. SQL> SQL> desc myTable;
Name Null? Type ----------------------------------------------------------------------------------------------------- -------- -------------------------------------------------------------------- A NUMBER B NUMBER
SQL> SQL> -- if a column is not explicitely defined as not null, nulls can be inserted multiple times: SQL> SQL> insert into myTable values (4, 5); 1 row created. SQL> insert into myTable values (4, 1); insert into myTable values (4, 1)
ERROR at line 1: ORA-00001: unique constraint (SYS.SYS_C004353) violated
SQL> insert into myTable values (9, 8); 1 row created. SQL> insert into myTable values (6, 9); 1 row created. SQL> insert into myTable values (null,9); 1 row created. SQL> insert into myTable values (null,9); 1 row created. SQL> SQL> select * from myTable;
A B
----------
4 5
9 8
6 9
9
9
SQL> SQL> drop table myTable;
</source>
ORA-00001: unique constraint (JAVA2S.JOB_UNIQUE_IN_TEAMID) violated
<source lang="sql">
SQL> create table emp
2 (emp_ID number primary key,
3 teamid number,
4 job varchar2(100),
5 status varchar2(20) check (status in ("ACTIVE", "INACTIVE"))
6 );
Table created. SQL> SQL> SQL> create UNIQUE index job_unique_in_teamid on emp
2 ( case when status = "ACTIVE" then teamid else null end, 3 case when status = "ACTIVE" then job else null end 4 ) 5 /
Index created. SQL> SQL> insert into emp(emp_id,teamid,job,status)values( 1, 10, "a", "ACTIVE" ); 1 row created. SQL> SQL> insert into emp(emp_id,teamid,job,status)values( 2, 10, "a", "ACTIVE" ); insert into emp(emp_id,teamid,job,status)values( 2, 10, "a", "ACTIVE" )
ERROR at line 1: ORA-00001: unique constraint (sqle.JOB_UNIQUE_IN_TEAMID) violated
SQL> SQL> SQL> update emp
2 set status = "INACTIVE" 3 where emp_id = 1 4 and teamid = 10 5 and status = "ACTIVE";
1 row updated. SQL> SQL> SQL> insert into emp(emp_id,teamid,job,status)values( 2, 10, "a", "ACTIVE" ); 1 row created. SQL> SQL> drop table emp; Table dropped. SQL>
</source>
Setting a Unique Constraint
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE myTable (
2 Name VARCHAR(50) NOT NULL, 3 PhoneNo VARCHAR(15) DEFAULT "Unknown Phone" NOT NULL, 4 CONSTRAINT MyUniqueKey UNIQUE (Name) 5 );
Table created. SQL> SQL> drop table myTable; Table dropped. SQL> SQL>
</source>
Unique value column
<source lang="sql">
SQL> SQL> SQL> SQL> create table t ( x int unique ); Table created. SQL> insert into t values ( 1 ); 1 row created. SQL> insert into t values ( 2 ); 1 row created. SQL> update t set x = x+1; 2 rows updated. SQL> SQL> drop table t; Table dropped. SQL>
</source>
Vialate the unique contraint: try to insert the same value
<source lang="sql">
SQL> --Unique Key SQL> SQL> SQL> create table myTable (
2 a number unique, 3 b number 4 );
Table created. SQL> SQL> desc myTable;
Name Null? Type ----------------------------------------------------------------------------------------------------- -------- -------------------------------------------------------------------- A NUMBER B NUMBER
SQL> SQL> insert into myTable values (4, 5); 1 row created. SQL> insert into myTable values (2, 1); 1 row created. SQL> insert into myTable values (2, 1); -- Dup
2 insert into myTable values (9, 8);
insert into myTable values (2, 1); -- Dup
*
ERROR at line 1: ORA-00911: invalid character
SQL> insert into myTable values (6, 9); 1 row created. SQL> insert into myTable values (null,9); 1 row created. SQL> insert into myTable values (null,9); 1 row created. SQL> SQL> select * from myTable;
A B
----------
4 5
2 1
6 9
9
9
SQL> SQL> drop table myTable; Table dropped. SQL> SQL> SQL>
</source>
