HELP on IntegrityError: UNIQUE constraint failed on Guided project: Creating Designing a Database

Q&A DQ Courses
#1

Hello on Guided project: Creating Designing a Database topic 6: Adding The Team and Game Tables when i want to make my INSERT to the team Table i got this error.

IntegrityErrorTraceback (most recent call last)
in ()
10
11 “”"
—> 12 run_command(c)

in run_command(c)
17 conn.execute(“PRAGMA foreing_keys = ON;”)
18 conn.isolation_level = None
—> 19 conn.execute(c)
20 def view_tables():
21 “”"

IntegrityError: UNIQUE constraint failed: team.team_id

This is my insert code

c = “”"
INSERT INTO team
SELECT
team_id,
league,
city,
nickname,
franch_id
FROM team_codes

“”"
run_command(c)

When i check on the teams_code table i verify that there is a team with same ID repeated Twice as see below, and as team_id is a primary key i think that it must not be repeated twice, and thats the error but, in the data supplied that team repeated twice is valid, I want to know how could be handled?

I saw the solution file but i did not see how was handle this situation.

team_id league start end city nickname franch_id seq
111 SE1 AL 1969 1969 Seattle Pilots SE1 1
112 MIL AL 1970 1997 Milwaukee Brewers SE1 2
113 MIL NL 1998 0 Milwaukee Brewers SE1 3
1 Like
#2

Hi @guabeff,

The team_id is not the primary key in team_codes table. Therefore, it can have duplicate Team Ids.

CREATE TABLE "team_codes" (
  "team_id" TEXT,
  "league" TEXT,
  "start" INTEGER,
  "end" INTEGER,
  "city" TEXT,
  "nickname" TEXT,
  "franch_id" TEXT,
  "seq" INTEGER
)

You can use INSERT OR IGNORE to avoid the IntegrityError like this:

INSERT OR IGNORE INTO "team"
SELECT
    "team_id",
    "league",
    "city",
    "nickname",
    "franch_id"
FROM "team_codes";

INSERT OR IGNORE skips the row if there is a conflict.

#3

Thanks a lot, I’ve test right now

1 Like
#4

Did you manage to test it. For some reason, when I try ignoring this, it still gives me ‘Foreign Key’ Constraint Failed error. :frowning:

#5

Hi @Sahil,

as @guabeff pointed out, judging from the data the MIL team switched leagues in 1998. Advising not to include the row is a swift solution - but possibly not best practice?

I’d suggest modifying the normalized db schema by DQ slightly - by removing the league_id column from the new team table. Doing so makes creating the team table with distinct values from the team_codes a breeze and the appropriate historical league_id for the team_appearance table would be extracted from the game_log table anyway.

2 Likes
#6

@kamranmk

did you manage to make it run in the end?

#7

@mrbattle yes, I think I was able to address it but I don’t remember to be honest how :frowning:

#8

Hi @mrbattle,

Great suggestion! I will let the content team know about it.

Thanks,
Sahil

#9

Thanks I think is the best aproach for handling that situation

#10

team

“The team_id is not the primary key in team_codes table. Therefore, it can have duplicate Team Ids.”

It looks like team_id is supposed to be the primary key from the schema.

#12

Hi @dramcoyne,

team table is separate from the team_codes table:

Instruction - 6. Adding The Team and Game Tables
Create the team table with columns, primary key, and foreign key as shown in the schema diagram.

  • Select the appropriate type based on the data.
  • Insert the data from the team_codes table.

As you can see in the instruction, we are using team_codes table to create team table. The team_codes table is created using the team_codes.csv file and it contains some duplicate team_id like this:

Best,
Sahil