In today’s article, we will be explaining the concept of a primary key in Oracle, which is a single field or combination of fields that uniquely defines a record.
It does not allow empty values to be entered in the relevant column and ensures that the entered value is unique.
Therefore, Primary Key is the combination of UNIQUE constraint and NOT NULL constraint.
While defining constraints, they can be named by the user or created without specifying a name.
In this case, Oracle gives the constraint a name starting with SYS_.
|
1 2 3 4 5 6 7 |
SQL> CREATE TABLE SUBELER ( SUBE_ID NUMBER (5) PRIMARY KEY, SUBE_ADI VARCHAR2(30) ); Table created. |
Since we did not give a name to the Primary Key, its name started with SYS_.
|
1 2 3 4 5 |
SQL> SELECT CONSTRAINT_NAME FROM DBA_CONS_COLUMNS WHERE TABLE_NAME='SUBELER'; CONSTRAINT_NAME ------------------------------ SYS_C0011171 |
If a name is given to the Primary Key and it is queried, the name we gave will be returned.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SQL> CREATE TABLE SUBELER_2 ( SUBE_ID NUMBER(5) CONSTRAINT SUBE_PK PRIMARY KEY, SUBE_ADI VARCHAR2(30) ); Table created. SQL> SELECT CONSTRAINT_NAME FROM DBA_CONS_COLUMNS WHERE TABLE_NAME='SUBELER_2'; CONSTRAINT_NAME ------------------------------ SUBE_PK |
When the Primary Key constraint is created, Oracle also creates a unique index with the same name.
The created index can be created in the default tablespace of the user performing the operation, or in the tablespace we specify.
|
1 2 3 4 5 |
SQL> SELECT INDEX_NAME, UNIQUENESS, TABLESPACE_NAME FROM DBA_INDEXES WHERE TABLE_NAME='SUBELER'; INDEX_NAME UNIQUENES TABLESPACE_NAME ------------------------------ --------- ------------------------------ SYS_C0011171 UNIQUE SYSTEM |
Since the default Tablespace is SYSTEM, it was created here.
When I specify USERS, it is created there.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SQL> CREATE TABLE SUBELER_3 ( SUBE_ID NUMBER(5) CONSTRAINT SUBE3_PK PRIMARY KEY USING INDEX TABLESPACE USERS, SUBE_ADI VARCHAR2(30) ); Table created. SQL> SELECT INDEX_NAME, UNIQUENESS, TABLESPACE_NAME FROM DBA_INDEXES WHERE TABLE_NAME='SUBELER_3'; INDEX_NAME UNIQUENES TABLESPACE_NAME ------------------------------ --------- ------------------------------ SUBE3_PK UNIQUE USERS |
There can be only one Primary Key constraint on a table. An error is received when a second one is created.
|
1 2 |
SQL> ALTER TABLE SUBELER ADD CONSTRAINT SUBEADI_PK PRIMARY KEY (SUBE_ID); ALTER TABLE SUBELER ADD CONSTRAINT SUBEADI_PK PRIMARY KEY (SUBE_ID) |
ERROR at line 1:
ORA-02260: table can have only one primary key
A Primary Key constraint can be created on more than one column.
|
1 2 3 4 5 6 7 8 9 10 11 |
SQL> CREATE TABLE SUBELER_4 ( SUBE_ID NUMBER(5), SUBE_ADI VARCHAR2(30) ); Table created. SQL> ALTER TABLE SUBELER_4 ADD CONSTRAINT SUBEE_PK PRIMARY KEY (SUBE_ID, SUBE_ADI); Table altered. |
