SQL The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Score_Gebruikersnaam"

Question

I have a create script for my SQL database (see below). Everything works fine except when I run the INSERT INTO Scores() I get an error.

The error I get is:

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Score_Gebruikersnaam". The conflict occurred in database "gamescores", table "dbo.Players", column 'gebruikersnaam'

I don't understand what I'm doing wrong so please help me :)

I already tried to drop the database first and then run the rest of the script but that didn't help. I think something went wrong with the foreign key...

Thanks!

USE master;
GO

DROP DATABASE IF EXISTS [gamescores];

CREATE DATABASE gamescores; 
GO 

USE gamescores;
GO

SET DATEFORMAT dmy;

CREATE TABLE [Players]
(
    [gebruikersnaam] VARCHAR(75) NOT NULL,
    [voornaam]       VARCHAR(75) NOT NULL,
    [achternaam]     VARCHAR(75) NOT NULL,
    [emailadres]     VARCHAR(75) NOT NULL,
    [geboortdatum]   DATE  NOT NULL
);

CREATE TABLE [Scores]
(
    [scoreID]        INT IDENTITY(1,1) NOT NULL,
    [gebruikersnaam] VARCHAR(75) NOT NULL,
    [aantalScore]    INT NOT NULL,
    [datum]          DATE NOT NULL
);

ALTER TABLE [Players]
    ADD CONSTRAINT [PK_Speler]
        PRIMARY KEY (gebruikersnaam);

ALTER TABLE [Scores]
    ADD CONSTRAINT [PK_Score]
        PRIMARY KEY (scoreID);

ALTER TABLE [Players]
    ADD CONSTRAINT [AK_Speler_Emailadres]
        UNIQUE (emailadres)

ALTER TABLE [Scores]
    ADD CONSTRAINT [FK_Score_Gebruikersnaam]
        FOREIGN KEY (gebruikersnaam) REFERENCES Players(gebruikersnaam);
GO

INSERT INTO Players([gebruikersnaam], [voornaam], [achternaam], [emailadres], [geboortdatum]) 
VALUES ('apraundlin1', 'Angelle', 'Praundlin', '[email protected]', '15-9-1997'),
       ('rnoore3', 'Rebekah', 'Noore', '[email protected]', '9-10-1987'),
       ('nplevinh', 'Nicolais', 'Plevin', '[email protected]', '18-3-2001');

-- SCORE table vullen
INSERT INTO Scores([gebruikersnaam], [aantalScore], [datum]) 
VALUES ('rsprasen0', 551, '15-5-2021'),
       ('fwhawell8', 309, '8-4-2021'),
       ('rgravett9', 1063, '16-11-2021');

Answer 1

You got an error because the database schema, though a foreign key, enforces that a player referenced in the scores table must first exist in the players table. Add the player to the players table, before trying to update their score in the scores table.