CODEWITHSUNDEEP
sqlsql-serverdatabaseado.net
Kirill Ryzhkov
Kirill Ryzhkov

Reputation: 522

The INSERT statement conflicted with the FOREIGN KEY constraint

I searched for this kind of problem, but unfortunately didn't find any solution.

When I try to create a contact in my application I get an error

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Contacts_UserProfile". The conflict occurred in database "ContactAppContext", table "dbo.UserProfile", column 'UserId'.

I want to associate UserId with the Contacts table

My tables look like this:

CREATE TABLE [dbo].[Contacts] (
    [ContactId] INT            IDENTITY (1, 1) NOT NULL,
    [UserId]    INT            NOT NULL,
    [FirstName] NVARCHAR (MAX) NOT NULL,
    [LastName]  NVARCHAR (MAX) NOT NULL,
    [Address]   NVARCHAR (MAX) NOT NULL,
    [City]      NVARCHAR (MAX) NOT NULL,
    [Phone]     NVARCHAR (MAX) NOT NULL,
    [Email]     NVARCHAR (MAX) NOT NULL,
    CONSTRAINT [PK_dbo.Contacts] PRIMARY KEY CLUSTERED ([ContactId] ASC),
    CONSTRAINT [FK_Contacts_UserProfile] FOREIGN KEY ([UserId]) REFERENCES [dbo].[UserProfile] ([UserId])
);

CREATE TABLE [dbo].[UserProfile] (
    [UserId]   INT           IDENTITY (1, 1) NOT NULL,
    [UserName] NVARCHAR (56) NOT NULL,
    PRIMARY KEY CLUSTERED ([UserId] ASC),
    UNIQUE NONCLUSTERED ([UserName] ASC)
);

As you can see in the picture, UserId exists in UserProfile table

pic

So what am I doing wrong?

EDIT 1
@Alexander Fedorenko
You mean this code?

SET IDENTITY_INSERT [dbo].[UserProfile] ON
INSERT INTO [dbo].[UserProfile] ([UserId], [UserName]) VALUES (1, N'admin')
INSERT INTO [dbo].[UserProfile] ([UserId], [UserName]) VALUES (2, N'test')
INSERT INTO [dbo].[UserProfile] ([UserId], [UserName]) VALUES (3, N'user')
SET IDENTITY_INSERT [dbo].[UserProfile] OFF

@Sachin How can I make sure, that when I try to insert in Contact table, it should be present in UserProfile table? I have, for example a user with UserId = 3, who is logged in and when he insert data, created contact would relate to that user.

EDIT 2
So when I created an editor field for UserId in the view, and it seems to be working, when I specify the UserId, and data is created, but I don't want this editor field for UserId in Create Contact page exist, because it is inconvenient that a user will write his UserId. So is it possible to relate UserId to logged in user, for example when he creates data, he doesn't need to specify his UserId, instead the record will be automatically saved to a database with his Id?

Upvotes: 1

Views: 18333

Answers (2)

Joel Cuff
Joel Cuff

Reputation: 1038

You need to either add data to UserProfile table first or to temporary disable foreign key constraint while data is inserted.

Your tables are set so that Contacts table is referencing UserProfile table therefore entry in UserProfile is needed in order to insert Contact.

Look at it this way: How can you know what is going to be UserID you want to enter in Contacts table if you don’t generate it in UserProfile table first?

Upvotes: 2

Stefan Leisenberger
Stefan Leisenberger

Reputation: 11

Try to create the UserProfile table first and then the Contacts table.

Upvotes: 0

Related Questions