How I use Foreign Key

Question

1.00/5 (1 vote)

See more:

ASP.NET

Hi

Somebody please tell me how i use foreign key

Posted 3-Nov-14 0:37am

Arora1992

Add a Solution

3 solutions

Solution 1

How to use? Normally... to build relationship between tables.

See this:
http://www.w3schools.com/sql/sql_foreignkey.asp[^]
Create Foreign Key Relationships[^]

Posted 3-Nov-14 0:48am

Maciej Los

Comments

Arora1992 3-Nov-14 6:51am

I create a code Please See this

SqlConnection abc = new SqlConnection(@"Connection string ");
abc.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = abc;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "Email_insert_procedure";
cmd.Parameters.Add("@Id", SqlDbType.Int).Value = "125";
cmd.Parameters.Add("@Email", SqlDbType.VarChar).Value = "Mr";

and Thats Sql Table and Procedure

create table Email
(
Id int FOREIGN KEY (Id) REFERENCES contact(Id),
Email varchar(100) NOT NULL
)
go

create procedure Email_insert_procedure(@Id int, @Email varchar(100))as
insert into Email values (@Id, @Email)

Maciej Los 3-Nov-14 7:30am

How it is related to your question??

Arora1992 3-Nov-14 7:34am

I create one more table with the name of Contact in this table i store Id and Name . So I need same Id in Table Email and Email address of user . How i do this

Solution 3

When you create a relationship between 2 table, a master and a slave table. You use a foreign key to be used as a field to create the relationship on.

In your code (from your comment) it is clear that the ID that integer, would be a FOREIGN KEY. As Microsoft SQL Server documentation[^] says,

Quote:
A foreign key (FK) is a column or combination of columns that is used to establish and enforce a link between the data in two tables. You can create a foreign key by defining a FOREIGN KEY constraint when you create or modify a table.

Read the attached link, it has more information on this chapter and you will find more details to it there.

Thanks.

Posted 3-Nov-14 0:55am

Afzaal Ahmad Zeeshan

Comments

Arora1992 3-Nov-14 6:56am

I create a code Please See this

SqlConnection abc = new SqlConnection(@"Connection string ");
abc.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = abc;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "Email_insert_procedure";
cmd.Parameters.Add("@Id", SqlDbType.Int).Value = "125";
cmd.Parameters.Add("@Email", SqlDbType.VarChar).Value = "Mr";

and Thats Sql Table and Procedure

create table Email
(
Id int FOREIGN KEY (Id) REFERENCES contact(Id),
Email varchar(100) NOT NULL
)
go

create procedure Email_insert_procedure(@Id int, @Email varchar(100))as
insert into Email values (@Id, @Email)

Afzaal Ahmad Zeeshan 3-Nov-14 6:59am

..and Microsoft created a documentation for new developers, please see that.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Akashjoshi · Accepted Answer · 2014-11-03T00:53:00

Solution 2

all information regarding your question ...
http://www.1keydata.com/sql/sql-foreign-key.html[^]

Posted 3-Nov-14 0:53am

Akashjoshi

Comments

Arora1992 3-Nov-14 6:56am

I create a code Please See this

SqlConnection abc = new SqlConnection(@"Connection string ");
abc.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = abc;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "Email_insert_procedure";
cmd.Parameters.Add("@Id", SqlDbType.Int).Value = "125";
cmd.Parameters.Add("@Email", SqlDbType.VarChar).Value = "Mr";

and Thats Sql Table and Procedure

create table Email
(
Id int FOREIGN KEY (Id) REFERENCES contact(Id),
Email varchar(100) NOT NULL
)
go

create procedure Email_insert_procedure(@Id int, @Email varchar(100))as
insert into Email values (@Id, @Email)

Akashjoshi 3-Nov-14 7:07am

your defination is correct but first you insert data into contact table and those data is used in Email table...

Like
insert into contact VALUES(1)
insert into Email VALUES(1,'aka@gmail.com')

Arora1992 3-Nov-14 7:13am

I already Insert value into Contact table the id is same 125 but this query is nor working

Akashjoshi 3-Nov-14 7:23am

Try this...
insert into Email VALUES((select id from contact WHERE Id=@id),@email)

Arora1992 3-Nov-14 8:08am

I am creating a procedure how i try this Try this...
insert into Email VALUES((select id from contact WHERE Id=@id),@email)

Akashjoshi 3-Nov-14 23:26pm

cmd.Parameters.Add("@Id", SqlDbType.Int).Value = "125";
cmd.Parameters.Add("@Email", SqlDbType.VarChar).Value = "Mr";

Here you add 125 as a ID
so in insert procedure first it checks that
insert into Email VALUES((select id from contact WHERE Id=125),@email)
If this condition is true then id is inserted into your Email table..
and if condition is wrong then id does't inserted...because ID=125 is not in contact table...

Arora1992 3-Nov-14 23:31pm

So I need to Use IF else

Arora1992 3-Nov-14 23:31pm

Please tell me the Proper code how i use that

Akashjoshi 3-Nov-14 23:49pm

Use this procedure..

create procedure Email_insert_procedure(@Id int, @Email varchar(100))as
insert into Email values ((select Id from contact WHERE Id=@Id), @Email)

Arora1992 3-Nov-14 23:52pm

K I try this

Arora1992 3-Nov-14 23:51pm

If (Id==125)
{
value inserted
}
else
{
value not inserted
}

Is this correct

Arora1992 4-Nov-14 1:37am

Thank You So Much