1
Vote

Error creating update script

description

When 2 tables with a multi column primary key are linked to each other the update query voor generating te link is not correct.
The fields are not in the correct order. they must be in the same order as the primary key.

ALTER TABLE [dbo].[Sales] ADD
[ParentBusinessRelationId] [int] NULL,
[ParentRelationTypeId] [int] NULL
GO
ALTER TABLE [dbo].[Sales] ALTER COLUMN [RelationTypeId] [int] NULL
GO
ALTER TABLE [dbo].[Sales] ALTER COLUMN [BusinessRelationId] [int] NULL
GO
ALTER TABLE [dbo].[Sales] ADD CONSTRAINT [FK_Sales_BusinessRelationType] FOREIGN KEY
(
    [BusinessRelationId],
    [RelationTypeId],
    [ParentBusinessRelationId],
    [ParentRelationTypeId]
)
REFERENCES [dbo].[BusinessRelationType]
(
    [BusinessRelationId],
    [RelationTypeId],
    [ParentBusinessRelationId],
    [ParentRelationTypeId]
)
GO

should be:

ALTER TABLE [dbo].[Sales] ADD
[ParentBusinessRelationId] [int] NULL,
[ParentRelationTypeId] [int] NULL
GO
ALTER TABLE [dbo].[Sales] ALTER COLUMN [RelationTypeId] [int] NULL
GO
ALTER TABLE [dbo].[Sales] ALTER COLUMN [BusinessRelationId] [int] NULL
GO
ALTER TABLE [dbo].[Sales] ADD CONSTRAINT [FK_Sales_BusinessRelationType] FOREIGN KEY
(
    [RelationTypeId],
    [BusinessRelationId],
    [ParentBusinessRelationId],
    [ParentRelationTypeId]
)
REFERENCES [dbo].[BusinessRelationType]
(
    [RelationTypeId],
    [BusinessRelationId],
    [ParentBusinessRelationId],
    [ParentRelationTypeId]
)
GO

comments