Primary key vs Candidate Key
What is the difference betwixt principal key together with candidate key is to a greater extent than or less other pop SQL together with database interview questions which appears inwards diverse programming interviews instantly together with then? The concept of principal key together with candidate key is non but of import from interview betoken of sentiment but likewise on designing database together with normalization. By the way, this is my minute postal service virtually principal keys, In end one, nosotros accept seen a comparing of primary key vs unique key, which likewise happens to hold upwardly ane of the often asked database questions. By Definition principal key is a column or collection of columns, which uniquely define a row inwards a table. Candidate keys are keys which tin hold upwardly a principal key together with likewise able to uniquely position whatsoever row inwards the table.
In elementary terms, you lot may accept a couplet of Candidate keys together with you lot accept chosen ane of them every bit a principal key.
This alternative part is the most of import science inwards database design. Since entirely principal key tin accept clustered index inwards a tabular array piece unique keys tin accept a Nonclustered index, it's of import to pick out correct column or collection of columns every bit a principal key.
Often I select a column which is most often used inwards Where clause of SELECT query.
If you lot preparing for SQL interview or looking for to a greater extent than or less skillful SQL interview enquiry than you lot tin likewise depository fiscal establishment jibe the difference betwixt Correlated together with Noncorrelated subqueries together with When to occupation truncate vs delete inwards SQL.
In elementary terms, you lot may accept a couplet of Candidate keys together with you lot accept chosen ane of them every bit a principal key.
This alternative part is the most of import science inwards database design. Since entirely principal key tin accept clustered index inwards a tabular array piece unique keys tin accept a Nonclustered index, it's of import to pick out correct column or collection of columns every bit a principal key.
Often I select a column which is most often used inwards Where clause of SELECT query.
If you lot preparing for SQL interview or looking for to a greater extent than or less skillful SQL interview enquiry than you lot tin likewise depository fiscal establishment jibe the difference betwixt Correlated together with Noncorrelated subqueries together with When to occupation truncate vs delete inwards SQL.
Difference betwixt Candidate Key vs Primary Key
Before seeing the departure betwixt a Candidate key together with Primary key let's run across to a greater extent than or less similarities betwixt them inwards bullets points. 1) Both Primary together with Candidate keys tin uniquely position records inwards a tabular array on the database.
2) Both Primary together with Candidate keys accept constraints UNIQUE together with NOT NULL.
3) Primary key or Candidate keys tin hold upwardly either unmarried column or combination of multiple columns inwards a table.
Now from interview betoken of sentiment hither is the departure betwixt a Candidate key together with principal key inwards SQL tabular array on betoken format for slowly to shout out back :
1) There tin hold upwardly multiple Candidate keys inwards a tabular array inwards relation database e.g. Oracle, MySQL, Sybase or MSSQL but entirely ane principal key is permitted.
2) An event of Primary key together with Candidate key tin hold upwardly ID together with SSN discover inwards an Employee table, Since both tin position each employee uniquely they are candidate key together with anyone tin decease the principal key.
Now if you lot accept to pick out betwixt them every bit principal key, I volition decease ID every bit a primary key because SSN is sensitive information together with may non hold upwardly allowed/not security to occupation every bit String inwards queries every bit often every bit ID.
The minute argue of choosing ID over SSN every bit principal key tin hold upwardly the occupation of ID every bit principal tracking ID inside the arrangement together with its frequent occupation all over the place.
Once you lot pick out a principal key, All candidate key are similar unique keys.
Here is a prissy tabular array which explains a couplet of candidate keys (columns which are eligible to decease principal keys) together with how other keys decease alternate keys ane time a principal key is chosen:
Now if you lot accept to pick out betwixt them every bit principal key, I volition decease ID every bit a primary key because SSN is sensitive information together with may non hold upwardly allowed/not security to occupation every bit String inwards queries every bit often every bit ID.
The minute argue of choosing ID over SSN every bit principal key tin hold upwardly the occupation of ID every bit principal tracking ID inside the arrangement together with its frequent occupation all over the place.
Once you lot pick out a principal key, All candidate key are similar unique keys.
Here is a prissy tabular array which explains a couplet of candidate keys (columns which are eligible to decease principal keys) together with how other keys decease alternate keys ane time a principal key is chosen:
P.S. - If you lot are looking for online training/course to larn SQL from scratch, I advise you lot joining Introduction to SQL yesteryear Jon Flanders. It's ane of the best sources to larn SQL fundamentals e.g. join, subquery, aggregate functions, window functions, grouping data, advanced filtering together with SQL query optimization.
No comments:
Post a Comment