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.<\/p>\n
It does not allow empty values \u200b\u200bto be entered in the relevant column and ensures that the entered value is unique.<\/p>\n
Therefore, Primary Key is the combination of UNIQUE constraint and NOT NULL constraint.<\/p>\n
While defining constraints, they can be named by the user or created without specifying a name.<\/p>\n
In this case, Oracle gives the constraint a name starting with SYS_.<\/p>\n
SQL> CREATE TABLE SUBELER\r\n(\r\nSUBE_ID NUMBER (5) PRIMARY KEY,\r\nSUBE_ADI VARCHAR2(30)\r\n); \r\n\t\r\nTable created.\r\n<\/pre>\nSince we did not give a name to the Primary Key, its name started with SYS_.<\/p>\n
SQL> SELECT CONSTRAINT_NAME FROM DBA_CONS_COLUMNS WHERE TABLE_NAME='SUBELER';\r\n\t\r\nCONSTRAINT_NAME\r\n------------------------------\r\nSYS_C0011171\r\n<\/pre>\nIf a name is given to the Primary Key and it is queried, the name we gave will be returned.<\/p>\n
SQL> CREATE TABLE SUBELER_2\r\n(\r\nSUBE_ID NUMBER(5) CONSTRAINT SUBE_PK PRIMARY KEY,\r\nSUBE_ADI VARCHAR2(30)\r\n);\r\n\t\r\nTable created.\r\n\t\r\nSQL> SELECT CONSTRAINT_NAME FROM DBA_CONS_COLUMNS WHERE TABLE_NAME='SUBELER_2';\r\n\t\r\nCONSTRAINT_NAME\r\n------------------------------\r\nSUBE_PK\r\n<\/pre>\nWhen the Primary Key constraint is created, Oracle also creates a unique index with the same name.<\/p>\n
The created index can be created in the default tablespace of the user performing the operation, or in the tablespace we specify.<\/p>\n
SQL> SELECT INDEX_NAME, UNIQUENESS, TABLESPACE_NAME FROM DBA_INDEXES WHERE TABLE_NAME='SUBELER';\r\n\t\r\nINDEX_NAME UNIQUENES TABLESPACE_NAME\r\n\t------------------------------ --------- ------------------------------\r\nSYS_C0011171 UNIQUE SYSTEM\r\n<\/pre>\nSince the default Tablespace is SYSTEM, it was created here.<\/p>\n
When I specify USERS, it is created there.<\/p>\n
SQL> CREATE TABLE SUBELER_3\r\n(\r\nSUBE_ID NUMBER(5) CONSTRAINT SUBE3_PK PRIMARY KEY USING INDEX TABLESPACE USERS,\r\nSUBE_ADI VARCHAR2(30)\r\n);\r\n\t\r\nTable created.\r\n\t\r\nSQL> SELECT INDEX_NAME, UNIQUENESS, TABLESPACE_NAME FROM DBA_INDEXES WHERE TABLE_NAME='SUBELER_3';\r\n\t\r\nINDEX_NAME UNIQUENES TABLESPACE_NAME\r\n\t------------------------------ --------- ------------------------------\r\nSUBE3_PK UNIQUE USERS<\/pre>\nThere can be only one Primary Key constraint on a table. An error is received when a second one is created.<\/p>\n
SQL> ALTER TABLE SUBELER ADD CONSTRAINT SUBEADI_PK PRIMARY KEY (SUBE_ID);\r\nALTER TABLE SUBELER ADD CONSTRAINT SUBEADI_PK PRIMARY KEY (SUBE_ID)\r\n<\/pre>\nERROR at line 1:\u00a0<\/strong><\/p>\n
ORA-02260: table can have only one primary key<\/em><\/span><\/p>\n
A Primary Key constraint can be created on more than one column.<\/p>\n
SQL> CREATE TABLE SUBELER_4\r\n(\r\nSUBE_ID NUMBER(5),\r\nSUBE_ADI VARCHAR2(30)\r\n); \r\n\t\r\nTable created.\r\n\t\r\nSQL> ALTER TABLE SUBELER_4 ADD CONSTRAINT SUBEE_PK PRIMARY KEY (SUBE_ID, SUBE_ADI);\r\n\t\r\nTable altered.\r\n<\/pre>\n<\/div>\n