Introduction:

Key play an important role in data integrity and an efficient access to the records in the relational databases. It is used to prevent the data from being duplicated. It maintains the reliable relationship between the tables. There are several types of keys which are below listed:

Primary Key

Candidate Key

Super Key

Foreign Key

Unique Key

Alternate Key

Primary Key:

It is applied on the columns of the table which helps in identifying each row or tuple uniquely. It enforces integrity constraint on the table. Only one primary key is applied in a table. It does not accept any null and duplicate values. It does not accept null and duplicate values. It's value is rarely changed in a table. Primary key in one table can acts as foreign key in another table.

Let us understand the concept with the help of student table example.

create table student (roll_no int not null,sname varchar2(15),dept_name varchar2(20),mobile_no varchar2(10),uid varchar2(20), primary key(roll_no));

It can be made from one or more table fields. It contains not null constraints.

Unique Key:

Unique key constraints are used to identify an individual tuple or row uniquely in the relation or table.Table can have more than one primary key. It can accept one null value in the table. Unique key constraint is referenced by the foreign key of another table. It can be enforced when the group of columns is not having primary key.

create table student (roll_no int not null,sname varchar2(15),dept_name varchar2(20),mobile_no varchar2(10),uid varchar2(20) unique, primary key(roll_no));

Every citizen of India must have unique id. If he/she is migrated to another country permanently, he or she will not have uid in that country.So,unique key will replace null value for that fields.

Difference:

PRIMARY KEY	UNIQUE KEY
It supports auto increment value.	It does not support auto increment value.
It creates the clustered index.	It does not create the clustered index.
One table can have only one primary key.	One table can have more than one unique key.
Primary key values cannot be changed or deleted.	Unique key can change
Null value cannot be accepted.	Null value can be accepted.
Primary key can be unique value.	Unique key cannot be primary key.

Comments

Difference between union and union all in oracle sql plsql

"This is one of the most important questions that interviewer might be asking in an interview.Infyosis,Linked group,Kyndryl,Vodafone Idea,IBM and Mphasis etc" INTRODUCTION: It is said to be set operators which is used to combine, add or concatenate two or more result sets. It is used in the multiple select statements where to combine to produce the final desired results.In other words,multiple select statements of similar column name in same order and having same data type to produce the unified result.In MIS datalake,when you are adding the modules of GST reconciliation which is to be used in micros in Excel or VB script, union and union all set operator is preferred for eliminating duplicate records or keeping duplicate records as per business requirement. Column must be of the same datatype for these operations of one select statement with another select statements. Columns must be in the same order in this set operators in the multiple select statements. Same number of co

Difference between decode and case oracle sql plsql interview question and answer

" This is the most accepted question for PLSQL developer role.It has been asked in many interviews like Mphasis and Kyndryl. " INTRODUCTION: Case was came after oracle database version 8.1.6 whereas decode was came before oracle database version 8.1.6 .Decode and case statements in an oracle work or acts like if then elsif conditional statements. if sal='2000000' then 'General Manager' elsif sal='3000000' then 'Senior Manager' elsif sal='1200000' then 'Manager' else 'Employee' end if; create table student_details(Student_RollNo Number,Student_Name Varchar2(20),student_marks Number,student_result varchar2(1)); insert into student_details values (100,'Rahul',90,'P'); insert into student_details values (101,'Rakesh',30,'F'); insert into student_details values (102,'Ram',60,'P'); insert into student_details values (103,'Rohan',10,'F'); insert into student_detai

Difference view and Materialized view in Oracle SQL PLSQL Interview Question and Answer.

" This is excepted question in oracle interview. It is asked in oracle technical support,PLSQL developer, SQL Analyst and SQL developer. " INTRODUCTION: View and Materialized view are called as the database object or the named query. View is the virtual table which stores the select statement from the base table by execution of the sql query at the runtime. View Query is running for the view definition. In the case of the materialized view, the data from the base table is loaded in the temporary or physical memory location by which the materialized view gets refreshed and all the dependent tables on the materialized view are also get updated after refresh operation. Materialized views are used in lakhs lines of code. In the data lake, tables are dependent on the master table to get updated where more than lakhs line of codes, today every reporting is based on this, in that case materialized view are used and preferred on the master tables. An activity of materialized view wi

ONLINE EDUCATION

Search This Blog

Difference Between Primary Key and Unique Key in oracle sql plsql interview question and answer.