Flagging record as primary

[tprohas]

I'm trying to create this association table that will allow a user to have many phone numbers and only one of the phone numbers can be flagged as being the primary number. To do this I created multi column primary key using (UserID, PhoneNumberID). I tried to create a unique index on (UserID, IsPrimary) to force the table to only allow one of the many phone numbers to be flagged as primary. When I create the unique index EA automatically removed the primary keys from the table. Now what should I do to make the table only allow one of the many phone numbers be flagged as primary?

IF EXISTS (SELECT * FROM SYSOBJECTS WHERE id = object_id('[UserPhoneNumbers]') AND  OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE [UserPhoneNumbers]
GO

CREATE TABLE [UserPhoneNumbers] (
   [UserID] int NOT NULL,
   [PhoneNumberID] int NOT NULL,
   [IsPrimary] bit DEFAULT (0) NOT NULL
)
GO

CREATE INDEX [CL_IX_UserPhoneNumbers_User]
ON [UserPhoneNumbers] ([UserID] ASC)
GO

ALTER TABLE [UserPhoneNumbers] ADD CONSTRAINT [PK_UserPhoneNumbers]
PRIMARY KEY ([UserID], [PhoneNumberID])
GO

ALTER TABLE [UserPhoneNumbers] ADD CONSTRAINT [FK_UserPhoneNumbers_PhoneNumbers]
FOREIGN KEY ([PhoneNumberID]) REFERENCES [PhoneNumbers] ([PhoneNumberID])
GO

ALTER TABLE [UserPhoneNumbers] ADD CONSTRAINT [FK_UserPhoneNumbers_Users]
FOREIGN KEY ([UserID]) REFERENCES [Users] ([UserID])
ON DELETE CASCADE ON UPDATE CASCADE
GO

thanks,

Aaron

[Bruno.Cossi]

Hi Aaron,

the DB modeling features in EA leave a bit to be desired. Either way, I do not think that you want to create a Unique Index on UserID and IsPrimary - that would result in one Primary number per user, but also only in one non-primary number :-)
A simple Unique Index won't do. If you insist in enforcing this constraint on the DB level, you may need to write a trigger. Otherwise, a better solution would be to have a column PrimaryPhoneNumberID in the table Users, referring to the User's primary phone number.

Hope this helps,
Bruno

[tprohas]

Ah, ok. This is going to make things interesting as I use this design for primary entities a lot in our system. I'm going to have to rethink how I do this.

thanks,

Aaron

Hi Aaron,

the DB modeling features in EA leave a bit to be desired. Either way, I do not think that you want to create a Unique Index on UserID and IsPrimary - that would result in one Primary number per user, but also only in one non-primary number :-)
A simple Unique Index won't do. If you insist in enforcing this constraint on the DB level, you may need to write a trigger. Otherwise, a better solution would be to have a column PrimaryPhoneNumberID in the table Users, referring to the User's primary phone number.

Hope this helps,
Bruno

[Bruno.Cossi]

Aaron,

your design is not wrong, I use it frequently myself, actually even in a scenario precisely like yours (multiple addresses, one of them is the mailing one).
Just think about whether you really need to have this enforced at the DB level or if the application level is enough.

Bruno