 -----  Create new records in PersonAttributes to match records in PersonProperties  -----
 -----------------------------------------------------------------------------------------
 
-ALTER TABLE PersonProperties ADD PersonAttributesId_Tmp UNIQUEIDENTIFIER NOT NULL 
-CONSTRAINT DF_PersonAttributesId_Tmp DEFAULT(NewID())
+
+IF NOT EXISTS (SELECT * FROM sys.columns c WHERE name = 'PersonAttributesId_Tmp' and object_id=object_id('PersonProperties'))
+	ALTER TABLE PersonProperties ADD PersonAttributesId_Tmp UNIQUEIDENTIFIER NOT NULL 
+	CONSTRAINT DF_PersonAttributesId_Tmp DEFAULT(NewID())
 GO
 
 INSERT INTO PersonAttributes (PersonAttributesId, PersonRegistrationId, EffectId)
-SELECT PersonAttributesId_Tmp, PersonAttributesId, EffectId
-FROM PersonProperties
+SELECT PP.PersonAttributesId_Tmp, PP.PersonAttributesId, PP.EffectId
+FROM PersonProperties PP
+LEFT OUTER JOIN PersonAttributes PA ON PP.PersonAttributesId = PP.PersoNAttributesId
+WHERE PP.PersoNAttributesId IS NULL
+
 GO
 
 UPDATE PersonProperties SET PersonAttributesId = PersonAttributesId_Tmp

-----  Allow multiple attributes in a single registration  ------
-----------------------------------------------------------------

sp_rename 'dbo.PersonAttributes.PersonRegistrationId', 'PersonAttributesId', 'COLUMN'
GO
ALTER TABLE dbo.PersonAttributes ADD PersonRegistrationId UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID()
GO
UPDATE dbo.PersonAttributes SET PersonRegistrationId = PersonAttributesId
GO
ALTER Table dbo.PersonAttributes DROP CONSTRAINT FK_PersonAttributes_PersonRegistration
GO
ALTER TABLE dbo.PersonAttributes ADD CONSTRAINT FK_PersonAttributes_PersonRegistration FOREIGN KEY(PersonRegistrationId) REFERENCES PersonRegistration(PersonRegistrationId) 
ON UPDATE CASCADE ON DELETE CASCADE
GO

sp_rename 'dbo.CprData.PersonRegistrationId', 'PersonAttributesId', 'COLUMN'
GO
sp_rename 'dbo.ForeignCitizenData.PersonRegistrationId', 'PersonAttributesId', 'COLUMN'
GO
sp_rename 'dbo.ForeignCitizenCountry.PersonRegistrationId', 'PersonAttributesId', 'COLUMN'
GO
sp_rename 'dbo.UnknownCitizenData.PersonRegistrationId', 'PersonAttributesId', 'COLUMN'
GO
sp_rename 'dbo.HealthInformation.PersonRegistrationId', 'PersonAttributesId', 'COLUMN'
GO
sp_rename 'dbo.PersonProperties.PersonRegistrationId', 'PersonAttributesId', 'COLUMN'
GO
sp_rename 'dbo.PersonName.PersonRegistrationId', 'PersonAttributesId', 'COLUMN'
GO



COLUMN PersonAttributesId_Tmp, EffectId
GO


-----------------------------------------------------
-----  UnknownCitizen_PersonAttributes cascade ------
-----------------------------------------------------

IF EXISTS (SELECT * FROM sys.sysconstraints WHERE constid= object_id('FK_UnknownCitizenData_PersonAttributes'))
	ALTER TABLE [dbo].[UnknownCitizenData]  DROP CONSTRAINT [FK_UnknownCitizenData_PersonAttributes] 
GO

ALTER TABLE [dbo].[UnknownCitizenData]  WITH CHECK ADD  CONSTRAINT [FK_UnknownCitizenData_PersonAttributes] FOREIGN KEY([PersonAttributesId])
