The master copy divergence betwixt Primary primal as well as Foreign primal inwards a tabular array is that it’s the same column which behaves equally primary primal inwards the raise tabular array as well as equally a unusual primal inwards a fry table. For instance inwards Customer as well as Order relationship, customer_id is the primary primal inwards Customer tabular array only a unusual primal inwards Order table. By the way, what is a unusual primal inwards a tabular array as well as divergence betwixt Primary as well as Foreign primal are closed to of the pop SQL interview questions, much similar truncate vs delete inwards SQL or difference betwixt correlated as well as noncorrelated subquery? We bring been learning primal SQL concepts along amongst these oft asked SQL questions as well as inwards this SQL tutorial, nosotros volition speak over what is a unusual primal inwards SQL as well as purpose of the unusual primal inwards whatever table. By the way, this is the tertiary article related to a primary primal inwards SQL, other beingness difference betwixt primary as well as unique key as well as How to notice instant highest salary inwards SQL. If you lot are preparing for whatever technical undertaking interview where you lot human face closed to SQL questions, banking concern check out these questions, they are worth preparing.
What is Foreign primal inwards a table
primary key inwards Department tabular array as well as unusual primal inwards Employee table.
Though it’s non require that advert of unusual primal must last same amongst primary key, nosotros bring kept it same equally per measure SQL best practices. Foreign primal inwards a tabular array enforce Referential Integrity constraint, which tin hand notice last used to implement describe of piece of work organisation rules e.g. referential integrity tin hand notice terminal you lot from creating an Employee amongst a non rattling department.
This form of banking concern check maintains integrity of information inwards a relationship. As discussed inwards our post service What is referential integrity inwards MySQL database, nosotros bring seen that it's implemented equally unusual primal constraint as well as tin hand notice allow CASCADE UPDATE as well as DELETE. These referential activity delete or update matching column inwards fry tabular array ( unusual primal table) when corresponding row from raise tabular array (primary primal tabular array ) is deleted or updated to hold integrity of data.
Though it’s non require that advert of unusual primal must last same amongst primary key, nosotros bring kept it same equally per measure SQL best practices. Foreign primal inwards a tabular array enforce Referential Integrity constraint, which tin hand notice last used to implement describe of piece of work organisation rules e.g. referential integrity tin hand notice terminal you lot from creating an Employee amongst a non rattling department.
This form of banking concern check maintains integrity of information inwards a relationship. As discussed inwards our post service What is referential integrity inwards MySQL database, nosotros bring seen that it's implemented equally unusual primal constraint as well as tin hand notice allow CASCADE UPDATE as well as DELETE. These referential activity delete or update matching column inwards fry tabular array ( unusual primal table) when corresponding row from raise tabular array (primary primal tabular array ) is deleted or updated to hold integrity of data.
Difference betwixt Primary primal as well as Foreign primal inwards SQL
Here is closed to of import divergence betwixt primary as well as unusual keys inwards a tabular array which is worth remembering both on SQL interview betoken of sentiment as well as noesis betoken of view.
1) Name of foreign key tin hand notice last different than the advert of primary key it correspond inwards other table. For instance inwards our Employee as well as Department relationship, Primary primal inwards Department tabular array is dept_id and nosotros bring used same advert inwards Employee tabular array to do unusual key. It could bring been different e.g. departmentId or departmentID t etc.
2) Another divergence betwixt primary as well as unusual primal is that dissimilar primary key, a foreign primal tin hand notice last null e.g. inwards our instance you lot tin hand notice bring an Employee tape for which dept_id tin hand notice last null, this shows that no corresponding tape inwards Department table.
3) One to a greater extent than difference betwixt primary primal as well as unusual key is that foreign primal tin hand notice last duplicate reverse to primary primal which is e'er unique.
4) By using unusual primal constraints, nosotros tin hand notice innovate referential integrity inwards multiple tabular array human relationship inwards SQL. Referential integrity guarantees information integrity, run into benefits of Referential Integrity inwards SQL to know more.
5) Foreign primal by as well as large industrial plant equally a link betwixt ii tabular array when nosotros bring together tables using INNER JOIN as well as OUTER JOIN. For example, when nosotros INNER JOIN both Employee amongst Department table, nosotros tin hand notice occupation dept_id equally joining column. See How to bring together 3 tables inwards SQL for to a greater extent than details.
6) Table on which a column is declared equally a primary key is known equally raise tabular array inwards the human relationship as well as foreign key tabular array is known equally fry tabular array inwards a relationship. For instance inwards Employee as well as Department relationship, Department is raise tabular array because dept_id is primary primal at that topographic point as well as Employee is fry tabular array because dept_id is a unusual primal inwards this table.
Primary primal as well as Foreign primal Example inwards SQL
One of the best instance to sympathise Primary primal as well as Foreign primal inwards a tabular array is Employee as well as Department human relationship or Customer as well as Order relationship. You tin hand notice do Order as well as Customer tabular array inwards MySQL equally next to do primary as well as unusual keys :
CREATE TABLE Customer (cust_id INT NOT NULL,
cust_name VARCHAR(256),
PRIMARY KEY (cust_id)) ENGINE=INNODB;
CREATE TABLE ORDER (order_id INT NOT NULL,
sum INT NOT NULL,
cust_id INT,
FOREIGN KEY (cust_id) REFERENCES Customer(cust_id)
ON DELETE CASCADE) ENGINE=INNODB;
cust_name VARCHAR(256),
PRIMARY KEY (cust_id)) ENGINE=INNODB;
CREATE TABLE ORDER (order_id INT NOT NULL,
sum INT NOT NULL,
cust_id INT,
FOREIGN KEY (cust_id) REFERENCES Customer(cust_id)
ON DELETE CASCADE) ENGINE=INNODB;
Now cust_id is primary primal inwards Customer tabular array as well as unusual primal inwards Order table. If nosotros travail to insert an Order for which cust_id is something which is invalid inwards Customer table, MySQL database volition refuse such INSERT or UPDATE. This is i of the do goodness of using Referential Integrity. It likewise allow to CASCADE UPDATE as well as DELETE functioning which laid out delete or update a row inwards raise tabular array e.g. Customer as well as thus delete or update all matching rows inwards fry tabular array e.g. Order table.
That's all on what is unusual primal inwards a tabular array as well as divergence betwixt primary as well as unusual primal inwards SQL. I advise to do closed to tabular array yesteryear yourself as well as travail to attempt unusual primal constraint yesteryear violating it as well as run into how database e.g. Oracle, MySQL or SQL Server behaves. To sympathise to a greater extent than travail ON DELETE CASCADE as well as ON DELETE UPDATE to run into how database maintains unusual primal constraint. You tin hand notice likewise run into my post service on Referential Integrity instance on MySQL database
Further Learning
What are ACID properties of whatever database transaction?