How to insert value to 2 foreign key in single row

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

How to insert value to 2 foreign key in single row

Author

Topic

Arunavally
Yak Posting Veteran

58 Posts

Posted - 2013-10-17 : 06:18:34

I created 3 tables.
1) Student_Registration
Stu_id varchar not null primary key

2) Staff_Registration
Stf_id varchar not null primary key.

3) Book_issue
stf/Stu_id varchar.
This filed i created two foreign key of student registration and Staff Registration table.

when i insert the value,i got the error msg

"The INSERT Statement conflicted with the FOREIGN KEY constraint "fk_bookisssue_staff". The conflict occured in database "sample", tble dbo.staff_reg", column "StfId". The Statement has been terminated"

How can i resolve it?

Thanks in Advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-17 : 07:46:47

you cant do like that.
you need to create separate fields for that
stf_id pointing to Staff_Registration (Stf_id) by means of fk
Stu_id pointing to Student_Registration(Stu_id) by means of fk

then add a CHECK CONSTRAINT on Book_issue such that (stf_id IS NOT NULL OR Stu_id IS NOT NULL)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Arunavally
Yak Posting Veteran

58 Posts

Posted - 2013-10-17 : 09:36:10

Thank you. I will try it

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-17 : 10:05:01

cool
lets us know how you got on

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Arunavally
Yak Posting Veteran

58 Posts

Posted - 2013-10-17 : 11:11:50

sorry sir. I dont knw to create chk constraint. i saw sql tutorial video. then i create what u told. But its display error.
How i resolve it sir?

quote:
Originally posted by visakh16

cool
lets us know how you got on

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-18 : 02:16:49

the syntax will be like


ALTER TABLE Book_issue ADD CONSTRAINT Chk_HasADependentID CHECK (stf_id IS NOT NULL OR Stu_id IS NOT NULL)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Arunavally
Yak Posting Veteran

58 Posts

Posted - 2013-10-18 : 03:53:52

Thank you sir. It will work correctly.

quote:
Originally posted by visakh16

the syntax will be like

ALTER TABLE Book_issue ADD CONSTRAINT Chk_HasADependentID CHECK (stf_id IS NOT NULL OR Stu_id IS NOT NULL)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Arunavally
Yak Posting Veteran

58 Posts

Posted - 2013-10-18 : 04:10:02

Hai visakh murukes sir,

I have another one doubt. That is,

Student Table
StuId Stuname Fathername DOB Age Qual Addr Creationdate updationdate
(Primarykey)
MSBSTU01 xxxx xxxxxxx dd 23 MCA xxx dd dd

Staff Table
StfId Stfame Fathername DOB Age Qual Addr Creationdate updationdate
(Primarykey)
MSBSF01 xxxx xxxxxxx dd 23 MCA xxx dd dd

Book_issue Table

Stu_stf_id BkId Bkname Person issuedate rtdate CMBook Magazine Amount
MSBSTU01 01 xxxx Student dd dd 1234 0 0
MSBSF01 14 yyyy Staff dd dd 0 0 120

I didnt set any foreign key for Book_issue table If i follow this option means, In future can i retrieve, who got this book or not/ who paid how much and return date of ths book. through "Person" field without foreign key.
It is possible or not? It is good way or not?
kindly tel clearly.

Otherwise i wl select What you told like stu_id and stf_id are individual field and connect foreign key....

If i choose this way, can i get last name, CMBook, Magazine from Student or Staff and Book_issue table.

Kindly help me...and resolve it

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-18 : 08:13:56

foreign key is to enforce that value in field corresponds to valid value in parent table. If you've set it you can be sure that you'll have only valid entries
Even without FK you'll be able to pull related values so far as ID values populated corresponds to ones present in other tables.
Using two keys will also be fine. But in that case query would involve an additional LEFT JOIN or UNION ALL statement
In both cases you'll be able to extract last name, CMBook, Magazine from Student or Staff and Book_issue table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Arunavally
Yak Posting Veteran

58 Posts

Posted - 2013-10-19 : 06:13:12

Thank you so much sir.

quote:
Originally posted by visakh16

foreign key is to enforce that value in field corresponds to valid value in parent table. If you've set it you can be sure that you'll have only valid entries
Even without FK you'll be able to pull related values so far as ID values populated corresponds to ones present in other tables.
Using two keys will also be fine. But in that case query would involve an additional LEFT JOIN or UNION ALL statement
In both cases you'll be able to extract last name, CMBook, Magazine from Student or Staff and Book_issue table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources