Oracle PL/SQL/Constraints/Contraint Name — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
(нет различий)
|
Текущая версия на 13:02, 26 мая 2010
Add constraint to a column and give it a name
<source lang="sql">
SQL> SQL> create table emp_1
2 (emp_id number constraint pk_emp_id primary key, 3 firstname varchar2(15) constraint firstname_nn not null, 4 lastname varchar2(20) constraint lastname_nn not null, 5 midinit char(1) );
Table created. SQL> SQL> drop table emp_1; Table dropped.
</source>
Name the constraint
<source lang="sql">
SQL> --name the constraint. SQL> -- creates a unique constraint on the columns a and b and names the constraint uq_myTable. SQL> SQL> create table myTable (
2 a number, 3 b number, 4 c number, 5 constraint uq_myTable unique (a,b) 6 );
Table created. SQL> 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.UQ_MYTABLE) 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>
SYS.SYS_C004353 is the contraint name
<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>