How do I keep repeated foreign key values without having to get rid of the foreign key constraint?

Question

0.00/5 (No votes)

See more:

, +

SQL

-- Values for the schedule of all matches that took place

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');
	
-- Values for the referees who were assigned 

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:

SQL

-- Drop table if it exists and then create a list of referees and their ID
DROP TABLE IF EXISTS Officials;
CREATE TABLE Officials
(
    Referee_ID VARCHAR(5) PRIMARY KEY, -- Unique identifier for the referee
    RefereeName VARCHAR(50) -- Name of the referee
);

-- Drop table if it exists and then create the full 2023 WC schedule
DROP TABLE IF EXISTS WomenWCSchedule; 
CREATE TABLE WomenWCSchedule
(
    Match_ID VARCHAR(5) PRIMARY KEY, -- Unique identifier for the match
    Bracket VARCHAR(20), -- Tournament bracket
    Referee_ID VARCHAR(5), -- Foreign key referencing Officials table
    Team_1 VARCHAR(20), -- Team 1 participating in the match
    Team_2 VARCHAR(20), -- Team 2 participating in the match
    Date DATE, -- Date of the match
    Time TIME, -- Time of the match
    Stadium VARCHAR(30), -- Foreign key referencing Stadium table
    FOREIGN KEY (Referee_ID) REFERENCES Officials(Referee_ID),
    FOREIGN KEY (Stadium) REFERENCES Stadium(Stadium)
);

Posted 22-Nov-23 6:27am

DosNecro

Updated 22-Nov-23 8:43am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

k5054 · Answer 1 · 2023-11-22T09:39:00

Solution 1

As presented, there are no issues with your schema, other than no Stadium table definition. In any case, a column with a Foreign Key constraint may have duplicate values. Check the schema for the WomensWCSchedule table and make sure you do not have a UNIQUE constraint on it.

Posted 22-Nov-23 9:39am

k5054