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 |
|
arusu
Yak Posting Veteran
60 Posts |
 Posted - 2010-09-16 : 10:45:38
|
| I have a test table and a result table.... each test can only have one result, how do i link these 2 table? I cant have a foreign key of result_id in the test table because there might not have been a test performed yet |
|
|
rohitvishwakarma
Posting Yak Master
232 Posts |
Posted - 2010-09-16 : 10:48:16
|
| Can you provide the columns of both the tables?You can always use OUTER JOIN between the tables to get the list of all Tests and having NULLS for the results so, use test_id(column of test table) as foreign key in results table |
 |
|
|
arusu
Yak Posting Veteran
60 Posts |
Posted - 2010-09-16 : 10:50:35
|
| but i can't just have the result table not linked to anything in my database... I read something about a composite key, not sure if im in the right direction |
|
|
|
rohitvishwakarma
Posting Yak Master
232 Posts |
Posted - 2010-09-16 : 10:55:26
|
quote:
Originally posted by arusu
but i can't just have the result table not linked to anything in my database... I read something about a composite key, not sure if im in the right direction
I assume you are having a column like test_id in the Test table and for linking with the Result table you can use the same column as foreign key in Result table(furthermore it will ensure that you won't have results of a non-existent Test). |
|
|
|
arusu
Yak Posting Veteran
60 Posts |
Posted - 2010-09-16 : 10:58:47
|
| yes but that will allow the same test to be added twice in a result table... there will be no constraint |
|
|
|
rohitvishwakarma
Posting Yak Master
232 Posts |
Posted - 2010-09-16 : 11:20:39
|
| UNIQUE and NOT NULL Constraints on the Foreign KeyWhen both UNIQUE and NOT NULL constraints are defined on the foreign key, only one row in the child table can reference a given parent key value, and because NULL values are not allowed in the foreign key, each row in the child table must explicitly reference a value in the parent key.This model establishes a one-to-one relationship between the parent and foreign keys that does not allow undetermined values (nulls) in the foreign key. |
|
|
|
rohitvishwakarma
Posting Yak Master
232 Posts |
Posted - 2010-09-16 : 11:44:08
|
| For one to one relationships, just mark the FK as unique |
|
|
|
|
|
|
table joins