FK association not created at import from ODBC

[Geert Bellekens]

When a foreign key is not referencing the Primary key of the other table (it exists in the wild, don't ask ) the FK association is not created.
Only the FK operation is created.
The result is that there is no indication in the database that both tables are related to each other.

Example database (SQL Server 2012):

Code: [Select]

USE [test database]
GO
/****** Object:  Table [dbo].[MESSAGE_IN]    Script Date: 15/11/2018 13:28:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MESSAGE_IN](
[MESSAGE_IN_ID] [bigint] IDENTITY(1,1) NOT NULL,
[MESSAGE_ID] [bigint] NOT NULL,
 CONSTRAINT [PK__MESSAGE_IN] PRIMARY KEY CLUSTERED
(
[MESSAGE_IN_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[MESSAGE_IN2]    Script Date: 15/11/2018 13:28:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MESSAGE_IN2](
[MESSAGE_IN2_ID] [bigint] IDENTITY(1,1) NOT NULL,
[MESSAGE_ID] [bigint] NOT NULL,
 CONSTRAINT [PK__MESSAGE_IN2] PRIMARY KEY CLUSTERED
(
[MESSAGE_IN2_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[MESSAGE_OUT]    Script Date: 15/11/2018 13:28:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MESSAGE_OUT](
[MESSAGE_OUT_ID] [bigint] IDENTITY(1,1) NOT NULL,
[REFERENCE_MESSAGE_ID] [bigint] NOT NULL,
 CONSTRAINT [PK__MESSAGE_OUT] PRIMARY KEY CLUSTERED
(
[MESSAGE_OUT_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[MESSAGE_OUT2]    Script Date: 15/11/2018 13:28:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MESSAGE_OUT2](
[MESSAGE_OUT2_ID] [bigint] IDENTITY(1,1) NOT NULL,
[REFERENCE_MESSAGE_ID] [bigint] NOT NULL,
 CONSTRAINT [PK__MESSAGE_OUT2] PRIMARY KEY CLUSTERED
(
[MESSAGE_OUT2_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[MESSAGE_OUT]  WITH CHECK ADD  CONSTRAINT [FK_MESSAGE_OUT_MESSAGE_IN] FOREIGN KEY([REFERENCE_MESSAGE_ID])
REFERENCES [dbo].[MESSAGE_IN] ([MESSAGE_ID])
GO
ALTER TABLE [dbo].[MESSAGE_OUT] CHECK CONSTRAINT [FK_MESSAGE_OUT_MESSAGE_IN]
GO
ALTER TABLE [dbo].[MESSAGE_OUT2]  WITH CHECK ADD  CONSTRAINT [FK_MESSAGE_OUT2_MESSAGE_IN2] FOREIGN KEY([REFERENCE_MESSAGE_ID])
REFERENCES [dbo].[MESSAGE_IN2] ([MESSAGE_IN2_ID])
GO
ALTER TABLE [dbo].[MESSAGE_OUT2] CHECK CONSTRAINT [FK_MESSAGE_OUT2_MESSAGE_IN2]
GO
Result is that between MESSAGE_OUT and MESSAGE_IN there is no relation whereas between MESSAGE_OUT2 and MESSAGE_IN2 there is one.
The only difference is the fact that one uses a primary key and the other one doesn't.



Reported

Geert

[Paolo F Cantoni]

Hi Geert,

When I try this on MS SQL Server, I get:

Code: [Select]

Msg 1776, Level 16, State 0, Line 63
There are no primary or candidate keys in the referenced table 'dbo.MESSAGE_IN' that match the referencing column list in the foreign key 'FK_MESSAGE_OUT_MESSAGE_IN'.
Msg 1750, Level 16, State 0, Line 63
Could not create constraint. See previous errors.
Msg 4917, Level 16, State 0, Line 66
Constraint 'FK_MESSAGE_OUT_MESSAGE_IN' does not exist.
Msg 4916, Level 16, State 0, Line 66
Could not enable or disable the constraint. See previous errors.
Could these be related to the issue?

Paolo

[Geert Bellekens]

I must have forgotten to include the unique constraint on MESSAGE_IN.

Will check later today.

Geert

[Geert Bellekens]

Apparently the automatic script creation doesn't include unique indexes. Below the complete script.

Code: [Select]

/****** Object:  Table [dbo].[MESSAGE_IN]    Script Date: 16/11/2018 13:05:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MESSAGE_IN](
[MESSAGE_IN_ID] [bigint] IDENTITY(1,1) NOT NULL,
[MESSAGE_ID] [bigint] NOT NULL,
 CONSTRAINT [PK__MESSAGE_IN] PRIMARY KEY CLUSTERED
(
[MESSAGE_IN_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Index [messageID]    Script Date: 16/11/2018 13:08:36 ******/
CREATE UNIQUE NONCLUSTERED INDEX [messageID] ON [dbo].[MESSAGE_IN]
(
[MESSAGE_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[MESSAGE_IN2]    Script Date: 16/11/2018 13:05:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MESSAGE_IN2](
[MESSAGE_IN2_ID] [bigint] IDENTITY(1,1) NOT NULL,
[MESSAGE_ID] [bigint] NOT NULL,
 CONSTRAINT [PK__MESSAGE_IN2] PRIMARY KEY CLUSTERED
(
[MESSAGE_IN2_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[MESSAGE_OUT]    Script Date: 16/11/2018 13:05:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MESSAGE_OUT](
[MESSAGE_OUT_ID] [bigint] IDENTITY(1,1) NOT NULL,
[REFERENCE_MESSAGE_ID] [bigint] NOT NULL,
 CONSTRAINT [PK__MESSAGE_OUT] PRIMARY KEY CLUSTERED
(
[MESSAGE_OUT_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[MESSAGE_OUT2]    Script Date: 16/11/2018 13:05:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MESSAGE_OUT2](
[MESSAGE_OUT2_ID] [bigint] IDENTITY(1,1) NOT NULL,
[REFERENCE_MESSAGE_ID] [bigint] NOT NULL,
 CONSTRAINT [PK__MESSAGE_OUT2] PRIMARY KEY CLUSTERED
(
[MESSAGE_OUT2_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[MESSAGE_OUT]  WITH CHECK ADD  CONSTRAINT [FK_MESSAGE_OUT_MESSAGE_IN] FOREIGN KEY([REFERENCE_MESSAGE_ID])
REFERENCES [dbo].[MESSAGE_IN] ([MESSAGE_ID])
GO
ALTER TABLE [dbo].[MESSAGE_OUT] CHECK CONSTRAINT [FK_MESSAGE_OUT_MESSAGE_IN]
GO
ALTER TABLE [dbo].[MESSAGE_OUT2]  WITH CHECK ADD  CONSTRAINT [FK_MESSAGE_OUT2_MESSAGE_IN2] FOREIGN KEY([REFERENCE_MESSAGE_ID])
REFERENCES [dbo].[MESSAGE_IN2] ([MESSAGE_IN2_ID])
GO
ALTER TABLE [dbo].[MESSAGE_OUT2] CHECK CONSTRAINT [FK_MESSAGE_OUT2_MESSAGE_IN2]
GO
Geert