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.
| Author |
Topic |
|
yoavyoavyoav
Starting Member
1 Post |
 Posted - 2007-03-29 : 16:25:37
|
Hi , I'm creating a .net application that requires me to create connections between tables on-the-fly. so i've been using the following syntax to create a foreign key : ALTER TABLE table1ADD CONSTRAINT Fk_nameFOREIGN KEY (colName) REFERENCES table2 (colName)This has worked pretty well , but when it comes to tables that consist of 2 or more primary keys - im getting a problem. consider the following tables : students - (studentID (PK) , studentName)courses - (courseID (PK) , courseName )studentInCourses - (studentID(PK) , courseID(PK) , grade )Now I'd like to create a connection between students and studentsInCourses by connecting only the column "studentID".by using the above syntax - I cannot - it just gives me an error.So my question is - what query should i use to make a connection between 2 tables when one or both of them contain more than 1 primary key ? .Any help will be appreciated.  For any refernce , the error I get when trying to run the query in the described case is : System.Data.SqlClient.SqlException: There are no primary or candidate keys in the referenced table 'studentInCourses' that match the referencing column list in the foreign key 'Fk_xyz'. Could not create constraint |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-03-29 : 16:39:03
|
| First of all, tables can only have one primary key, studentInCourses doesn't have two primary keys, it has one compund primary key that consists of the two columns studentID and courseID. Based on the error you are getting, you are trying to create a foreign key in students that references studentInCourses, but that's the wrong way round, you should be creating a foreign key in studentInCourses that references students.Finally, why are you creating the foreign keys on the fly? You should create them once and then leave them alone. |
 |
|
|
|
|
|
|
|
Problem creating foreign key