Argthtjtr

I can't insert data in a table where 2 foreign keys refer to one primary key...

The code is as follows:

create table Currency 

(

    ID int primary key identity(1,1),

    Code nvarchar(8) not null,

    Name nvarchar(128) not null,

    Is_Active bit not null default(0),

    Is_Base_Currency bit not null default(0),

    Country_id int foreign key(ID) references Country(ID) not null

)



Create table Currency_rate 

(

    ID int primary key identity(1,1),

    Currency_id int foreign key(ID) references Currency(ID) not null,

    Base_currency_id int foreign key(ID) references Currency(ID) not null,

    Rate decimal(16,6) not null,

    Ts datetime default getDate()

)



Insert into Currency_rate(Currency_id, Base_currency_id, Rate, Ts)

values (1, 1, 121212.212121, '2008-11-11 13:23:44.111'),

       (2, 2, 232323.323232, '2009-11-11 13:23:44.222'),

       (3, 3, 343434.434343, '2010-11-11 13:23:44.333')

This is the error I get:

Msg 547, Level 16, State 0, Line 1

The INSERT statement conflicted with the FOREIGN KEY constraint "FK__Currency___Curre__239E4DCF". The conflict occurred in database "CryptoCurrencyData", table "dbo.Currency", column 'ID'.
The statement has been terminated.

Please help me - I can't find any solution surfing the internet...

Thank you all,

Regards,
Elias.H

Constrainsts are created to prevent corrupting data by inserting or updating your tables.

In your case, you are trying to insert data which is not existing. You are trying to insert into Currency_rate values ID's of Currency which are not existing in Currency table. So this is a whole goal of constraints - prevent corruption of data.

Just for demonstration purposes I've created Country table:

Create table Country (

  ID int primary key identity(1,1),

  CountryName nvarchar(50)

)

Then your first step would be:

INSERT INTO dbo.Country

(

    --ID - this column value is auto-generated

    CountryName

)

VALUES

(

    -- ID - int

    N'India' -- CountryName - nvarchar

)

, (N'Canada')

, (N'South America')

The second step will be:

INSERT INTO dbo.Currency

(

    --ID - this column value is auto-generated

    Code,

    Name,

    Is_Active,

    Is_Base_Currency,

    Country_id

)

VALUES

(

    -- ID - int

    N'Code1', -- Code - nvarchar

    N'India Currency', -- Name - nvarchar

    0, -- Is_Active - bit

    0, -- Is_Base_Currency - bit

    1 -- Country_id - int

)

, (

 N'Code2', -- Code - nvarchar

    N'Canada Currency', -- Name - nvarchar

    0, -- Is_Active - bit

    0, -- Is_Base_Currency - bit

    2 -- Country_id - int

)

, (

 N'Code3', -- Code - nvarchar

    N'South America Currency', -- Name - nvarchar

    0, -- Is_Active - bit

    0, -- Is_Base_Currency - bit

    3 -- Country_id - int

)

And the final step is the following:

Insert into Currency_rate(Currency_id,Base_currency_id,Rate,Ts)

values(1,1,121212.212121,'2008-11-11 13:23:44.111'),

(2,2,232323.323232,'2009-11-11 13:23:44.222'),

(3,3,343434.434343,'2010-11-11 13:23:44.333')