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.

1 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
  • I ran a sql query running this and it noted it ran successfully. is there a way to view my constraints on a column as it isn't showing up on in sql server management studio under the column I applied it to.
    – dbollig
    Dec 3, 2014 at 14:51
  • @dbollig try this EXEC sp_helpconstraint 'dbo.tableName', I have modified the answer and changed the contraints to Primary Key from Unique.
    – vijayp
    Dec 4, 2014 at 7:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.