SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint “FK_Register_Event”

I have three tables in SQL server;

Event table:

CREATE TABLE [dbo].[Event] 
(
    [EventName]   VARCHAR(25) NOT NULL,
    [Description] VARCHAR(60) NULL,
    [Location]    VARCHAR(20) NULL,
    [Date]        VARCHAR(10) NULL,
    [TicketFee]   FLOAT(53)   NULL,
    PRIMARY KEY CLUSTERED ([EventName] ASC)
);

Client:

CREATE TABLE [dbo].[Client] 
(
    [Email]    VARCHAR(35) NOT NULL,
    [FullName] VARCHAR(25) NULL,
    [Address]  VARCHAR(50) NULL,
    [Age]      INT          NULL,
    [Phone]    VARCHAR(15) NULL,
    PRIMARY KEY CLUSTERED ([Email] ASC)
);

Register:

CREATE TABLE [dbo].[Register] 
(
    [Id]            INT         NOT NULL,
    [GuestNum]      INT         NULL,
    [PaymentAmount] FLOAT(53)   NULL,
    [EventName]     VARCHAR(25) NULL,
    [Email]         VARCHAR(35) NULL,

    PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_Register_Client] 
         FOREIGN KEY ([Email]) REFERENCES [dbo].[Client] ([Email]),
    CONSTRAINT [FK_Register_Event] 
         FOREIGN KEY ([EventName]) REFERENCES [dbo].[Event] ([EventName])
);

The Event table consists of dummy data for search function only, and when a user tries to register a new event, it is stored in Register table.

Trouble is that I am getting an error:

SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint “FK_Register_Event”. The conflict occurred in database “C:USERSRHYMESOURCEREPOSONLINEEVENTREGISTRATIONONLINEEVENTREGISTRATIONAPP_DATADATABASE1.MDF”, table “dbo.Event”, column ‘EventName’.

I searched Internet and learned that it’s because I’m trying to insert a record with a value in the foreign key column that doesn’t exist in the foreign table. However even if I try to register an event with a different EventName that does not exist in the Event table it still throws the same error.

I’m really confused where in my table is causing the problem…I’m new to database and any help would be appreciated.

Answer

The Event table consists of dummy data for search function only, and when a user tries to register a new event, it is stored in Register table.

That’s not the way this database was intended to be used, based on the table design. Events should be defined in the Event table because that table was built to hold the attributes (columns) of an “Event” entity.

The Client table holds attributes about people who are registered to attend events.

The Register table defines a many-to-many relationship between “Clients” and “Events”. One client may attend many events. One event may be attended by many clients.

The foreign key relationship between Register and Event means that the EventName must be defined in the Event table before it can be inserted into the Register table.

If you intend to keep this data model, your code will have to be adjusted to create an event in the Event table prior to creating registrations for the event. If you intend to use the tables as you describe above (creating events through registrations) then you need a new data model.