CODEWITHSUNDEEP
sqlsql-serverdatabase-designrelational-databaseconstraints
FluffyBeing
FluffyBeing

Reputation: 468

SQL Server Management Studio - How to model this database constraint in a Database Diagram

I have a table named Communications which contain a user's contact numbers. This can either be a Home, Mobile or Fax number. I'm storing them all in one table and identifying them using a Type column. (0 = Home, 1 = Mobile, 2 = Fax). Communications table has a foreign key UserId which maps to my Users table to show User to Number relation. I want to have a constraint so that each user can only have at most one of each Type of number. What would be the best way to model this?

I'm using Database Diagram in SQL Server Manager Studio 2008 and would like a GUI-ish answer as opposed to SQL query if possible.

Upvotes: 0

Views: 519

Answers (2)

Damir Sudarevic
Damir Sudarevic

Reputation: 22187

Simply use composite key {UserID, NumberType}

enter image description here

It also a good idea to keep {UserID, PhoneNo} unique and to make sure that a phone number is of one type only; while allowing for more than one user to have the same phone number, say home phone for a family. In that case you can try something like this

enter image description here

Note: implement AK (alternate key) as a unique index.

Upvotes: 1

Daniel B
Daniel B

Reputation: 807

You can add a NumberType table, containing the values 0,1,2 as primary key in column NumberType, and add a foreign key from table Communications.NumberType to NumberType.NumberType. This way table Comunications will only be able to contain values 0,1,2 (or whatever NumberTypes you define).

Then you add a primary key (or unique index, if you want some other primary key) to table Communications on columns NumberType and UserId. This makes sure that each user may only have one number of each kind.

Upvotes: 1

Related Questions