what happens to foreign key

Question

hii...

we use foreign keys.. its fine.. Now i am ready with a table having some foreign key references. I connected my C# code to the databsae and update the table having some foreign key references. My c# code have values to insert in the table. Now please tell me the use of foreign key reference her in inserting the values in the table.

Answer 1

Hi,

   The use of Foreign key is to maintain data integrity.  say, you have two tables Table A (sid int,sname varchar(20) sid is the pk of the table .Table B (sno int,sid int,smark1 int,smark2 int) sno is the pk of tableb and sid is FK referencing to tableA.

So, if you happen to update or insert any value in sid  column of tableB that does not exist in tableA, it will not let you do that.

Also, you cannot delete the sid value in the table A that is existing in tableB.However there some options you can specify which will let you change/delete sid in table A like cascading, default, no action, null.

Hope it helps.

---

Please mark as 'Answer', if the solution solves your problem.

Answer 2

Thanks for ur valuable time... I want to know how to insert the values in the table having foreign keys? exactly if I happen to update or insert any value in sid  column of tableB that does not exist in tableA, it will not let me do that. From front end user will enter the values and submit the data.. how would I use foreign key to insert the data into the table submitted by user..

Answer 3

you should be able to handle the error using try catch and display the message to user or use  drop down values where they can select the values.

---

Please mark as 'Answer', if the solution solves your problem.

Answer 4

You don't use a Foreign Key to insert data into a table as Foreign Keys are used purely to enforce referential integrity between two Tables (As stated by Stan210).

If you want to insert a record into TableB that does not have a corresponding record in TableA, you will need to create a record in TableA first and then create your record in TableB.

---

Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA, MCSD, MCAD

Answer 5

As u said we dont use foreign keys to insert data into table.. its fine... now if i insert the values in the columns having foreign key references then it will aquire some space in the table in which we are inserting.. right ? Foreign key have a reference to the primary key.. But according to insertion what i made just above foreign key column would not have reference to primary key.. rather it would store the value which is same as primary key value..

Also, Please tell,, Does any column having foreign key reference aquire as much space as primary key column for a single tuple ?

Answer 6

But according to insertion what i made just above foreign key column would not have reference to primary key.. rather it would store the value which is same as primary key value..

Also, Please tell,, Does any column having foreign key reference aquire as much space as primary key column for a single tuple ?

That's not correct. 

You cannot insert a record ino a child table (TableB) that has a relationship with a parent table (TableA) unless you have the key value from the parent table (TableA).  If you try to insert a record into TableB without a valid key from TableA, the insert will fail.  I suggest you have a read of http://msdn.microsoft.com/en-us/library/ms175464.aspx.

It depends on the siez of the key.

 

---

Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA, MCSD, MCAD

Answer 7

The data in the foreign key column will occupy as much space as the primary key it  references to. 

The Foreign key is a constraint which checks whether the data you were entering in that column already exists in other table.

if  it exists, it will add that record just like any other record. if it does not , it will throw you an error.

 So, the data space occupied by the fk column will be same  as primary key column.

Assume a simple web application accessing two tables student ( sno int,sname nvarchar(20) not null) sno is the PK and Marks(sid int identity(1,1),sno int not null,smark1 int,smart2 int) sid is the PK and sno is the FK refercning sno in student table

If the front end user, tires to add a record to the Marks table, if the sno is not already existing in student table, it will throw you an error. 

you can catch this error and display it front end user something like 'The student record does not exists'. for this you can use TRY ..CAtch block.

hope it helps.

---

Please mark as 'Answer', if the solution solves your problem.