Sql-server – Composite Key for Gizmo Tracking

database-designidentitysql server

I'm trying to setup a many to many relationship between an gizmo table and a owner table.

  • A owner can have many gizmos
  • And a gizmo can have many owners

When I have a linking table do I set the gizmoID and ownerID as a composite key and then set the foreign keys on the gizmo table to Identity Auto-Increment by 1 and the same on the owner table or do I set composite key to identity auto-increment.

I am working on sql server 2014 Express and fairly new to this and trying to wrap my head around this.

Thanks for the help in advance.

Best Answer

as it will have three table

  1. one table for Owner, Identity Auto-Increment by 1 will give OwnerId
  2. one table for gizmos, Identity Auto-Increment by 1 will give gizmosId
  3. One table for mapping the many to many relationship as

    OwnerId - FK gizmosId - FK and Other columns

Now as the combination of OwnerId and gizmosId will make a row unique you can create primary key constraints that will enforce Uniqueness as well.

ALTER TABLE schemaName.TableName