I have three tables in SQL server;
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) );
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) );
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]) );
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.
Eventtable 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.
Client table holds attributes about people who are registered to attend events.
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
Event means that the
EventName must be defined in the
Event table before it can be inserted into the
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.