Composite Key for Gizmo Tracking

Question

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.

Answer 1

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
ADD CONSTRAINT PK_Relation PRIMARY KEY CLUSTERED(OwnerId , gizmosId);