Skip to main content

primary key & Foreign key Relationship sql server

primary key & Foreign key Relation


CREATE TABLE table2 (
   id int PRIMARY KEY,
   name varchar(20)
) ENGINE=INNODB;

CREATE TABLE table1 (
   id int PRIMARY KEY,
   t2_id int,
   FOREIGN KEY (t2_id) REFERENCES table2 (id)
) ENGINE=INNODB;

INSERT INTO table2 VALUES (1, 'First Row');
INSERT INTO table2 VALUES (2, 'Second Row');

INSERT INTO table1 VALUES (1, 1);
INSERT INTO table1 VALUES (2, 1);
INSERT INTO table1 VALUES (3, 1);
INSERT INTO table1 VALUES (4, 2);
The tables now contain:
SELECT * FROM table1;
+----+-------+
| id | t2_id |
+----+-------+
|  1 |     1 |
|  2 |     1 |
|  3 |     1 |
|  4 |     2 |
+----+-------+
4 rows in set (0.00 sec)

SELECT * FROM table2;
+----+------------+
| id | name       |
+----+------------+
|  1 | First Row  |
|  2 | Second Row |
+----+------------+
2 rows in set (0.00 sec)
Now we can successfully delete rows like this:
DELETE FROM table1 WHERE id = 1;
Query OK, 1 row affected (0.00 sec)

DELETE FROM table1 WHERE t2_id = 2;
Query OK, 1 row affected (0.00 sec)
However we won't be able to delete the following:
DELETE FROM table2 WHERE id = 1;
ERROR 1451 (23000): A foreign key constraint fails
If we had defined the foreign key on table1 with the CASCADE option, we would have been able to delete the parent, and all the children would get deleted automatically:
CREATE TABLE table2 (
   id int PRIMARY KEY,
   name varchar(20)
) ENGINE=INNODB;

CREATE TABLE table1 (
   id int PRIMARY KEY,
   t2_id int,
   FOREIGN KEY (t2_id) REFERENCES table2 (id) ON DELETE CASCADE
) ENGINE=INNODB;

INSERT INTO table2 VALUES (1, 'First Row');
INSERT INTO table2 VALUES (2, 'Second Row');

INSERT INTO table1 VALUES (1, 1);
INSERT INTO table1 VALUES (2, 1);
INSERT INTO table1 VALUES (3, 1);
INSERT INTO table1 VALUES (4, 2);
If we were to repeat the previously failed DELETE, the children rows in table1 will be deleted as well as the parent row in table2:
DELETE FROM table2 WHERE id = 1;
Query OK, 1 row affected (0.00 sec)

SELECT * FROM table1;
+----+-------+
| id | t2_id |
+----+-------+
|  4 |     2 |
+----+-------+
1 row in set (0.00 sec)

SELECT * FROM table2;
+----+------------+
| id | name       |
+----+------------+
|  2 | Second Row |
+----+------------+
1 row in set (0.00 sec)


Labels:

Comments

Post a Comment

Popular posts from this blog

.NET Core API With Dapper, Repository And UnitOfWork

  Introduction In this tutorial, you will use ASP.NET Core Web API to create a web API that returns a list of brands. This article focuses on creating web API using dapper, repository pattern, and UnitOfWork pattern in .NET 8. I will guide you through the steps by step to create web API. HTTP is not just for serving up web pages. HTTP is also a powerful platform for building APIs that expose services and data. HTTP is simple, flexible, and ubiquitous. Almost any platform that you can think of has an HTTP library, so HTTP services can reach a broad range of clients, including browsers, mobile devices, and traditional desktop applications. ASP.NET Core Web API is a framework for building web APIs on top of the .NET Framework. Creating a New Project in Visual Studio Launch Visual Studio and select Create a New Project. Creating a Core Web API Start Visual Studio and choose Create a new project. In the Create a new project dialog, select ASP.NET Core Web API Click Next. Configuring You...
Post a Comment
Read more

.Net framework interview questions

What is the .NET Framework? The .NET Framework is a set of technologies that form an integral part of the .NET Platform. It is Microsoft’s managed code programming model for building applications. The .NET Framework has two main components: Common Language Runtime (CLR): The CLR is one of the foundation in the .NET framework and provides a common set of services for applications developed on Microsoft .Net Technologies. .NET Framework class library: The .NET framework class library is a collection of reusable types and exposes features of the runtime. It contains of a set of classes that is used to access common functionality. What is CTS (Common Type System)? The common type system (CTS) defines how types are declared, used, and managed in the runtime, and is also an important part of the runtime’s support for cross-language integration. The common type system performs the following functions: ·          Establishes a framework that...
Post a Comment
Read more

Move Selected Gridview Rows to Another Gridview in Asp.net

Introduction :  Here I will explain how to move or transfer selected checkbox gridview rows to another  grid v iew in asp.net using c#, vb.net or copy one gridview row to another gridview in asp.net using c#, vb.net. Description :  In previous posts I explained Display images in gridview from database in asp.net, Enable/Disable checkbox in gridview based on condition,  asp.net gridview examples  and  bind data to textbox control in gridview ,  Bind data to dropdownlist  in gridview  and many articles relating to  asp.net,  c#, vb.net, c#,vb.net. Now I will explain how to move selected rows from one gridview to another gridview in asp.netusing c#, vb.net. To implement this first we need to write the code in aspx page like this  < html   xmlns ="http://www.w3.org/1999/xhtml"> < head > ...
Post a Comment
Read more