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

one table for Owner, Identity Auto-Increment by 1 will give OwnerId
one table for gizmos, Identity Auto-Increment by 1 will give gizmosId
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);

Related Solutions

Sql-server – Composite Primary Key efficiency as a Foreign Key

The cost of using a simple synthetic integer PK is small, and the benefit in your case would probably be quite considerable.

As you point out, you'll have a much simpler FK relationship.
A small PK makes for small (and fast) indices. Your total table space will probably be made less by adding such a column.
If business rules ever change, you won't have to reorder the table.

The only material downside that comes to mind is that you may lose performance on queries which benefited from clustering on the composite PK. If you think that's likely to be significant, than continue clustering on the composite candidate key, but put the PK on the synthetic key.

SQL Server – Delete Using a Composite Key

Should be something like this:

DELETE A
FROM 
    table_example1 AS A
    INNER JOIN table_example2 AS B
    ON A.COLUMN1 =B.COLUMN1
    AND A.COLUMN2 = B.COLUMN2
WHERE
    COLUMN_DATETIME > @Period;

Alternatively:

DELETE FROM A
FROM dbo.table_example1 AS A
WHERE EXISTS
(
    SELECT *
    FROM dbo.table_example2 AS B
    WHERE
        B.COLUMN1 = A.COLUMN1
        AND B.COLUMN2 = A.COLUMN2
        AND B.COLUMN_DATETIME > @Period
);

Best Answer

Related Solutions

Sql-server – Composite Primary Key efficiency as a Foreign Key

SQL Server – Delete Using a Composite Key

Related Question