insert and referential integrity

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

insert and referential integrity

Author

Topic

kloepper
Yak Posting Veteran

77 Posts

Posted - 2005-11-19 : 15:50:54

I think that I have my tables normalized properly now.

If the record that I'm adding to the normalized tables has NEW data in the parent table, how can I add that NEW data without encountering the following error. (If I understand correctly, I can add all the NEW data to the Parent table in a separate operation, but what I'm asking is how I can add the NEW data to the parent table on the fly, at the same time that I'm processing the entire new record.)

The INSERT statement conflicted with the FOREIGN KEY constraint "FK__Daily_Index_CompanyName_and_CIK". The conflict occurred in database "SEC", table "CompanyName_and_CIK".

Sorry if this is a simple question, but I'm a rookie.

Thanks,

Paul
------------------------------------------------------------------
Here is the Insert Query that I'm using...below that are the table DMLs.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER procedure [dbo].[_InsertInto_Daily_Index_TEST]

AS

DECLARE
@CompanyName nvarchar(255),
@FormType nvarchar(50),
@CIK int,
@DateFiled int,
@SECWebAddress nvarchar(255)

SET @CompanyName = 'MAHON PAUL A'
SET @FormType = '4'
SET @CIK = '1231589'
SET @DateFiled = '20051118'
SET @SECWebAddress = 'edgar/data/1231589/000108255405000143/0001082554-05-000143.txt'

SELECT * FROM dbo.[_Daily_Index]

INSERT INTO
_Daily_Index
(CompanyName, FormType, CIK, DateFiled, SECWebAddress)

VALUES (@CompanyName, @FormType, @CIK, @DateFiled, @SECWebAddress)
--VALUES ('MAHON PAUL A', '4', '1231589', '20051118', 'edgar/data/1231589/000108255405000143/0001082554-05-000143.txt')

SELECT * FROM dbo.[_Daily_Index]

===========================================================

USE [SEC]

IF OBJECT_ID('dbo._Daily_Index') IS NOT NULL
DROP TABLE dbo._Daily_Index

GO
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
set ANSI_PADDING ON

GO
create procedure _Create_Normalized_SEC_Tables_No_Data
as
------------------------------------------------------------------------
GO
CREATE TABLE [dbo].[_Daily_Index](
[ID] [int] IDENTITY(1,1) NOT NULL,
[CompanyName] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CIK] [int] NULL,
[FormType] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DateFiled] [int] NULL,
[SECWebAddress] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FormXML] [xml] NULL,
[LastUpdated] [timestamp] NOT NULL
) ON [PRIMARY]

GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER TABLE [dbo].[_Daily_Index] WITH CHECK ADD CONSTRAINT [FK__Daily_Index_CompanyName_and_CIK] FOREIGN KEY( [CompanyName], [CIK])
REFERENCES [dbo].[CompanyName_and_CIK] ( [CompanyName], [CIK])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[_Daily_Index] WITH CHECK ADD CONSTRAINT [FK__Daily_Index_Date_Filed] FOREIGN KEY( [DateFiled])
REFERENCES [dbo].[Date_Filed] ( [DateFiled])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[_Daily_Index] WITH CHECK ADD CONSTRAINT [FK__Daily_Index_Form_Type] FOREIGN KEY( [FormType])
REFERENCES [dbo].[Form_Type] ( [FormType])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
USE [SEC]
------------------------------------------------------------------------
GO
CREATE TABLE [dbo].[CompanyName_and_CIK](
[CompanyName] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CIK] [int] NOT NULL,
CONSTRAINT [PK_CompanyName_and_CIK] PRIMARY KEY CLUSTERED
(
[CompanyName] ASC,
[CIK] ASC
) ON [PRIMARY]
) ON [PRIMARY]
------------------------------------------------------------------------
GO
CREATE TABLE [dbo].[Date_Filed](
[DateFiled] [int] NOT NULL,
CONSTRAINT [PK_Date_Filed] PRIMARY KEY CLUSTERED
(
[DateFiled] ASC
) ON [PRIMARY]
) ON [PRIMARY]
-----------------------------------------------------------------------
GO
CREATE TABLE [dbo].[Form_Type](
[FormType] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_Form_Type] PRIMARY KEY CLUSTERED
(
[FormType] ASC
) ON [PRIMARY]
) ON [PRIMARY]
-----------------------------------------------------------------------

kloepper
Yak Posting Veteran

77 Posts

Posted - 2005-11-19 : 18:18:22

I changed my Insert Query to the one below from the one I posted previously...it seems to work, sort of.

