Candidate key vs Primary Key

DMT20601 · September 12th, 2012, 10:05 AM

New to Webmatrix, still reading. I have a many-to-many relationship between to tables. The reading (everywhere) is to put in a linked table between the two to simplify the linkage.

The question. Should the linked table have a PK field and two FK fields that point to their respective one-to-many tables or should the linked table have one field that is the concatination of the two PK from the primary tables.

Table 1 Table 2
table1Id (PK) Auto Increment Table2Id (PK) Auto Increment

LinkedTable
LinkedTableId (PK) Auto Increment
FK of table 1
FK of table 2

or

LinkedTable
FK1 + FK2 (PK)

Thanks
Dallas in Maryland

Imar · September 12th, 2012, 02:47 PM

Hi there,

You typically create a table that contains two columns, one for each foreign key. That way you can easily join them on the tables they are referring to.

Cheers,

Imar

DMT20601 · December 22nd, 2012, 10:05 PM

I had ask this question back in Sept 2012. I got a response that was surely the right direction. Today, I can be more accurate about what I need to do within Webmatrix.

I have a linked table for a many-to-many relationship. The table only has two columns PKtable1id and PKtable2id.

For it to be within the structure of Webmatrix it needs a multi column primary Key to insure uniqueness.

It easy to build this little table with the IDE but how do you configure the primary key?

Is the IDE going to let me check primary key on both columns?

or can I build a query script (Create Table) and apply a constraint for the multi column pk.

Thanks

Dallas in Maryland.

Imar · December 23rd, 2012, 04:53 PM

Hi there,

When you create the junction table, set the Is Primary Key property of both columns to True. This way, you create a composite key which in turn means each combination can only exist once.

Next, for this table, click New and create a new Relationship between the Foreign Key Table (the junction table) and the first Primary Key table. In your case, link PKtable1id to the ID of Table1. Then repeat this for the second table and link PKtable2id to the primary key of Table2.

This way, the individual columns of the composite key each refer to their respective related table.

Hope this helps,

Imar