Changing PK to compound primary key -> sync fails.

stephenm

Well I'm sure I'm doing something wrong... yet it eludes me! I created a 2 table test database that targets my issue in my production upgrade (to simplify things). I'll post the definitions below. Basically I have a parent & child relationship i.e. (1 to many) that fails to update if any data exists in the target tables. I had to add an additional column, forming a compound primary key, to the parent. The child table needs both columns referenced in the FK statement now... which fails. I've tried augmenting the table rebuild to no avail.

The 2 tables with the parent only having one primary key:

CREATE TABLE [dbo].[Employeee]
(
   [EmployeeName] [nvarchar](50) NOT NULL,
   [Address] [nvarchar](500) NULL,
   [Address2] [nvarchar](500) NULL,
   [City] [nvarchar](50) NULL,
   [State] [nvarchar](50) NULL,
   [Country] [nvarchar](50) NULL,
   CONSTRAINT [Employeee_PK] PRIMARY KEY CLUSTERED
   (
      [EmployeeName] ASC
   ) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Attendance]
(
   [AttendanceID] [int] NOT NULL,
   [EmployeeName] [nvarchar](50) NULL,
   [TimeIn] [datetime] NULL,
   [TimeOut] [datetime] NULL,
   CONSTRAINT [Attendance_PK] PRIMARY KEY CLUSTERED 
   (
     [AttendanceID] ASC
   ) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[Attendance] WITH CHECK ADD CONSTRAINT [Employeee_Attendance_FK1] FOREIGN KEY([EmployeeName])
REFERENCES [dbo].[Employeee] ([EmployeeName])
GO
ALTER TABLE [dbo].[Attendance] CHECK CONSTRAINT [Employeee_Attendance_FK1]

Updated the primary key on the parent table, I didn't paste the add column code:

PRINT N'Creating primary key [Employeee_PK] on [dbo].[Employeee]'
GO
ALTER TABLE [dbo].[Employeee] ADD CONSTRAINT [Employeee_PK] PRIMARY KEY CLUSTERED  ([EmployeeName], [EmployeeeID]) ON [PRIMARY]
GO

Attempted to add a FK constraint, which fails:

PRINT N'Adding foreign keys to [dbo].[Attendance]'
GO
ALTER TABLE [dbo].[Attendance] ADD
CONSTRAINT [Employeee_Attendance_FK1] FOREIGN KEY ([EmployeeName], [EmployeeeID]) REFERENCES [dbo].[Employeee] ([EmployeeName], [EmployeeeID])
GO

What am I missing!!??

Thanks,
Stephen Martin

stephenm

I had to write a UPDATE statement to SET the child table's new FK prior to creating the FK constraints. I've seen Red-Gate issue warnings on tables without 2 primary keys, but maybe this is a corner case.

Also I did in fact have the newid() set for all the default values, but newid() doesn't seem to work the same in this case.