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 |
|
kevincallan
Starting Member
13 Posts |
 Posted - 2008-03-26 : 17:47:44
|
| NOTE:I am not interested in any responses that want to argue the use of a unique ID field and autonumbering as the PK. It is quite clear from the forums that this subject is a polarizing one. This question is for those who follow text-book design practices and believe that a composite primary key should be used when it is available. I want to be one of them for the time being.SYNOPSIS:I have three tables, TestSummary, TestDetails, and Steps.The TestSummary table looks like this:Create table TestSummary( TestSummaryID int identity primary key, ... SequenceID int not null)It contains the date and time of the test, the serial number, the part number, the test operator's name, and the ID of the sequence of steps used during the test. It uses a unique ID field for the primary key.The TestDetails table looks like this:Create table TestDetails ( TestDetailsID int identity primary key, TestSummaryID int not null, StepID int not null, ...)It contains the details of the test like voltage readings, current readings, temperature, etc., one record per reading. It also contains the step number of the test sequence specified in the TestSummary table.The Steps table looks like this:Create table Steps( SequenceID int not null, StepID int not null, Function int not null Primary key (SequenceID, StepID))It contains a list of all of the functions to be performed on the device under test by sequence number and the step number within the sequence.When I try to establish a relation between TestSummary.SequenceID and Steps.SequenceID, SQL Server flags an error because TestSummary.SequenceID and Steps.SequenceID:Steps.StepID do not match.What is the problem with this approach? |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2008-03-26 : 18:48:35
|
| I believe that is a cardinality issue. You are trying to model a Many-to-One instead of a one-to-many.Maybe this will help:http://www.sqlteam.com/article/database-design-and-modeling-fundamentalsEDIT: I should say, by trying to create that particular foriegn key you are trying to model a many-to-one. I realize that is not your intent though. |
 |
|
|
|
|
|
|
|
splitting a composite primary key in a table