The INSERT statement conflicted with the FOREIGN KEY constraint. The conflict occurred in database

5
votes

I have been having this problem for a couple of hours now. In SQL Server, I ran this query :

INSERT INTO USERS_AVATAR(userId, avatId) VALUES ('1', '213');
INSERT INTO USERS_AVATAR(userId, avatId) VALUES ('2', '312');

but the message shows up saying :

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FKUSERS_AVAT731248". The conflict occurred in database "gk314", table "gk314.USERS", column 'userId'.

Msg 547, Level 16, State 0, Line 2
The INSERT statement conflicted with the FOREIGN KEY constraint "FKUSERS_AVAT731248". The conflict occurred in database "gk314", table "gk314.USERS", column 'userId'.

Help please!

2
sqlsql-serverforeign-keys
Unrelated but: don't use string literals for numbers '1' is a string. 1 is a number. – a_horse_with_no_name
Does this answer your question? The INSERT statement conflicted with the FOREIGN KEY constraint – Necreaux

2 Answers

11
votes

Before inserting userIds 1 and 2 into USERS_AVATAR, you must first insert them into table USERS. That is what the FOREIGN KEY constraint is requiring.

1
votes

Foreign key constraints are SQL's way of saying "this table expects data to exist in other tables". It allows you to reference other tables without the data having to exist twice or to be kept in sync.

In this case, there's a table for user data (USERS) and a table for avatar data (AVATARS), and the USERS_AVATAR table links the two together. You will need to add a user to the users table, then an avatar to the avatar table, then you can link the two together. It will look something like this:

INSERT INTO USERS (userId, email, password, status) VALUES (1, '[email protected]',' gk314', 'strong')
INSERT INTO AVATARS (avatId, name, ...) VALUES (1, 'Avatar1', ...)
INSERT INTO USERS_AVATAR (userId, avatId) VALUES (1, 1)

Original Answer:

This means that there's a table, gk314.USERS, that doesn't have a userId that matches the userId you're attempting to add to USERS_AVATAR. Check the USERS table and add users with userId 1 and 2, and then you should be able to add to the USERS_AVATAR table.