Problem when creating FK

Hello everyone,

I'm created a new SQL Server 2005 database for a new project and I'm facing a little situation regarding FKs. I guess that's what you get when you move from MySql 4 to Sql Server 2005 ;)

Here's the situation: I have a table called contact, assigned to the schema person, and that table handles all the base information regarding users. Informations such as FirstName, LastName, etc, are stored in the [Person].[Contact]. So far so good.

As a design decision I've decided to keep track of the nick names (Alias) that a given member had over-time. In order to do this I've designed the [Person].[GlobalAlias] table like this:

Id uniqueidentifier [NOT NULL] [PK]
ContactId uniqueidentifier [NOT NULL] [FK Person.Contact Id]
Alias nvarchar(30) [NOT NULL]
AddedByContactId uniqueidentifier [NOT NULL] [FK Person.Contact Id]
AddedDate datetime [NOT NULL]
AddedObs nvarchar(MAX) [NULL]
RemovedByContactId uniqueidentifier [NULL] [FK Person.Contact Id]
RemovedDate datetime [NULL]
RemovedObs nvarchar(MAX) [NULL]
ModifiedDate datetime [NOT NULL]
Status bit [NOT NULL]

This table seems to work great dispite the fact that it generates an error when I try to set the FKs. For the column [Person].[GlobalAlias] ContactId I set a FK to the column [Person].[Contact] Id with cascade on delete and on update. Now, when I go to the [Person].[GlobalAlias] AddedByMembershipId and try to add a FK to the column [Person].[Contact] Id it works as long as I define NO ACTION on delete and on update. Obviously, this could originate rows where the [Person].[Contact] that added a [Person].[GlobalAlias] was deleted and there's still a reference to that ID in the [Person].[GlobalAlias] AddedByMembershipId

Here's the error that I'm getting from the database:

'Contact (Person)' table saved successfully
'GlobalAlias (Person)' table
- Unable to create relationship 'FK_GlobalAlias_AddedByContactId_Contact_Id'.
Introducing FOREIGN KEY constraint 'FK_GlobalAlias_AddedByContactId_Contact_Id' on table 'GlobalAlias' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Could not create constraint. See previous errors.

How could I solve this? Perhaps the only way is to normalize it even further and create a history table?

Best regards,
DBA

That is pretty much what you appear to have now. Just that you are trying to set up foreign key relationships into it, which you probably shouldn't.

With a cascading delete, if you deleted someone, the delete would want to then cascade to every record in your GlobalAlias table that referenced them (Every alias they ever had, and any alias's they had ever created for someone else, or removed from someone else). That probably isn't what you wanted. I could see possibly wanting the updates to cascade, but you normally don't want users to be able to change their uid, so the FK really isn't all that useful for cascading updates.