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
The 2 tables with the parent only having one primary key:
Updated the primary key on the parent table, I didn't paste the add column code:
Attempted to add a FK constraint, which fails:
What am I missing!!??
Thanks,
Stephen Martin