CODEWITHSUNDEEP
sqlsql-server
Jack
Jack

Reputation: 384

Constrain a table such that each account can have one of another table

I have a table which has these columns:

I need to constrain this table such that for each Id, there can be only one of each TypeCreatorId. I hope that makes sense!

Upvotes: 0

Views: 46

Answers (2)

Barranka
Barranka

Reputation: 21047

Simply create a unique index on OwnerId and TypeCreatorId.

An example using MySQL (sorry, I don't use SQL Server):

alter table yourTable
    add unique index idx_newIndex(OwnerId, TypeCreatorId);

Example. I'll just put here what would happen with this new unique index:

OwnerId | TypeCreatorId
--------+--------------
   1    |    1
   1    |    2          -- This is Ok
   2    |    1          -- Ok too
   2    |    2          -- Ok again
   1    |    2          -- THIS WON'T BE ALLOWED because it would be a duplicate

Upvotes: 1

marc_s
marc_s

Reputation: 754518

For SQL Server, you have two options:

  1. create a UNIQUE CONSTRAINT 

    ALTER TABLE dbo.YourTable
ADD CONSTRAINT UNIQ_Id_TypeCreator UNIQUE(Id, TypeCreatorId)

  2. create a UNIQUE INDEX:

    CREATE UNIQUE INDEX UIX_YourTable_ID_TypeCreator
ON dbo.YourTable(Id, TypeCreatorId)

Basically, both things achieve the same thing - you cannot have two rows with the same (Id, TypeCreatorId) values.

Upvotes: 2

Related Questions