Design and index question

Question

Post reply

Design and index question

BuilderBits

SSCertifiable

Points: 6071
More actions
March 19, 2006 at 6:23 pm

#168783

Hi All..

I am really hoping that someone can help explain this to me and i also hope that i can even ask the question correctly..

I have been working with asp.net and sql2000 and now sql2005 for 15 months and even though i can work with sql2005 and get around very well i have bumped into an area that i can not run from any longer because i need to work with this area now..

so here goes..

First of all i have posted this link that displays a portion of my database here ---> http://constructionsupercenter.com/Help/sql-tabelHelp.gif

When creating a table and creating a primary key on a table i understand that this column is now unique and is set to primary.. Now the tables that are displayed in the link if you notice ( on tables orderitemopt, orderitem, productopt, productoptch )there are many primary keys... I am confused because i thought that a table could only have one primary key..

Another scenario....

If i create TWO TABLES and one has a primary key of ProductID and one has a column with FK_ProductID ...When i create the fk from in the designer from primary key table to the FK table I drag the key from PK_productID table to FK_ProductID table... NOW when working with a table like ProductOptCh i have to drag the Key in the Opposite direction in order for the dialog box to appear with the Primary table and the FK_table to appear iin the desired labels at top of the dialog...

can someone please help me with this.. If i did not ask the question correctly please ask me what in the HELL that i am talking about..

erik

Dam again!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Joseph Mulhall SSCarpal Tunnel Points: 4672 More actions · Answer 1

Joseph Mulhall

SSCarpal Tunnel

Points: 4672

March 20, 2006 at 5:30 am

#627560

Sure - your confusion lies in that this diagram shows the foreign key relationships, they're not all primary keys.

BuilderBits SSCertifiable Points: 6071 More actions · Answer 2

BuilderBits

SSCertifiable

Points: 6071

March 20, 2006 at 7:53 am

#627597

Anyone else care to give it a shot?

Don't make me beg!

erik

Dam again!

Joseph Mulhall SSCarpal Tunnel Points: 4672 More actions · Answer 3

Joseph Mulhall

SSCarpal Tunnel

Points: 4672

March 20, 2006 at 8:20 am

#627631

You think I've said something incorrect?

Those key icons indicate a key constraint - not just primary keys.

A foreign key constraint is created on the table the contraint is applied to, not the table it is referring to.

Regan Galbraith SSCertifiable Points: 5227 More actions · Answer 4

Let me take a shot at some of this for you. Please forgive me if the explanation is convoluted/unclear - I haven't tried to explain this before

BTW - could I recommend when you have some time, you follow up on some of the relational database design principles? That will give you a better grounding that the explanation I'm about to give, and may help you spot/fix any mistakes I make here

A table (an entity) may in fact have several seperate "unique" keys. These are referred to as candidate keys. A point that I think may be new to you is that not all candidate keys are necessarily 1 column keys. Typcially, multi-column keys are used to resolve many-to-many relationship entities - indeed that is the case in your model - the OrderItem table is good example. That table resolves the relationship Company Product Order (some naming standards would have this required it to be named along the lines of CompanyProductOrder). The primary key on that table (currently) is a combination of OrderId, CompanyId, ProductID. The only column (attribute) that is actually from that entity is Qty - the rest describes that relationship.

As an example of something that has several candidate keyd, take a human being. If you had a complete description of a specific human being, that might include: Full Names, Gender, Age, Birthdate, Maritial status, National Identification Number, DNA string, fingerprint, retinal scan etc. etc. etc. (Granted this is a bizarre example, but work with me.

Now, one might be able to say that the DNA string will definitely be unique. Again, we could say that the fingerprint is unqiue. Similarly, perhaps the retinal scan is unique. One might argue that the national identification number wuold be unique (this is (imho) a bad idea - I say that having been brought to my current company to resolve, amongst other things, the fact that they assummed identification numbers were unqiue, and number have multiple of people, since the government change the identification number system - but I digress)). Again, you might say the a person's Full Name, Birthdate, Gender and National Identification - when combined - are guaranteed to be unique. That gives us 4 candidate keys (I've excluded that National Identification Number). You may choose to enforce unqiueness on all of these keys by defining unique indexes on the columns (create unique index PersonDNA on Person (DNA)) for example, but you can only have 1 primary key.

Also, the candidate key should only have what is required to make it unqiue. So for example, you wouldn't call the combination of DBA string, fingerprint a unique key - because each of them are (by definition above) unqiue. Yes, the combination is also unqiue, but it is unnecessary.

Well, if that hasn't confused you, I don't know what will

Some honework for you (to help straighten out the mess above):

Candidate Key:

http://en.wikipedia.org/wiki/Candidate_key

Relation Databases:

http://en.wikipedia.org/wiki/Relational_database

btw - all the above is intended as an honest attempt to help. I apoligize if anything came across as supercillious or condescending, or simply incorrect.

HTH

--added to show example:

below is some code. If you take diagrams at the indicated points, in a test database, you will see what i mean:

create table MultipleColumnKey (

KeyColumn1 int not null,

KeyColumn2 int not null,

KeyColumn3 int not null,

ForeignKey int null,

DataColumn char(1))

--run above, then create diagram 1 via enterprise Manager

alter table MultipleColumnKey add constraint PrimaryKey Primary Key (KeyColumn1, KeyColumn2, KeyColumn3)

--run above, then create diagram 2 via enterprise Manager

Create table MultipleColumnKeyForeignKey (

UnqiueId int not null primary key)

alter table MultipleColumnKey add constraint ForeignKey Foreign Key (ForeignKey)

references MultipleColumnKeyForeignKey (UnqiueId)

--run above, then create diagram 3 via enterprise Manager

If you do that, you should see that the diagram 1 has not "key" icons, diagram2 has 3 "key" ocins next to columns KeyColumn1, KeyColumn2, KeyColumn3. Diagram 3 shows the Key on MultipleColumnKeyForeignKey BUT THERE IS NOT KEY NEXT TO "ForeignKey" column. Diagram does not display "key" icons next to foreign keys, only next to primary keys.

HTH

-- added (this is beginning to be a bahit :sick

I notice that this thread is under SQL2005. My "diagramming" was done on SQL 2000. The comments *should* hold true for SQL2005.

Last piece of advice (for now). Explicitly create your foreign keys using SQL (DDL) statements. Avoid the GUI to (a) avoid problems draggin in the worng direction (b) have complete control over names, what it is you are trying to do (c) learn the underlying syntax that the gui is using.

HTH