UNIQUE KEY AND CHECK KEY CONSTRAINT IN SQL

SQL> create table demo_unique(a number unique,b number);

Table created.

SQL> desc demo_unique;

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 A                                                  NUMBER

 B                                                  NUMBER

SQL> insert into demo_unique values(4,2);

1 row created.

SQL> insert into demo_unique values(4,2);

insert into demo_unique values(4,2)

*

ERROR at line 1:

ORA-00001: unique constraint (SCOTT.SYS_C003016) violated

SQL> insert into demo_unique values(6,2);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from demo_unique;

         A          B

---------- ----------

         4          2

         6          2

SQL> alter table demo_unique

  2  add (c number check (c > 50));

Table altered.

SQL> insert into demo_unique values(9,5,6);

insert into demo_unique values(9,5,6)

*

ERROR at line 1:

ORA-02290: check constraint (SCOTT.SYS_C003017) violated

SQL> insert into demo_unique values(9,5,60);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from demo_unique;

         A          B          C

---------- ---------- ----------

         4          2

         6          2

         9          5         60

SQL> update demo_unique set c=65 where a=4;

1 row updated.

SQL> update demo_unique set c=75 where a=6;

1 row updated.

SQL> select * from demo_unique;

         A          B          C

---------- ---------- ----------

         4          2         65

         6          2         75

         9          5         60

SQL> commit;

Commit complete.

SQL> create table demo (a number, constraint a_uni unique(a));

Table created.

SQL> alter table demo drop constraint a_uni;

Table altered.

SQL> drop table demo;

Table dropped.

SQL> create table demo (a number, constraint a_uni unique(a));

Table created.

SQL> insert into demo values(6);

1 row created.

SQL> insert into demo values(64);

1 row created.

SQL> commit;

Commit complete.

SQL> desc demo;

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 A                                                  NUMBER

SQL> select * from demo;

         A

----------

         6

        64

SQL> insert into demo values(64);

insert into demo values(64)

*

ERROR at line 1:

ORA-00001: unique constraint (SCOTT.A_UNI) violated

SQL> alter table demo disable constraint A_UNI;

Table altered.

SQL> insert into demo values(64);

1 row created.

SQL> alter table demo enable constraint a_uni;

alter table demo enable constraint a_uni

*

ERROR at line 1:

ORA-02299: cannot validate (SCOTT.A_UNI) - duplicate keys found

SQL> ed

Wrote file afiedt.buf

  1* alter table demo enable constraint A_UNI

  2  ;

alter table demo enable constraint A_UNI

*

ERROR at line 1:

ORA-02299: cannot validate (SCOTT.A_UNI) - duplicate keys found

SQL> select * from demo;

         A

----------

         6

        64

        64

SQL> delete from demo where a=64;

2 rows deleted.

SQL> alter table demo enable constraint A_UNI;

Table altered.

SQL> commit;

Commit complete.