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 |
|
MikeB
Constraint Violating Yak Guru
387 Posts |
 Posted - 2002-07-24 : 14:09:00
|
I can really use some help in my table design and was wondering if there is someone that can help me!First: In alot of my tables I have the same fields ProjectNumber and MarkNumber. These together sortof make a primary key. Should I create a single field Identification combining the ProjectNumber with the mark number for indexing and primary key use?example: Table 1:ProjectNumber | MarkNumber | Identification--------------|------------|----------------10001 | 401 | 10001401Table 2:Identification| TypeName--------------|---------10001401 | Column Or should I just leave it the way I designed it the first time?Actually I have more questions but, let's see about this one first!Any thoughts?Mike B |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-07-24 : 14:29:14
|
Well, many 'round here, firmly believe in using a Natural Key rather than a Surrogate Key such as int with the IDENTITY property.However, the rule begins to break down once you start talking about composite keys. Reason? SQL Server won't let you create a Foreign Key constraint that references a composite key.You can't do...alter table [table 2] add constraint fk foreign key (projectnumber,marknumber) references [table 1](projectnumber,marknumber) So in this case, if you would like to leverage the built in referential integrity tools provided by SQL Server, you'll have to derive you Identification column and use that as the Primary Key to reference (like you have above).This doesn't affect your indexing stragety. You can create the primary key index as non-clustered and cluster the (projectnumber,marknumber) columns. Whatever supports your queries better...[edit]oops, didn't realize this was an access question . . . little or none of my comments apply[/edit]<O>Edited by - Page47 on 07/24/2002 14:33:11 |
 |
|
|
MikeB
Constraint Violating Yak Guru
387 Posts |
Posted - 2002-07-24 : 14:55:31
|
I am a developer of c++ applications, I am just getting into database supported applications. I am currently working with Access, c++, ADO but will be adopting SQL Server sooner or later into my application. So your comments in a way do apply. Thank you for your responce. Couple questions about your responce:quote:
'round here, firmly believe in using a Natural Key rather than a Surrogate Key
What is a natural key?A surrogate key I guess would be the result of combining the two "10001" and "401" to make a surrogate key "10001401" so I take it that the natural key, would be the project number and the mark number the way they are shown in Table 1?quote:
break down once you start talking about composite keys.
What is a composite key?Mike B |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-07-24 : 15:13:02
|
| read meBy natural key, I mean the columns that naturally make a row unique in a table. Social Security Number, EmployeeNumber and ProductName are (probably) examples of natural keys. Sometimes even (FirstName,LastName) is a natural key....By surrogate key, I mean a column that is derived in order to create a column that uniquely identifies a row. Most DBMSs have an autonumber thingy that generates a sequential, guarenteed unique value. This IDENTITY (in SQL Server's case) would be a surrogate key. Your Identification column is a surrogate key; it is contrived, derived, and has no real meaning.A composite key is simple a key that is comprised of multiple columns. That is to say, either column alone does not uniquely identify the row, but the two(or three or ten) do....<O> |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2002-07-24 : 15:18:10
|
quote:
However, the rule begins to break down once you start talking about composite keys. Reason? SQL Server won't let you create a Foreign Key constraint that references a composite key.You can't do...alter table [table 2] add constraint fk foreign key (projectnumber,marknumber) references [table 1](projectnumber,marknumber)
Eh? |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-07-24 : 15:37:27
|
oh my gawd.That is so wrong.   You can reference all of composite key in a foreign key constraint.What I meant is if you have a composite, you can't reference part of it, because the target of a fk reference must be guarrenteed unique. With a composite, the uniqueness is by virtue of the whole thing.I'm shutting up now...<O> |
|
|
|
MikeB
Constraint Violating Yak Guru
387 Posts |
Posted - 2002-07-24 : 15:47:11
|
Thank you for the link to the article. That helped clear a few thingsup. (Had to remove a bunch of ListID AutoNumber fields) hehehe. Anyway, here is a scenerio. I have a shipping table that provides information about loads: Date | ProNumber | Load | Carrier | Trailer |-----------|-----------|------|---------|---------|07/21/2002 | 10001 | 1 | WhoKnows| Trlr100 Now in the table above, the primary key is the ShippingDate,ProjectNumber, and LoadNumber together correct because you cannothave 2 load Number 1 for project 10001 on 07/21/2002.Here is a Load table that show what is on each load Date | ProNumber | Load | Mark | Stack | Level | Position-----------|-----------|------|------|-------|-------|----------07/21/2002 | 10001 ! 1 | 401 | 1 | 1 | 107/21/2002 | 10001 ! 1 | 402 | 1 | 1 | 307/21/2002 | 10001 ! 1 | 403 | 1 | 2 | 2 Now in this situation, all the fields together make the row unique.Should all the fields be selected to create a primary key, so thatduplicates cannot happen?Mike BAdded:Here is the problem with the above. The Date, ProNumber, LoadNumber, MarkNumber fields are all filled in at one point in time and the stack, level, and position are entered at another. This means that it cannot be designated as the primary key. Any ideas, points or thoughts?Edited by - MikeB on 07/24/2002 15:51:26Edited by - MikeB on 07/24/2002 16:18:25 |
|
|
|
|
|
|
|
|
Table design