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
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.
Add unique containt to a varchar2 type column
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>
A unique constraint can be extended over multiple columns
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>
Create a table with "unique deferrable initially immediate"
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>
if a column is not explicitely defined as not null, nulls can be inserted multiple times
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;
ORA-00001: unique constraint (JAVA2S.JOB_UNIQUE_IN_TEAMID) violated
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>
Setting a Unique Constraint
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>
Unique value column
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>
Vialate the unique contraint: try to insert the same value
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>