- Trending Categories
- Data Structure
- Operating System
- C Programming
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
What do you mean by FOREIGN KEY and how can we use it in MySQL table?
Actually FOREIGN KEY is a column or combination of columns that can be used to set a link between the data in two tables. In other words, we can say that FOREIGN KEY constraint relates to two tables. It is also used to enhance data integrity because PRIMARY KEY of a table is linked to the FOREIGN KEY of other tables.
FOREIGN KEY [column_name] REFERENCES [table having Primary Key] ([column_name]);
Here REFERENCES is a keyword; column_name is the list of columns on which FOREIGN KEY is to be set; a table having Primary Key is the name of the table which contains PRIMARY KEY; column_name is the list of columns on which PRIMARY KEY has set.
Suppose we have two tables ‘Customer’ and ‘Orders’. The relationship between both the tables can be established by a foreign key in the table ‘orders’ specified by field ‘Cust_Id’. The query to create both the tables is as follows −
mysql> Create Table Customer(Cust_ID int Primary Key, First_name Varchar(20), Last_name Varchar(20), City Varchar(10)); Query OK, 0 rows affected (0.13 sec) mysql> Create Table Orders(Order_Id Int Primary Key, Product_Name Varchar(25), Orderdate DATE, Cust_ID Int, FOREIGN KEY(Cust_ID) REFERENCES Customer(Cust_id)); Query OK, 0 rows affected (0.13 sec) mysql> Describe Orders; +--------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+-------+ | Order_Id | int(11) | NO | PRI | NULL | | | Product_Name | varchar(25) | YES | | NULL | | | Orderdate | date | YES | | NULL | | | Cust_ID | int(11) | YES | MUL | NULL | | +--------------+-------------+------+-----+---------+-------+ 4 rows in set (0.04 sec)
Here, the table ‘Customer’ is called the Parent table or referenced table and the table ‘Orders’ is called child table or referencing table. A row in the child table must contain values that exist in the parent table. For example, each row in table ‘orders’ must have a ‘Cust_Id’ that exists in the ‘customer’ table.
- What do you mean by PRIMARY KEY and how can we use it in MySQL table?
- What do you mean by database view and how do MySQL views work?
- What do you mean MySQL user variables and how can we assign values to them?
- What do you mean by interfaces and services?
- What do you mean by C++ Tokens?
- What do you mean by corporate culture?
- What do you mean by compliance specialist?
- What do you mean by Teen Stress?
- What do you mean by performance testing?
- How can we remove FOREIGN KEY constraint from a column of an existing MySQL table?
- What do you mean by grammar and production in TOC?
- What do you mean by timeOut in TestNG?
- What do you mean by Listeners in TestNG?
- What do you mean by glue in Cucumber?
- What do you mean by schedule in DBMS?