By the way in which, that is the third article associated to a major key in SQL, the opposite being the distinction between major and distinctive key and Learn how to discover the second highest wage in SQL. If you’re getting ready for any technical job interview the place you count on some SQL questions, take a look at these questions, they’re price getting ready.
What’s the Overseas key in a desk within the Database?
A Overseas secret’s a column in a single desk which is the first key on one other desk. A international key and a Main key are used to outline the connection between two tables in a relational database. For instance in Worker and Division relationship, now we have two tables Division(dept_id, dept_name) and Worker (emp_id, emp_name, dept_id). dept_id is the major key within the Division desk and the international key within the Worker desk. Although it’s not required that title of a international key have to be the identical with major key, now we have stored it the identical as per normal SQL finest practices. Overseas key in a desk implement Referential Integrity constraint, which can be utilized to implement enterprise guidelines e.g. referential integrity can cease you from creating an Worker with a non existent division.
This sort of verify maintains integrity of knowledge in a relationship. As mentioned in our put up What’s referential integrity in MySQL database, now we have seen that it is carried out as international key constraint and might permit CASCADE UPDATE and DELETE. These referential motion delete or replace matching column in little one desk ( international key desk) when corresponding row from mother or father desk (major key desk ) is deleted or up to date to keep up integrity of knowledge.
Distinction between Main key and Overseas key in SQL
Right here is a few necessary distinction between major and international keys in a desk which is price remembering each on SQL interview viewpoint and information viewpoint.
1) Title of international key will be completely different than the title of major key it characterize in different desk. For instance in our Worker and Division relationship, Main key in Division desk is dept_id and now we have used similar title in Worker desk to create international key. It might have been completely different e.g. departmentId or departmentID t and so forth.
2) One other distinction between major and international secret’s that not like major key, a international key will be null e.g. in our instance you possibly can have an Worker document for which dept_id will be null, this reveals that no corresponding document in Division desk.
3) Yet another distinction between major key and international key is that international key will be duplicate reverse to major key which is all the time distinctive.
6) Desk on which a column is said as a major key is named mother or father desk within the relationship and international key desk is named little one desk in a relationship. For instance in Worker and Division relationship, Division is mother or father desk as a result of dept_id is major key there and Worker is little one desk as a result of dept_id is a international key on this desk.
Main key and Overseas key Instance in SQL
Top-of-the-line examples to know the Main key and Overseas key in a desk is Worker and Division relationship or the Buyer and Order relationship. You may create an Order and Buyer desk in MySQL as following to create major and international keys :
cust_name VARCHAR(256),
PRIMARY KEY (cust_id)) ENGINE=INNODB;
CREATE TABLE ORDER (order_id INT NOT NULL,
quantity INT NOT NULL,
cust_id INT,
FOREIGN KEY (cust_id) REFERENCES Buyer(cust_id)
ON DELETE CASCADE) ENGINE=INNODB;
Now, cust_id is major key in Buyer desk and the international key in Order desk. If we attempt to insert an Order for which cust_id is one thing that’s invalid in Buyer desk, MySQL database will reject such INSERT or UPDATE. This is among the good thing about utilizing Referential Integrity.
It additionally permit to CASCADE UPDATE and DELETE operation which first delete or replace a row in mother or father desk e.g. Buyer after which delete or replace all matching rows in little one desk e.g. Order desk.
Different SQL Interview Questions for follow
