Similarly, when we talk about employees in a company, the employee ID is functioning as the Primary key for identification.<\/p>\n
Let us now understand the Syntax of creating the table with the Primary key specified.<\/p>\n
Syntax:<\/strong><\/p>\n A Foreign key is beneficial when we connect two or more tables so that data from both can be put to use parallelly.<\/p>\n A foreign key is a field or collection of fields in a table that refers to the Primary key of the other table. It is responsible for managing the relationship between the tables.<\/p>\n The table which contains the foreign key is often called the child table, and the table whose primary key is being referred by the foreign key is called the Parent Table.<\/p>\n For example:<\/strong> When we talk about students and the courses they have enrolled in, now if we try to store all the data in a single table, the problem of redundancy arises.<\/p>\n To solve this table, we make two tables, one the student detail table and the other department table. In the student table, we store the details of students and the courses they have enrolled in.<\/p>\n And in the department table, we store all the details of the department. Here the courseId acts as the Primary key for the department table whereas it acts as the Foreign key in the student table.<\/p>\n Let us now look at the syntax of creating a table with a foreign key.<\/p>\n Syntax:<\/strong><\/p>\n Query:<\/strong><\/p>\n Output:<\/strong><\/p>\n Query:<\/strong><\/p>\n Output:<\/strong><\/p>\n <\/p>\n a. Parent Table or the DataFlair Table<\/strong> Output:<\/strong><\/p>\n b. Child Table of the Location Table:<\/strong> Output:<\/strong><\/p>\n Example 1:<\/strong> Let us now try to view the name of employee, location and the location id by using both the DataFlair and Location table. Output:<\/strong><\/p>\n Example 2:<\/strong> Let us now view the locations where we have an office of DataFlair as of now. Output:<\/strong><\/p>\n 1. Drop Primary Key in SQL<\/strong> Query:<\/strong><\/p>\n Output:<\/strong><\/p>\n 2. Drop Foreign Key in SQL<\/strong> Query:<\/strong><\/p>\n Output:<\/strong><\/p>\n In this tutorial, we have seen what a Primary and a Foreign Key is. We now know the definition of both and the syntax of Primary and foreign key.<\/p>\n We have seen examples of how to use both the Primary and Foreign key to create tables. And understanding the relationship they maintain with each other.<\/p>\n We have further enhanced our understanding by going through examples and understanding the working of queries on both the constraint keys.<\/p>\n","protected":false},"excerpt":{"rendered":" In this tutorial, we will look at the concept of Primary and foreign key. Both the Primary and the foreign key are SQL constraints. Constraints in SQL help us to manage the data and...<\/p>\n","protected":false},"author":6,"featured_media":87037,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[66],"tags":[3093,4858,8983,13402,13434,13435,13459,13512,13534,13550,13557],"class_list":["post-25144","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sql","tag-create-table-primary-key","tag-foreign-key-in-database","tag-mysql-add-foreign-key","tag-sql-add-foreign-key","tag-sql-create-table-foreign-key","tag-sql-create-table-primary-key","tag-sql-foreign-key","tag-sql-primary-key","tag-sql-server-add-primary-key","tag-sql-server-foreign-key","tag-sql-server-primary-key"],"yoast_head":"\nCREATE TABLE tableName (\r\n col1 int NOT NULL,\r\n col2 varchar(50) NOT NULL,\r\n col3 int,\r\n \u2026\u2026\u2026\u2026\u2026.\r\n PRIMARY KEY (col1)\r\n);\r\n<\/pre>\n
What is a Foreign key in SQL?<\/h3>\n
CREATE TABLE childTable (\r\n col1 int NOT NULL,\r\n col2 int NOT NULL,\r\n col3 int,\r\n \u2026\u2026\u2026...\r\n PRIMARY KEY (col1),\r\n FOREIGN KEY (col3) REFERENCES parentTable(parent_Primary_key)\r\n);\r\n<\/pre>\n
Understanding Primary and Foreign Key in detail with Examples<\/h3>\n
1. Creation of Parent Table DataFlair<\/h4>\n
CREATE TABLE DataFlair(\r\nemp_id varchar(5) NOT NULL,\r\nname varchar(50),\r\nlocation varchar(50),\r\nexperience int,\r\nPRIMARY KEY(emp_id));\r\n<\/pre>\n
![\"Create](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/08\/create-query-1.png\")
<\/a><\/p>\n2. Creation of Child Table Location<\/h4>\n
CREATE TABLE location(\r\nlocation_id varchar(5) NOT NULL,\r\nlocation varchar(50) NOT NULL,\r\noffice_size int,\r\nPRIMARY KEY(location_id),\r\nFOREIGN KEY(location) REFERENCES dataflair(location));\r\n<\/pre>\n
<\/a><\/p>\n3. Viewing the Parent and the Child Table<\/h4>\n
\nQuery:\u00a0<\/strong><\/p>\nSELECT * FROM DataFlair;\r\n<\/pre>\n
<\/a><\/p>\n
\nQuery:<\/strong><\/p>\nSELECT * FROM Location;\r\n<\/pre>\n
<\/a><\/p>\n4. Running Queries on the Parent and Child Table<\/h4>\n
\nQuery:<\/strong><\/p>\nSELECT emp_id , name, \r\nlocation.location_id,DataFlair.location \r\nFROM DataFlair RIGHT JOIN Location \r\nON DataFlair.location=Location.location \r\nWHERE emp_id IS NOT NULL;\r\n<\/pre>\n
<\/a><\/p>\n
\nQuery:<\/strong><\/p>\nSELECT DISTINCT(location.location_id) AS Location_ID,\r\nDataFlair.location AS Office_Location\r\nFROM DataFlair RIGHT JOIN Location \r\nON DataFlair.location=Location.location \r\nWHERE emp_id IS NOT NULL;\r\n<\/pre>\n
<\/a><\/p>\nHow to Drop Primary and Foreign key<\/h3>\n
\nSyntax:<\/strong><\/p>\nALTER TABLE tableName\r\nDROP PRIMARY KEY;\r\n<\/pre>\n
ALTER TABLE dataflair\r\nDROP PRIMARY KEY;\r\n<\/pre>\n
<\/a><\/p>\n
\nSyntax:<\/strong><\/p>\nALTER TABLE tableName\r\nDROP FOREIGN KEY foreignKeyName;\r\n<\/pre>\n
ALTER TABLE location\r\nDROP FOREIGN KEY location;\r\n<\/pre>\n
<\/a><\/p>\nDifference Between Primary and Foreign Key<\/h3>\n
\n\n
\n Sr.No<\/b><\/td>\n Primary Key<\/b><\/td>\n Foreign Key<\/b><\/td>\n<\/tr>\n \n 1<\/td>\n Used to maintain the unique identification of data in the table.<\/span><\/td>\n Used to maintain the relationship between two or more relational tables.<\/span><\/td>\n<\/tr>\n \n 2<\/td>\n Helps us to identify data in a database table.<\/span><\/td>\n Helps to identify the data in another table using the connection with the foreign key.<\/span><\/td>\n<\/tr>\n \n 3<\/td>\n A table can have only one Primary Key.<\/span><\/td>\n A table can have any number of Foreign Keys.<\/span><\/td>\n<\/tr>\n \n 4<\/td>\n The primary key is unique and Not Null.<\/span><\/td>\n A foreign key can contain duplicate values also.<\/span><\/td>\n<\/tr>\n \n 5<\/td>\n Primary key can\u2019t take Null as a value.<\/span><\/td>\n A foreign key can take NULL entries also.<\/span><\/td>\n<\/tr>\n \n 6<\/td>\n Primary Key can\u2019t be modified once entered.<\/span><\/td>\n A foreign key can be modified at any instance of time.<\/span><\/td>\n<\/tr>\n \n 7<\/td>\n We can have Primary keys for temporary tables as well.<\/span><\/td>\n We can’t have Foreign keys for the temporary tables.<\/span><\/td>\n<\/tr>\n \n 8<\/td>\n A Primary key can be defined on its own.<\/span><\/td>\n For defining a Foreign key, we need a parent table with a Primary Key.<\/span><\/td>\n<\/tr>\n \n 9<\/td>\n Primary key creates clustered indexes on the table.<\/span><\/td>\n Foreign key does not create indexes on the table neither clustered nor unclustered.<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n Summary<\/h3>\n