[odb-users] MS SQL Foreign Key Error

[Dean Throop]

Hi Boris,

Thanks for the prompt reply.

That piece of code was an act of desperation that I thought would not work as the object would go out of scope.  My memory profiler though indicates that it was still in state and accessible in the main.  (aProduct.productType address initialized at 0x13bf7f0, new object and at iesDB->update address was 0x7fff85672dd0)  I will however make that change.  I am avoiding "advanced" pointers though as my preference is for memory issues to crash my program vs. being obscured by the garbage collector at the expense of some extra work and diligence on my part.

Below are my tables.

USE [PCI_IES_DB]
GO

/****** Object:  Table [dbo].[ItemTypes]    Script Date: 04/01/2013 07:58:31 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[ItemTypes](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](50) NOT NULL,
	[IsProduct] [bit] NOT NULL,
 CONSTRAINT [PK_ItemTypes] PRIMARY KEY CLUSTERED 
(
	[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

SET ANSI_PADDING OFF
GO

USE [PCI_IES_DB]
GO

/****** Object:  Table [dbo].[Products]    Script Date: 04/01/2013 07:58:03 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Products](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[InstanceID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[Name] [varchar](50) NOT NULL,
	[Description] [varchar](1000) NULL,
	[LogicalGroupID] [int] NOT NULL,
	[ItemTemplateID] [int] NOT NULL,
	[Width] [decimal](18, 2) NOT NULL,
	[Height] [decimal](18, 2) NOT NULL,
	[Depth] [decimal](18, 2) NOT NULL,
	[FinishedLeft] [bit] NULL,
	[FinishedRight] [bit] NULL,
	[NumberOfShelves] [int] NULL,
	[ProductStatusID] [int] NOT NULL,
	[ProjectColorSchemeID] [int] NOT NULL,
	[ProjectMaterialSchemeID] [int] NOT NULL,
	[ProjectHardwareSchemeID] [int] NOT NULL,
	[ProjectDesignSchemeID] [int] NOT NULL,
	[ProductContourID] [uniqueidentifier] NULL,
	[ItemTypeID] [int] NULL,
	[IsCustom] [bit] NOT NULL,
 CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED 
(
	[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

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Products]  WITH CHECK ADD  CONSTRAINT [FK_Products_ItemTemplates] FOREIGN KEY([ItemTemplateID])
REFERENCES [dbo].[ItemTemplates] ([ID])
GO

ALTER TABLE [dbo].[Products] CHECK CONSTRAINT [FK_Products_ItemTemplates]
GO

ALTER TABLE [dbo].[Products]  WITH CHECK ADD  CONSTRAINT [FK_Products_ItemTypes] FOREIGN KEY([ItemTypeID])
REFERENCES [dbo].[ItemTypes] ([ID])
GO

ALTER TABLE [dbo].[Products] CHECK CONSTRAINT [FK_Products_ItemTypes]
GO

ALTER TABLE [dbo].[Products]  WITH CHECK ADD  CONSTRAINT [FK_Products_LogicalGroups] FOREIGN KEY([LogicalGroupID])
REFERENCES [dbo].[LogicalGroups] ([ID])
GO

ALTER TABLE [dbo].[Products] CHECK CONSTRAINT [FK_Products_LogicalGroups]
GO

ALTER TABLE [dbo].[Products]  WITH CHECK ADD  CONSTRAINT [FK_Products_ProductContours] FOREIGN KEY([ProductContourID])
REFERENCES [dbo].[ProductContours] ([ID])
GO

ALTER TABLE [dbo].[Products] CHECK CONSTRAINT [FK_Products_ProductContours]
GO

ALTER TABLE [dbo].[Products]  WITH CHECK ADD  CONSTRAINT [FK_Products_ProductStatuses] FOREIGN KEY([ProductStatusID])
REFERENCES [dbo].[ProductStatuses] ([ID])
GO

ALTER TABLE [dbo].[Products] CHECK CONSTRAINT [FK_Products_ProductStatuses]
GO

ALTER TABLE [dbo].[Products]  WITH CHECK ADD  CONSTRAINT [FK_Products_ProjectSchemes] FOREIGN KEY([ProjectColorSchemeID])
REFERENCES [dbo].[ProjectSchemes] ([ID])
GO

ALTER TABLE [dbo].[Products] CHECK CONSTRAINT [FK_Products_ProjectSchemes]
GO

ALTER TABLE [dbo].[Products]  WITH CHECK ADD  CONSTRAINT [FK_Products_ProjectSchemes1] FOREIGN KEY([ProjectDesignSchemeID])
REFERENCES [dbo].[ProjectSchemes] ([ID])
GO

ALTER TABLE [dbo].[Products] CHECK CONSTRAINT [FK_Products_ProjectSchemes1]
GO

ALTER TABLE [dbo].[Products]  WITH CHECK ADD  CONSTRAINT [FK_Products_ProjectSchemes2] FOREIGN KEY([ProjectHardwareSchemeID])
REFERENCES [dbo].[ProjectSchemes] ([ID])
GO

ALTER TABLE [dbo].[Products] CHECK CONSTRAINT [FK_Products_ProjectSchemes2]
GO

ALTER TABLE [dbo].[Products]  WITH CHECK ADD  CONSTRAINT [FK_Products_ProjectSchemes3] FOREIGN KEY([ProjectMaterialSchemeID])
REFERENCES [dbo].[ProjectSchemes] ([ID])
GO

ALTER TABLE [dbo].[Products] CHECK CONSTRAINT [FK_Products_ProjectSchemes3]
GO

ALTER TABLE [dbo].[Products] ADD  CONSTRAINT [DF_Products_InstanceID]  DEFAULT (newid()) FOR [InstanceID]
GO

ALTER TABLE [dbo].[Products] ADD  CONSTRAINT [DF_Products_IsCustom]  DEFAULT ((0)) FOR [IsCustom]
GO


-----Original Message-----
From: Boris Kolpackov [mailto:boris at codesynthesis.com] 
Sent: Monday, April 01, 2013 4:31 AM
To: Dean Throop
Cc: odb-users at codesynthesis.com
Subject: Re: [odb-users] MS SQL Foreign Key Error

Hi Dean,

Dean Throop <DThroop at pacificcabinets.com> writes:

> I have a Products table with a to-one relationship to an ItemTypes table.

Can you show the definitions for these tables (i.e., the CREATE TABLE statements).


>  for(std::vector<ItemType>::const_iterator i(productTypes.begin 
> ());i!=productTypes.end ();++i)  {
>      if(i->GetName()==currentProductType)
>      {
>          ItemType theValue=*i;
>          productType=&theValue;
>          break;
>      }
>  }

This code is not going to work. You are creating a local copy (on the
stack) of the ItemType object (theValue) and then assign a pointer to it to the data member (productType). You are lucky you are getting an exception. Normally you would end up with access violation.

A quick and dirty fix would be to instead assign a pointer to the object that is in the vector (you will either need to pass non-const reference to the vector of make productType const):

productType=&*i;

Better yet, use std::shared_ptr to manage your objects.

Boris