conflicted with the FOREIGN KEY constraint

Question

i create a voucher entry system like accounting software

but i receive a error message

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "Payment_Voucher_All_Voucher_List". The conflict occurred in database "Accounting12", table "dbo.Payment_Voucher".

The statement has been terminated.

please anyone tale me what wrong with my database
and database schema is here

CREATE TABLE [dbo].[Receipt_Voucher] (
    [Id] INTEGER IDENTITY(90000000,1) NOT NULL,
    [Voucher_No] VARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Date] DATE,
    [Dr_Account] INTEGER,
    [Dr_Amount] MONEY,
    [Cr_Account] INTEGER,
    [Cr_Amount] MONEY,
    [voucher_type] VARCHAR(40) NOT NULL,
    CONSTRAINT [PK_Receipt_Voucher] PRIMARY KEY CLUSTERED ([Voucher_No], [voucher_type])
)
GO


/* ---------------------------------------------------------------------- */
/* Add table "Payment_Voucher"                                            */
/* ---------------------------------------------------------------------- */

CREATE TABLE [dbo].[Payment_Voucher] (
    [Id] INTEGER IDENTITY(90000000,1) NOT NULL,
    [Voucher_No] VARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Date] DATE,
    [Dr_Account] INTEGER,
    [Dr_Amount] MONEY,
    [Cr_Account] INTEGER,
    [Cr_Amount] MONEY,
    [voucher_type] VARCHAR(40) NOT NULL,
    CONSTRAINT [PK_Payment_Voucher] PRIMARY KEY CLUSTERED ([Voucher_No], [voucher_type])
)
GO


/* ---------------------------------------------------------------------- */
/* Add table "All_Voucher_List"                                           */
/* ---------------------------------------------------------------------- */

CREATE TABLE [dbo].[All_Voucher_List] (
    [Voucher_No] VARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [voucher_type] VARCHAR(40) NOT NULL,
    CONSTRAINT [PK_Voucher] PRIMARY KEY CLUSTERED ([Voucher_No], [voucher_type])
)
GO


/* ---------------------------------------------------------------------- */
/* Foreign key constraints                                                */
/* ---------------------------------------------------------------------- */

ALTER TABLE [dbo].[All_Voucher_List] ADD CONSTRAINT [Receipt_Voucher_All_Voucher_List] 
    FOREIGN KEY ([Voucher_No], [voucher_type]) REFERENCES [dbo].[Receipt_Voucher] ([Voucher_No],[voucher_type])
GO


ALTER TABLE [dbo].[All_Voucher_List] ADD CONSTRAINT [Payment_Voucher_All_Voucher_List] 
    FOREIGN KEY ([Voucher_No], [voucher_type]) REFERENCES [dbo].[Payment_Voucher] ([Voucher_No],[voucher_type])
GO

enter in receipt voucher

INSERT INTO dbo.Receipt_Voucher
        ( Voucher_No ,
          Date ,
          Dr_Account ,
          Dr_Amount ,
          Cr_Account ,
          Cr_Amount ,
          voucher_type
        )
VALUES  ( '0001' , -- Voucher_No - varchar(50)
          '2013-03-13 08:15:28' , -- Date - date
          5 , -- Dr_Account - int
          500 , -- Dr_Amount - money
          2 , -- Cr_Account - int
          500 , -- Cr_Amount - money
          '2'  -- voucher_type - varchar(40)
        )

now enter in All_Voucher_List

INSERT INTO dbo.All_Voucher_List
        ( Voucher_No, voucher_type )
VALUES  ( '0001', -- Voucher_No - varchar(50)
          '2'  -- voucher_type - varchar(40)
          )

but there is some errors

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "Payment_Voucher_All_Voucher_List". The conflict occurred in database "Accounting12", table "dbo.Payment_Voucher".
The statement has been terminated.

score 1 · Answer 1 · edited May 23 '17 at 12:28

A foreign key must reference only one parent table.

In your case the same columns [Voucher_No], [voucher_type] of All_Voucher_List table reference two tables [Receipt_Voucher] and [Payment_Voucher].

When you try to insert a record into Receipt_Voucher your second FK prevents you from that since you don't have corresponding record in Payment_Voucher table.

You can read more on that topic here

score 1 · Answer 2 · answered Mar 13 '13 at 09:52

peterm is correct, your second Constraint doesn't allow the Insert to go through.

My first idea would be to create a table which contains the all vouchers and use it in a single constraint, but I understand that your third table is exactly that table.

So my suggestion is to populate that table using triggers or other methods, when you populate the first two tables.

I know that it's another piece of code to maintain, but other possible workarounds (like to use a view or duplicate the keys to reference two tables) will just make your data model more complicate with little or no benefits and will create problems to who will work on those tables in the future..

conflicted with the FOREIGN KEY constraint

2 Answers2