Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Databases

Posted by rh16
rh16
3,781 Points

why am I getting "Error: foreign key mismatch - "TICKETS" referencing "CONCERTS" when creating TICKETS table ?

Creating and modifying database tables. My code is exactly like the training video. The table is created, but no data inserts. It works in the video. I tried dropping the table an re-creating it without inserting data. That worked, but I when I try to insert the data, I get the same error. Why does it work in the video and not for me?

My code: PRAGMA FOREIGN_KEYS = ON; DROP TABLE IF EXISTS TICKETS; CREATE TABLE IF NOT EXISTS TICKETS ( ID INTEGER PRIMARY KEY AUTOINCREMENT, CONCERT_ID SMALLINT REFERENCES CONCERTS(ID), TICKET_HOLDER_ID INTEGER REFERENCES TICKET_HOLDERS(ID) ); INSERT INTO TICKETS VALUES (NULL, 1, 3); INSERT INTO TICKETS VALUES (NULL, 8, 2); INSERT INTO TICKETS VALUES (NULL, 5, 1); INSERT INTO TICKETS VALUES (NULL, 4, 4); SELECT * FROM TICKETS;

Error: foreign key mismatch - "TICKETS" referencing "CONCERTS" Video is "creating tickets table" https://teamtreehouse.com/library/creating-the-tickets-table

3 Answers

JEFF CHAI
JEFF CHAI
7,564 Points
JEFF CHAI
JEFF CHAI
7,564 Points

The video in CONCERTS table has the primary key in ID column is because teacher start his program from the previous video and continue working to this video. But if you start a new SQL playground, the CONCERTS is missing the primary key it maybe human error when the teacher miss key in the primary key.

In realistic, it is encouraged you to put primary key in the table and to create a realational database. Imagine that the foreign key allow you to target a column which is not a primary key, and the CONCERTS table have 2 same id '5' in the column. The Sql will confuse and getting error, is because they have two '5' in the CONCERTS table but is actually only need one row. Beside, putting primary key and foreign key can increasing performance and easy for you when doing coding.

Sorry for my broken english, hope you can understand it.

rh16
rh16
3,781 Points

I do understand. Thank you so much. I did restart the Playground, because that is normally what you do. You have been a great help! I marked you for 2 best answers. Thank you again.

JEFF CHAI
JEFF CHAI
7,564 Points
JEFF CHAI
JEFF CHAI
7,564 Points

The English language error message for "foreign key mismatch" in the launch SQL Playground is based on the parent key of a foreign key constraint is the primary key of the parent table. If they are not the primary key, then the parent key columns must be collectively subject to a UNIQUE constraint or have a UNIQUE index.

You can run the below code to drop the CONCERTS table and re-create it with a primary key on id.

CREATE TEMPORARY TABLE CONCERTS_backup(ID INTEGER PRIMARY KEY AUTOINCREMENT,DATE DATE,CITY VARCHAR(255),STATE VARCHAR(255),VENUE VARCHAR(255)); INSERT INTO CONCERTS_backup SELECT ID,DATE,CITY,STATE,VENUE FROM CONCERTS; DROP TABLE IF EXISTS CONCERTS; CREATE TABLE CONCERTS(ID INTEGER PRIMARY KEY AUTOINCREMENT,DATE DATE,CITY VARCHAR(255),STATE VARCHAR(255),VENUE VARCHAR(255)); INSERT INTO CONCERTS SELECT ID,DATE,CITY,STATE,VENUE FROM CONCERTS_backup; DROP TABLE IF EXISTS CONCERTS_backup;

DROP TABLE IF EXISTS TICKET_HOLDERS; CREATE TABLE IF NOT EXISTS TICKET_HOLDERS ( ID INTEGER PRIMARY KEY AUTOINCREMENT, FIRST_NAME VARCHAR(255), LAST_NAME VARCHAR(255), EMAIL VARCHAR(255) ); INSERT INTO TICKET_HOLDERS VALUES (NULL, 'Jessica', 'Rodgers', 'jessrodg23@hotmail.com'); INSERT INTO TICKET_HOLDERS VALUES (NULL, 'Mary', 'O’Hara', 'mohara@gmail.com'); INSERT INTO TICKET_HOLDERS VALUES (NULL, 'Dominik', 'Smith', 'dsmith1120@aol.com'); INSERT INTO TICKET_HOLDERS VALUES (NULL, 'Marcella', 'Childs', 'mcchilds@gmail.com'); SELECT * FROM TICKET_HOLDERS;

rh16
rh16
3,781 Points

Why does the video tell you to create the CONCERTS table without a Primary key? I find this confusing because all 3 tables in the video would have primary keys. You wouldn't have that in reality would you? As you can tell, this is new to me.

Thank you

internettraveller
internettraveller
4,028 Points
internettraveller
internettraveller
4,028 Points

Move on! If you need some extra sample code, ask me!