INSERT IGNORE INTO WomenWCSchedule (Bracket, Match_ID, Referee_ID, Team_1, Team_2, Date, Time, Stadium) VALUES
('Group Stage A', 'M01', 'R05', 'New Zealand', 'Norway', '2023-07-20', '19:00:00', 'EDEN PARK'),
('Group Stage B', 'M02', 'R22', 'Australia', 'Republic of Ireland', '2023-07-20', '20:00:00', 'Stadium Australia'),
('Group Stage A', 'M03', 'R06', 'Philippines', 'Switzerland', '2023-07-21', '17:00:00', 'Dunedin Stadium'),
('Group Stage B', 'M04', 'R25', 'Nigeria', 'Canada', '2023-07-21', '12:30:00', 'Melbourne Rectangular Stadium'),
('Group Stage C', 'M05', 'R02', 'Spain', 'Costa Rica', '2023-07-21', '19:30:00', 'Wellington Regional Stadium'),
('Group Stage E', 'M06', 'R07', 'USA', 'Vietnam', '2023-07-22', '13:00:00', 'EDEN PARK'),
('Group Stage C', 'M07', 'R23', 'Zambia', 'Japan', '2023-07-22', '19:00:00', 'Waikato Stadium'),
('Group Stage D', 'M08', 'R10', 'England', 'Haiti', '2023-07-22', '19:30:00', 'Brisbane Stadium'),
('Group Stage D', 'M09', 'R08', 'Denmark', 'China', '2023-07-22', '20:00:00', 'Perth Rectangular Stadium'),
('Group Stage F', 'M10', 'R24', 'France', 'Jamaica', '2023-07-23', '20:00:00', 'Sydney Football Stadium'),
('Group Stage E', 'M11', 'R21', 'Netherlands', 'Portugal', '2023-07-23', '19:30:00', 'Dunedin Stadium'),
('Group Stage G', 'M12', 'R02', 'Sweden', 'South Africa', '2023-07-23', '17:00:00', 'Wellington Regional Stadium'),
('Group Stage G', 'M13', 'R26', 'Italy', 'Argentina', '2023-07-24', '18:00:00', 'EDEN PARK'),
('Group Stage H', 'M14', 'R08', 'Germany', 'Morocco', '2023-07-24', '18:30:00', 'Melbourne Rectangular Stadium'),
('Group Stage F', 'M15', 'R30', 'Brazil', 'Panama', '2023-07-24', '20:30:00', 'Hindmarsh Stadium'),
('Group Stage H', 'M16', 'R10', 'Colombia', 'South Korea', '2023-07-25', '12:00:00', 'Sydney Football Stadium'),
('Group Stage A', 'M17', 'R02', 'New Zealand', 'Philippines', '2023-07-25', '17:30:00', 'Wellington Regional Stadium'),
('Group Stage A', 'M18', 'R27', 'Switzerland', 'Norway', '2023-07-25', '20:00:00', 'Waikato Stadium'),
('Group Stage C', 'M19', 'R03', 'Spain', 'Zambia', '2023-07-26', '19:30:00', 'EDEN PARK'),
('Group Stage C', 'M20', 'R31', 'Japan', 'Costa Rica', '2023-07-26', '17:00:00', 'Dunedin Stadium'),
('Group Stage B', 'M21', 'R08', 'Canada', 'Republic of Ireland', '2023-07-26', '20:00:00', 'Perth Rectangular Stadium'),
('Group Stage E', 'M22', 'R02', 'USA', 'Netherlands', '2023-07-27', '13:00:00', 'Wellington Regional Stadium'),
('Group Stage E', 'M23', 'R09', 'Portugal', 'Vietnam', '2023-07-27', '19:30:00', 'Waikato Stadium'),
('Group Stage B', 'M24', 'R28', 'Australia', 'Nigeria', '2023-07-27', '20:00:00', 'Brisbane Stadium'),
('Group Stage D', 'M25', 'R03', 'England', 'Denmark', '2023-07-28', '18:30:00', 'Sydney Football Stadium'),
('Group Stage G', 'M26', 'R29', 'Argentina', 'South Africa', '2023-07-28', '12:00:00', 'Dunedin Stadium'),
('Group Stage D', 'M27', 'R10', 'China', 'Haiti', '2023-07-28', '20:30:00', 'Hindmarsh Stadium'),
('Group Stage G', 'M28', 'R24', 'Sweden', 'Italy', '2023-07-29', '19:30:00', 'Wellington Regional Stadium'),
('Group Stage F', 'M29', 'R01', 'France', 'Brazil', '2023-07-29', '20:00:00', 'Brisbane Stadium'),
('Group Stage F', 'M30', 'R08', 'Panama', 'Jamaica', '2023-07-29', '20:30:00', 'Perth Rectangular Stadium'),
('Group Stage A', 'M31', 'R34', 'Norway', 'Philippines', '2023-07-30', '19:00:00', 'EDEN PARK'),
('Group Stage H', 'M32', 'R05', 'Germany', 'Colombia', '2023-07-30', '19:30:00', 'Sydney Football Stadium'),
('Group Stage A', 'M33', 'R14', 'Switzerland', 'New Zealand', '2023-07-30', '19:00:00', 'Dunedin Stadium'),
('Group Stage H', 'M34', 'R15', 'South Korea', 'Morocco', '2023-07-30', '14:00:00', 'Hindmarsh Stadium'),
('Group Stage B', 'M35', 'R16', 'Canada', 'Australia', '2023-07-31', '20:00:00', 'Melbourne Rectangular Stadium'),
('Group Stage C', 'M36', 'R15', 'Japan', 'Spain', '2023-07-31', '19:00:00', 'Wellington Regional Stadium'),
('Group Stage C', 'M37', 'R02', 'Costa Rica', 'Zambia', '2023-07-31', '19:00:00', 'Waikato Stadium'),
('Group Stage B', 'M38', 'R33', 'Republic of Ireland', 'Nigeria', '2023-07-31', '20:00:00', 'Brisbane Stadium'),
('Group Stage E', 'M39', 'R16', 'Portugal', 'USA', '2023-08-01', '19:00:00', 'EDEN PARK'),
('Group Stage E', 'M40', 'R17', 'Vietnam', 'Netherlands', '2023-08-01', '19:00:00', 'Dunedin Stadium'),
('Group Stage D', 'M41', 'R03', 'Haiti', 'Denmark', '2023-08-01', '19:00:00', 'Perth Rectangular Stadium'),
('Group Stage D', 'M42', 'R04', 'China', 'England', '2023-08-01', '20:30:00', 'Hindmarsh Stadium'),
('Group Stage F', 'M43', 'R18', 'Panama', 'France', '2023-08-02', '20:00:00', 'Sydney Football Stadium'),
('Group Stage F', 'M44', 'R06', 'Jamaica', 'Brazil', '2023-08-02', '20:00:00', 'Melbourne Rectangular Stadium'),
('Group Stage G', 'M45', 'R18', 'South Africa', 'Italy', '2023-08-02', '19:00:00', 'Wellington Regional Stadium'),
('Group Stage G', 'M46', 'R02', 'Argentina', 'Sweden', '2023-08-02', '19:00:00', 'Waikato Stadium'),
('Group Stage H', 'M47', 'R19', 'South korea', 'Germany', '2023-08-03', '20:00:00', 'Brisbane Stadium'),
('Group Stage H', 'M48', 'R08', 'Morocco', 'Colombia', '2023-08-03', '18:00:00', 'Perth Rectangular Stadium'),
('Round of 16', 'M49', 'R20', 'Switzerland', 'Spain', '2023-08-05', '17:00:00', 'EDEN PARK'),
('Round of 16', 'M50', 'R21', 'Japan', 'Republic of Ireland', '2023-08-05', '17:00:00', 'Wellington Regional Stadium'),
('Round of 16', 'M51', 'R03', 'Netherlands', 'South Africa', '2023-08-06', '12:00:00', 'Sydney Football Stadium'),
('Round of 16', 'M52', 'R04', 'Sweden', 'USA', '2023-08-06', '19:00:00', 'Melbourne Rectangular Stadium'),
('Round of 16', 'M53', 'R32', 'England', 'Nigeria', '2023-08-07', '17:30:00', 'Brisbane Stadium'),
('Round of 16', 'M54', 'R11', 'Australia', 'Denmark', '2023-08-07', '20:30:00', 'Stadium Australia'),
('Round of 16', 'M55', 'R01', 'Colombia', 'Jamaica', '2023-08-08', '18:00:00', 'Melbourne Rectangular Stadium'),
('Round of 16', 'M56', 'R13', 'France', 'Morocco', '2023-08-08', '20:30:00', 'Hindmarsh Stadium'),
('Quarter Finals', 'M57', 'R14', 'Japan', 'Sweden', '2023-08-11', '19:30:00', 'EDEN PARK'),
('Quarter Finals', 'M58', 'R32', 'Spain', 'Netherlands', '2023-08-11', '13:00:00', 'Wellington Regional Stadium'),
('Quarter Finals', 'M59', 'R07', 'Australia', 'France', '2023-08-12', '17:00:00', 'Brisbane Stadium'),
('Quarter Finals', 'M60', 'R03', 'England', 'Colombia', '2023-08-12', '20:30:00', 'Stadium Australia'),
('Semi Finals', 'M61', 'R12', 'Spain', 'Sweden', '2023-08-15', '20:00:00', 'EDEN PARK'),
('Semi Finals', 'M62', 'R13', 'Australia', 'England', '2023-08-16', '20:00:00', 'Stadium Australia'),
('3rd Place', 'M63', 'R18', 'Sweden', 'Australia', '2023-08-19', '18:00:00', 'Brisbane Stadium'),
('Final', 'M64', 'R05', 'Spain', 'England', '2023-08-20', '20:00:00', 'Stadium Australia');
INSERT IGNORE INTO Officials (Referee_ID, RefereeName) VALUES
('R01', 'Kate Jacewicz'),
('R02', 'Kim Yu-Jeong'),
('R03', 'Oh Hyeon-Jeong'),
('R04', 'Casey Reibelt'),
('R05', 'Yoshimi Yamashita'),
('R06', 'Vincentia Amendome'),
('R07', 'Bouchra Karboubi'),
('R08', 'Akhona Makalima'),
('R09', 'Salima Mukansanga'),
('R10', 'Marianela Araya'),
('R11', 'Marie Soleil'),
('R12', 'Melissa Borjas'),
('R13', 'Katia Garcia'),
('R14', 'Ekaterina Koroleva'),
('R15', 'Myriam Marcotte'),
('R16', 'Tori Marcotte'),
('R17', 'Tori Penso'),
('R18', 'Edina Alves Batista'),
('R19', 'Emikar Calderas Barrera'),
('R20', 'Maria Carvajal'),
('R21', 'Anahi Fernandez'),
('R22', 'Laura Fortunato'),
('R23', 'Anna Marie Keighley'),
('R24', 'Iuliana Demetrescu'),
('R25', 'Maria Sole Ferrieri'),
('R26', 'Cheryl Foster'),
('R27', 'Stephanie Frappart'),
('R28', 'Marta Huerta De Aza'),
('R29', 'Lina Lehtovaara'),
('R30', 'Ivana Martincic'),
('R31', 'Kateryna Monzula'),
('R32', 'Tess Olofsson'),
('R33', 'Esther Staubli'),
('R34', 'Rebecca Welch');
So in the Officials table I have referee_ID as the primary key, and that primary key is referenced into the WomenWCSchedule as a foreign key. Now since it is a foreign key, any repeated values of that will be deemed as duplicate and won't load in the table. I want to know how I can keep the foreign key constraint while allowing repeated values of the referee_ID in the WomenWCSchedule.
What I have tried:
I first tried to remove the INSERT IGNORE but that only gave more errors since I am using key constraints. So the INSERT IGNORE needs to exist. I also know I can remove the foreign key constraint but I do not want to do that. Let's just say that it is NEEDED for this, so I want to keep it and at the same time allow for repeated values of it.
I will provide the code for the tables of them both below:
DROP TABLE IF EXISTS Officials;
CREATE TABLE Officials
(
Referee_ID VARCHAR(5) PRIMARY KEY,
RefereeName VARCHAR(50)
);
DROP TABLE IF EXISTS WomenWCSchedule;
CREATE TABLE WomenWCSchedule
(
Match_ID VARCHAR(5) PRIMARY KEY,
Bracket VARCHAR(20),
Referee_ID VARCHAR(5),
Team_1 VARCHAR(20),
Team_2 VARCHAR(20),
Date DATE,
Time TIME,
Stadium VARCHAR(30),
FOREIGN KEY (Referee_ID) REFERENCES Officials(Referee_ID),
FOREIGN KEY (Stadium) REFERENCES Stadium(Stadium)
);