Add primary key and try to insert default value to it : Add Primary Key « Constraints « Oracle PL / SQL






  1. Oracle PL / SQL
  2. Constraints
  3. Add Primary Key

Add primary key and try to insert default value to it

   
SQL>
SQL> CREATE TABLE DEPT (DEPTNO NUMBER(2),DNAME VARCHAR2(14),LOC VARCHAR2(13) );

Table created.

SQL>
SQL> INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');

1 row created.

SQL> INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS');

1 row created.

SQL> INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO');

1 row created.

SQL> INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');

1 row created.

SQL>
SQL>
SQL> create table another_dept as select * from dept;

Table created.

SQL>
SQL> alter table another_dept add constraint another_dept_pk primary key( deptno );

Table altered.

SQL>
SQL> insert into another_dept  values( 40, 'OPERATIONS', 'BOSTON' );
insert into another_dept  values( 40, 'OPERATIONS', 'BOSTON' )
*
ERROR at line 1:
ORA-00001: unique constraint (SYS.ANOTHER_DEPT_PK) violated


SQL> insert into another_dept (loc)values( 'RESTON' );
insert into another_dept (loc)values( 'RESTON' )
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SYS"."ANOTHER_DEPT"."DEPTNO")


SQL>
SQL> drop table another_dept;

Table dropped.

SQL> drop table dept;

Table dropped.

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.DUP_VAL_ON_INDEX exception.
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