- 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 :