SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 How to insert value to 2 foreign key in single row
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Arunavally
Yak Posting Veteran

India
58 Posts

Posted - 10/17/2013 :  06:18:34  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 10/17/2013 :  07:46:47  Show Profile  Reply with Quote
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
Go to Top of Page

Arunavally
Yak Posting Veteran

India
58 Posts

Posted - 10/17/2013 :  09:36:10  Show Profile  Reply with Quote
Thank you. I will try it
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/17/2013 :  10:05:01  Show Profile  Reply with Quote
cool
lets us know how you got on

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Arunavally
Yak Posting Veteran

India
58 Posts

Posted - 10/17/2013 :  11:11:50  Show Profile  Reply with Quote
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


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/18/2013 :  02:16:49  Show Profile  Reply with Quote
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
Go to Top of Page

Arunavally
Yak Posting Veteran

India
58 Posts

Posted - 10/18/2013 :  03:53:52  Show Profile  Reply with Quote
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


Go to Top of Page

Arunavally
Yak Posting Veteran

India
58 Posts

Posted - 10/18/2013 :  04:10:02  Show Profile  Reply with Quote
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


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/18/2013 :  08:13:56  Show Profile  Reply with Quote
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
Go to Top of Page

Arunavally
Yak Posting Veteran

India
58 Posts

Posted - 10/19/2013 :  06:13:12  Show Profile  Reply with Quote
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


Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000