The database is a collection of tables in addition to a tabular array is the collection of rows or records. H5N1 primary key is the information to uniquely position a row inward the table. For example, if an Employee tabular array has millions of rows in addition to emp_id is a primary key thus it tin uniquely position that row. You tin exercise this primary key to recall (SELECT) the row, update the row in addition to delete the row. In SQL, a tabular array tin cause got only i primary key but it tin comprise to a greater extent than than i columns. There are ii types of primary key, a uncomplicated primary key, in addition to a composite primary key. H5N1 uncomplicated primary key is made of simply i column spell a composite primary key is made of to a greater extent than than i columns. Primary key too enforces about constraint e.g. UNIQUE in addition to NOT NULL, which agency if yous endeavor to motility into about other tape alongside the primary key already existing inward the database than DBMS volition refuse it. Similarly, a primary key cannot endure NULL, it must cause got about value. On the other hand, the unusual key is simply the primary key of about other table. In a relational database, ii tables tin endure related to each other e.g. Employee in addition to Department, those cases, the primary key of i tabular array becomes the unusual key of about other table. In this article, yous volition larn about key departure betwixt the Primary in addition to unusual key inward a table. This is too i of the most common SQL Interview questions for 1 to 2 years sense programmers.
1) One of the major departure betwixt these ii keys is that primary key enforces clustered index, which is the actual physical social club of how information is stored inward the disc. On the other hand, Foreign key doesn't practise a clustered index inward the table.
2) The primary key is unique inward the table. So a tabular array cannot comprise to a greater extent than than i row alongside the same primary key, but that's non required for unusual key. You tin cause got to a greater extent than than i rows inward a tabular array alongside same unusual key e.g. all employees volition cause got a unique id but many of them working for the same subdivision volition cause got the same dept_id. This is i of the principal difference betwixt a unique key in addition to a primary key.
3) Foreign key helps to keep the integrity of related tables inward the database. For example, it's non possible to delete a department, unless a unmarried employee is referring that. So, an accidental delete of such subdivision volition endure prevented yesteryear database administration systems e.g. Oracle, MySQL or SQL SERVER. You tin farther setup cascade actions alongside unusual key e.g. when a subdivision is deleted, update the dept_id of all employees inward Employee tabular array referring to this department. You tin setup such organization using ON CASCADE clause.
Similarly, yous cannot insert the information inward the secondary tabular array if a row alongside the primary key doesn't be inward Primary key e.g. adding a non-existent subdivision to an employee's profile. This is too known as referential integrity in the database.
4) Another key departure betwixt the primary in addition to unusual key inward a tabular array is that primary key cannot convey null, but the unusual key tin convey multiple values.
5) The Primary key uniquely identifies a tape inward the table, but the unusual key tin non endure used for that purpose, it's simply the primary key of the related table.
Here is a prissy tabular array of about key differences betwixt the Primary in addition to Foreign key inward the table:
That's all virtually the difference betwixt Primary in addition to Foreign key inward SQL. It's i of the most of import concepts inward a relational database in addition to a skillful agreement of this is required to grapple or recall information from a commercial database similar Oracle 11g or Microsoft SQL SERVER 2014. It's of import for a beginner to sympathise these key SQL concepts, if yous are simply starting SQL, thus yous should banking concern fit out the Head First SQL book, i of the best books to larn SQL.
Some other SQL Interview Questions from this blog:
Difference betwixt Primary in addition to Foreign key inward table
As I said, if ii tabular array is inward relation alongside each other than primary key of i tabular array becomes the unusual key of about other table. Let's encounter about to a greater extent than differences betwixt Primary in addition to Foreign key inward SQL:1) One of the major departure betwixt these ii keys is that primary key enforces clustered index, which is the actual physical social club of how information is stored inward the disc. On the other hand, Foreign key doesn't practise a clustered index inward the table.
2) The primary key is unique inward the table. So a tabular array cannot comprise to a greater extent than than i row alongside the same primary key, but that's non required for unusual key. You tin cause got to a greater extent than than i rows inward a tabular array alongside same unusual key e.g. all employees volition cause got a unique id but many of them working for the same subdivision volition cause got the same dept_id. This is i of the principal difference betwixt a unique key in addition to a primary key.
3) Foreign key helps to keep the integrity of related tables inward the database. For example, it's non possible to delete a department, unless a unmarried employee is referring that. So, an accidental delete of such subdivision volition endure prevented yesteryear database administration systems e.g. Oracle, MySQL or SQL SERVER. You tin farther setup cascade actions alongside unusual key e.g. when a subdivision is deleted, update the dept_id of all employees inward Employee tabular array referring to this department. You tin setup such organization using ON CASCADE clause.
Similarly, yous cannot insert the information inward the secondary tabular array if a row alongside the primary key doesn't be inward Primary key e.g. adding a non-existent subdivision to an employee's profile. This is too known as referential integrity in the database.
4) Another key departure betwixt the primary in addition to unusual key inward a tabular array is that primary key cannot convey null, but the unusual key tin convey multiple values.
5) The Primary key uniquely identifies a tape inward the table, but the unusual key tin non endure used for that purpose, it's simply the primary key of the related table.
Here is a prissy tabular array of about key differences betwixt the Primary in addition to Foreign key inward the table:
That's all virtually the difference betwixt Primary in addition to Foreign key inward SQL. It's i of the most of import concepts inward a relational database in addition to a skillful agreement of this is required to grapple or recall information from a commercial database similar Oracle 11g or Microsoft SQL SERVER 2014. It's of import for a beginner to sympathise these key SQL concepts, if yous are simply starting SQL, thus yous should banking concern fit out the Head First SQL book, i of the best books to larn SQL.
Some other SQL Interview Questions from this blog:
- How to discovery the minute highest salary inward MySQL? (solution)
- What is the departure betwixt UNION in addition to UNION ALL inward SQL? (answer)
- The departure betwixt TRUNCATE in addition to DELETE inward SQL? (answer)
- The departure betwixt self in addition to equijoin inward SQL? (answer)
- What is the departure betwixt View in addition to Materialized View inward Oracle database? (answer)
- How to discovery minute highest salary inward Oracle using ROW_NUMBER? (answer)
- The departure betwixt WHERE in addition to HAVING clause inward SQL? (answer)
- The departure betwixt LEFT in addition to RIGHT OUTER JOIN inward SQL? (answer)
- How to discovery duplicate records inward a table? (query)
P.S. - If yous are looking for online training/course to larn SQL from scratch, I propose yous joining Introduction to SQL yesteryear Jon Flanders. It's i of the best sourse to larn SQL fundamentals e.g. join, subquery, aggregate functions, window functions, gropuing data, advanced filtering in addition to SQL query optimization.
0 Response to "Difference betwixt Primary in addition to Foreign fundamental inwards tabular array - SQL"
Posting Komentar