Keys in Oracle SQL

  • When we are working with databases, we store data in tables. 
  • Tables are a collection of the same type of record. A table has columns.
  • A “key” is a field in a table that is used in the identification of that record.

Types of key in oracle

  • primary keys
    • In Oracle, a primary key is a single field or combination of fields that uniquely defines a record.
    • None of the fields that are part of the primary key can contain a null value. 
    • A table can have only one primary key.
    • In Oracle, a primary key can not contain more than 32 columns.
    • A primary key can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.
    • In Oracle you can create, drop, disable and enable a primary key
  • Foreign keys
    • Foreign Key used to link two tables together.
    • The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent table.
    • Foreign key in a column(or a group of columns) of a table which maps to a primary key in another table.
    • Foreign Key may have duplicate & NULL values if it is defined to accept NULL values.
  • Composite keys
    • Composite Key refers to a group of two or more columns that can be used to identify a tuple from the table uniquely. 
    • A group of the column in combination with each other can identify a row uniquely but a single column of that group doesn’t promise to identify the row uniquely.
  • Unique keys
    • Unique Key can be a field or set of fields that can be used to uniquely identify the tuple from the database.
    • One or more fields can be declared as a unique Key. 
    • The unique Key column can also hold the NULL value.
    • Use of Unique Key improves the performance of data retrieval. It makes searching for records from the database much more faster & efficient.
  • Candidate keys
    • Candidate Key can be a column or group of columns that can qualify for the Unique Key. 
    • A table may have one or more Candidate Key. Each Candidate Key can work as a Primary Key if required in certain scenarios.
  • Super keys
    • Candidate Key is the subset of the Super Key. 
    • Candidate Key is also known as minimal Super Key.
    • If some more columns are added to a candidate key it can still uniquely identify a record in a table right? so all those possible combinations are super keys
  • Alternate Keys
    • Alternate Key is that Key which can be used as a Primary Key if required. 

Reference :

Leave a Comment