When I run it in the query analyzer, it inserts the new record in the parent table as it should, and the new data in the child tables as it should, but it also gives me error messages about not inserting redundant data in parent tables (Is this ok to run it and just ignore these error messages? I've included the error messages at the bottom, after the Insert query).

Can anyone verify if this is the correct way to go about this? or is there a better way of doing this.

Thank you,

Paul
-----------------------------------------------------------

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER procedure [dbo].[_InsertInto_Daily_Index_TEST]
AS

DECLARE
@CompanyName nvarchar(255),
@FormType nvarchar(50),
@CIK int,
@DateFiled int,
@SECWebAddress nvarchar(255)

SET @CompanyName = 'MAHON PAUL A'
SET @CIK = '1231589'
SET @FormType = '4'
SET @DateFiled = '20051118'
SET @SECWebAddress = 'edgar/data/1231589/000108255405000143/0001082554-05-000143.txt'

SELECT * FROM dbo.[_Daily_Index]

--Parent 1--------------------------------------------------------
INSERT INTO
CompanyName_and_CIK
(CompanyName, CIK)
VALUES (@CompanyName, @CIK)
--VALUES ('MAHON PAUL A', '1231589')

--Parent 2--------------------------------------------------------
INSERT INTO
Date_Filed
(DateFiled)
VALUES (@DateFiled)
--VALUES ('20051118')

--Parent 3--------------------------------------------------------
INSERT INTO
Form_Type
(FormType)
VALUES (@FormType)
--VALUES ('4')

--Child Table 1--------------------------------------------------------
INSERT INTO
_Daily_Index
(CompanyName, CIK, FormType, DateFiled, SECWebAddress)
VALUES (@CompanyName, @CIK, @FormType, @DateFiled, @SECWebAddress)
--VALUES ('MAHON PAUL A', '1231589', '4', '20051118', 'edgar/data/1231589/000108255405000143/0001082554-05-000143.txt')

SELECT * FROM dbo.[_Daily_Index]

=========================================================
Error messages

(8 row(s) affected)
Msg 2627, Level 14, State 1, Procedure _InsertInto_Daily_Index_TEST, Line 27
Violation of PRIMARY KEY constraint 'PK_CompanyName_and_CIK'. Cannot insert duplicate key in object 'dbo.CompanyName_and_CIK'.
The statement has been terminated.
Msg 2627, Level 14, State 1, Procedure _InsertInto_Daily_Index_TEST, Line 34
Violation of PRIMARY KEY constraint 'PK_Date_Filed'. Cannot insert duplicate key in object 'dbo.Date_Filed'.
The statement has been terminated.
Msg 2627, Level 14, State 1, Procedure _InsertInto_Daily_Index_TEST, Line 41
Violation of PRIMARY KEY constraint 'PK_Form_Type'. Cannot insert duplicate key in object 'dbo.Form_Type'.
The statement has been terminated.

(1 row(s) affected)

(9 row(s) affected)

(1 row(s) affected)

Kristen
Test

22859 Posts

Posted - 2005-11-20 : 02:54:28

Well now you are trying to insert records into the parent tables which already exist.

Presumably your FK is there to prevent you adding records to the Child Table if there is no parent record. What you are doing here is trying to add records tot eh parent to make sure that the child record will have parents.

That's unusual - normally a Parent not existing would be treated as an error - rather than taking the "Create the parent just-in-time if necessary" approach.

In your first post add a test to check that the parents exist, and raise an error if they don't.

In your second post add an IF NOT EXISTS ... before each Insert of a parent record, so that they are only added if not already existing.

Kristen

kloepper
Yak Posting Veteran

77 Posts

Posted - 2005-11-21 : 11:18:27

Kristen:

I'm not sure what you mean by add IF NOT EXISTS...this is what I did

IF NOT EXISTS (SELECT CompanyName,CIK FROM CompanyName_and_CIK)
INSERT INTO
CompanyName_and_CIK
(CompanyName, CIK)
VALUES (@CompanyName, @CIK)

This returns the same error that I had with the prior procedure.

I came across another comment about just ignoring the error messages. Actually, I've run the code a number of times now and have not yet observed any ill effects of just ignoring the error messages. I'll continue to monitor this, but I'm also moving on to other problems also.

Thank you for your comments.

Paul

Kristen
Test

22859 Posts

Posted - 2005-11-21 : 13:44:04

IF NOT EXISTS (SELECT * FROM CompanyName_and_CIK WHERE CompanyName = @CompanyName AND CIK = @CIK)

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources