locked
Junction Table RRS feed

  • Question

  • hi .

    i have two tables and i want to have a juncation table among them .

    Table A { idA(pk) , name,...}

    Table B{idB(pk) , name ,...}

    But for the junction table i don't know whether to determine a seprated Pk or to join primary ked of the above tables ? and if the second one is true , how can i ralete (join) the juction table with two other tables?

    Table C{idC(pk) , idA ,idB ,...}

    or

    Table C {id A(pk) , idB(pk) , ...}

    Saturday, May 19, 2012 5:22 AM

Answers

  • Morning,

    In most part of the cases it's better to use "by the book" approach without additional ID.

    create table TableC ( idA int not null, idB int not null, constraint PK_TableC primary key clustered(IdA, idB), constraint FK_TableC_TableA foreign key(IdA) references dbo.TableA(IdA), constraint FK_TableC_TableB foreign key(IdB) references dbo.TableA(IdB) ) go create unique nonclustered index IDX_TableC_IdB_Ida on dbo.TableC(IdB, idA) go

    select
    from
        dbo.TableA ta join dbo.TableC tc on
            ta.idA = tc.idA
        join dbo.TableB tb on
            tc.idB = tb.idB

    Don't forget to create another non-clustered index on idB as you can see above.

    Another approach (separate PK on idC) introduces an overhead (extra index to maintain, bigger row size). There are some very special cases when you can choose that approach. For example if client application uses some frameworks that do not support composite PK and/or if there is some kind of manual replication that relies on the single column key, but generally speaking it's better to avoid an overhead that introduces


    Thank you!

    My blog: http://aboutsqlserver.com



    Saturday, May 19, 2012 2:27 PM
  • You can look into Northwind  sample database at Order Details table... which has its own PK and referencing to Orders (OrderID) and Products(ProductID) tables

    Having said that you can create a table C with id column pk and havev idA and idB referencing to the tables A and  B


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    • Marked as answer by great ehsan Sunday, May 20, 2012 7:08 AM
    Sunday, May 20, 2012 3:18 AM

All replies

  • Morning,

    In most part of the cases it's better to use "by the book" approach without additional ID.

    create table TableC ( idA int not null, idB int not null, constraint PK_TableC primary key clustered(IdA, idB), constraint FK_TableC_TableA foreign key(IdA) references dbo.TableA(IdA), constraint FK_TableC_TableB foreign key(IdB) references dbo.TableA(IdB) ) go create unique nonclustered index IDX_TableC_IdB_Ida on dbo.TableC(IdB, idA) go

    select
    from
        dbo.TableA ta join dbo.TableC tc on
            ta.idA = tc.idA
        join dbo.TableB tb on
            tc.idB = tb.idB

    Don't forget to create another non-clustered index on idB as you can see above.

    Another approach (separate PK on idC) introduces an overhead (extra index to maintain, bigger row size). There are some very special cases when you can choose that approach. For example if client application uses some frameworks that do not support composite PK and/or if there is some kind of manual replication that relies on the single column key, but generally speaking it's better to avoid an overhead that introduces


    Thank you!

    My blog: http://aboutsqlserver.com



    Saturday, May 19, 2012 2:27 PM
  • You can look into Northwind  sample database at Order Details table... which has its own PK and referencing to Orders (OrderID) and Products(ProductID) tables

    Having said that you can create a table C with id column pk and havev idA and idB referencing to the tables A and  B


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    • Marked as answer by great ehsan Sunday, May 20, 2012 7:08 AM
    Sunday, May 20, 2012 3:18 AM