Difference Betwixt Principal Commutation Vs Unusual Commutation Inwards Tabular Array

The master copy divergence betwixt Primary telephone substitution together with Foreign telephone substitution inward a tabular array is that it’s the same column which behaves every bit primary telephone substitution inward the nurture tabular array together with every bit a unusual telephone substitution inward a tike table. For instance inward Customer together with Order relationship, customer_id is the primary telephone substitution inward Customer tabular array precisely a unusual telephone substitution inward Order table. By the way, what is a unusual telephone substitution inward a tabular array together with divergence betwixt Primary together with Foreign telephone substitution are to a greater extent than or less of the pop SQL interview questions, much similar truncate vs delete inward SQL or difference betwixt correlated together with noncorrelated subquery? We accept been learning telephone substitution SQL concepts along amongst these often asked SQL questions together with inward this SQL tutorial, nosotros volition hash out what is a unusual telephone substitution inward SQL together with purpose of the unusual telephone substitution inward whatever table. By the way, this is the 3rd article related to a primary telephone substitution inward SQL, other beingness difference betwixt primary together with unique key together with How to notice instant highest salary inward SQL. If you lot are preparing for whatever technical project interview where you lot human face to a greater extent than or less SQL questions, cheque out these questions, they are worth preparing.

What is Foreign telephone substitution inward a table

primary key inward Department tabular array together with unusual telephone substitution inward Employee table.

Though it’s non require that elevate of unusual telephone substitution must hold out same amongst primary key, nosotros accept kept it same every bit per measure SQL best practices. Foreign telephone substitution inward a tabular array enforce Referential Integrity constraint, which tin hold out used to implement concern rules e.g. referential integrity tin terminate you lot from creating an Employee amongst a non real department.

This form of cheque maintains integrity of information inward a relationship. As discussed inward our postal service What is referential integrity inward MySQL database, nosotros accept seen that it's implemented every bit unusual telephone substitution constraint together with tin allow CASCADE UPDATE together with DELETE. These referential activity delete or update matching column inward tike tabular array ( unusual telephone substitution table) when corresponding row from nurture tabular array (primary telephone substitution tabular array ) is deleted or updated to keep integrity of data.

Difference betwixt Primary telephone substitution together with Foreign telephone substitution inward SQL

Here is to a greater extent than or less of import divergence betwixt primary together with unusual keys inward a tabular array which is worth remembering both on SQL interview betoken of persuasion together with cognition betoken of view.

1) Name of foreign key tin hold out different than the elevate of primary key it correspond inward other table. For instance inward our Employee together with Department relationship, Primary telephone substitution inward Department tabular array is dept_id and nosotros accept used same elevate inward Employee tabular array to do unusual key. It could accept been different e.g. departmentId or departmentID t etc.

2) Another divergence betwixt primary together with unusual telephone substitution is that different primary key, a foreign telephone substitution tin hold out null e.g. inward our instance you lot tin accept an Employee tape for which dept_id tin hold out null, this shows that no corresponding tape inward Department table.

3) One to a greater extent than difference betwixt primary telephone substitution together with unusual key is that foreign telephone substitution tin hold out duplicate contrary to primary telephone substitution which is ever unique.

4) By using unusual telephone substitution constraints, nosotros tin innovate referential integrity inward multiple tabular array human relationship inward SQL. Referential integrity guarantees information integrity, encounter benefits of Referential Integrity inward SQL to know more.

5) Foreign telephone substitution generally plant every bit a link betwixt 2 tabular array when nosotros bring together tables using INNER JOIN together with OUTER JOIN. For example, when nosotros INNER JOIN both Employee amongst Department table, nosotros tin job dept_id every bit joining column. See How to bring together 3 tables inward SQL for to a greater extent than details.

6) Table on which a column is declared every bit a primary key is known every bit nurture tabular array inward the human relationship together with foreign key tabular array is known every bit tike tabular array inward a relationship. For instance inward Employee together with Department relationship, Department is nurture tabular array because dept_id is primary telephone substitution at that spot together with Employee is tike tabular array because dept_id is a unusual telephone substitution inward this table.

Primary telephone substitution together with Foreign telephone substitution Example inward SQL

One of the best instance to empathise Primary telephone substitution together with Foreign telephone substitution inward a tabular array is Employee together with Department human relationship or Customer together with Order relationship. You tin do Order together with Customer tabular array inward MySQL every bit next to do primary together with 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;

Now cust_id is primary telephone substitution inward Customer tabular array together with unusual telephone substitution inward Order table. If nosotros elbow grease to insert an Order for which cust_id is something which is invalid inward Customer table, MySQL database volition turn down such INSERT or UPDATE. This is i of the do goodness of using Referential Integrity. It likewise allow to CASCADE UPDATE together with DELETE functioning which commencement delete or update a row inward nurture tabular array e.g. Customer together with thence delete or update all matching rows inward tike tabular array e.g. Order table.

That's all on what is unusual telephone substitution inward a tabular array together with divergence betwixt primary together with unusual telephone substitution inward SQL. I advise to do to a greater extent than or less tabular array yesteryear yourself together with elbow grease to seek unusual telephone substitution constraint yesteryear violating it together with encounter how database e.g. Oracle, MySQL or SQL Server behaves. To empathise to a greater extent than elbow grease ON DELETE CASCADE together with ON DELETE UPDATE to encounter how database maintains unusual telephone substitution constraint. You tin likewise encounter my postal service on Referential Integrity instance on MySQL database

Further Learning
What are ACID properties of whatever database transaction?

How to separate string inward stored physical care for using PATINDEX together with CHARINDEX function

How to re-create or backup tables using SQL query

The divergence betwixt clustered vs non-clustered index inward SQL.

How to do auto-incremented identity column inward MySQL, Oracle, Sybase together with SQL Server

Sumber https://javarevisited.blogspot.com/