в 13:45, 26 мая 2010

2010-05-26T13:45:19Z

Admin: 1 версия

2010-05-26T10:01:59Z

1 версия

Новая страница

==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>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL/Constraints/Unique - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия