DUP_VAL_ON_INDEX exception. : Add Primary Key « Constraints « Oracle PL / SQL

DUP_VAL_ON_INDEX exception.

   
SQL>
SQL>
SQL>
SQL> CREATE TABLE lecturer (
  2    id               NUMBER(5) PRIMARY KEY,
  3    first_name       VARCHAR2(20),
  4    last_name        VARCHAR2(20),
  5    major            VARCHAR2(30),
  6    current_credits  NUMBER(3)
  7    );

Table created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10001, 'Scott', 'Lawson','Computer Science', 11);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
  2                VALUES (10002, 'Mar', 'Wells','History', 4);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10003, 'Jone', 'Bliss','Computer Science', 8);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10004, 'Man', 'Kyte','Economics', 8);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10005, 'Pat', 'Poll','History', 4);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10006, 'Tim', 'Viper','History', 4);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10007, 'Barbara', 'Blues','Economics', 7);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10008, 'David', 'Large','Music', 4);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10009, 'Chris', 'Elegant','Nutrition', 8);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10010, 'Rose', 'Bond','Music', 7);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10011, 'Rita', 'Johnson','Nutrition', 8);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10012, 'Sharon', 'Clear','Computer Science', 3);

1 row created.

SQL>
SQL> BEGIN
  2    INSERT INTO lecturer (id, first_name, last_name)
  3      VALUES (20000, 'John', 'Smith');
  4    INSERT INTO lecturer (id, first_name, last_name)
  5      VALUES (20000, 'Susan', 'Ryan');
  6  END;
  7  /
BEGIN
*
ERROR at line 1:
ORA-00001: unique constraint (JAVA2S.SYS_C006710) violated
ORA-06512: at line 4


SQL>
SQL> select * from lecturer;

      ID FIRST_NAME           LAST_NAME            MAJOR                          CURRENT_CREDITS
-------- -------------------- -------------------- ------------------------------ ---------------
######## Scott                Lawson               Computer Science                         11.00
######## Mar                  Wells                History                                   4.00
######## Jone                 Bliss                Computer Science                          8.00
######## Man                  Kyte                 Economics                                 8.00
######## Pat                  Poll                 History                                   4.00
######## Tim                  Viper                History                                   4.00
######## Barbara              Blues                Economics                                 7.00
######## David                Large                Music                                     4.00
######## Chris                Elegant              Nutrition                                 8.00
######## Rose                 Bond                 Music                                     7.00
######## Rita                 Johnson              Nutrition                                 8.00

      ID FIRST_NAME           LAST_NAME            MAJOR                          CURRENT_CREDITS
-------- -------------------- -------------------- ------------------------------ ---------------
######## Sharon               Clear                Computer Science                          3.00

12 rows selected.

SQL>
SQL> drop table lecturer;

Table dropped.

SQL>
SQL>

Related examples in the same category

1.	Add Primary Key
2.	Use alter table to add foreign key with cascade delete
3.	Use alter table to add foreign key with cascade delete for more than one column
4.	Alter a table to insert primary key and index
5.	Add primary key and try to insert default value to it
6.	Alter table to add primary key
7.	Alter table to primary key and check it in user_ind_columns and user_cons_columns
8.	Joined primary key
9.	Add primary key as the last statement
10.	Setting primary key as declaring the column