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 trialrh16
3,781 Pointswhy 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
7,564 PointsThe 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
3,781 PointsI 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
7,564 PointsThe 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
3,781 PointsWhy 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
4,028 PointsMove on! If you need some extra sample code, ask me!
Tristan Magpantay
Full Stack JavaScript Techdegree Student 14,032 PointsTristan Magpantay
Full Stack JavaScript Techdegree Student 14,032 Pointswhich video is it related to?