Using foreign keys in SQL

Question

I want to use 'Postcode' from 'address' table which is a primary key there, as a foreign key in 'customers' table. I did the same for getting 'RentalID' from rentals table as a foreign key in 'customers' table, however, when I try doing the same for 'Postcode' it gives me this error: "Please check for a foreign key constraint on table customers for parent table address".

Please note I am using the online SQLite IDE on khanacademy.org

Here is my code:

CREATE TABLE rentals 
( RentalID INTEGER PRIMARY KEY, CarRegistration TEXT, DateHired TEXT, 
DateReturned TEXT); 

INSERT INTO rentals VALUES (  1, "J986NDX", "12.2.94", "25.8.94"); 
INSERT INTO rentals VALUES (  2, "K788NRT", "1.3.95", "1.4.96"); 
INSERT INTO rentals VALUES (  3, "L346NDS", "2.4.96", ""); 
INSERT INTO rentals VALUES (  4, "J986NDX", "15.9.94", "14.9.95"); 
INSERT INTO rentals VALUES (  5, "M411NGT", "15.9.95", "1.2.96");  

CREATE TABLE customers (CustomerID INTEGER, CustomerSurname TEXT, 
Customerinitial TEXT, Companyname TEXT, Postcode TEXT, RentalID INTEGER , 
PRIMARY KEY(CustomerID, RentalID), FOREIGN KEY (RentalID) REFERENCES 
rentals(RentalID), FOREIGN KEY (Postcode) REFERENCES address(Postcode));

INSERT INTO customers VALUES ( 153, "Nazarali", "N", "MF Plastics", "DB5 
3ER", 1); 
INSERT INTO customers VALUES ( 153, "Nazarali", "N", "MF Plastics", "DB5 
3ER", 2); 
INSERT INTO customers VALUES ( 187, "Brown", "L", "MF Plastics", "DB5 3ER", 
3); INSERT INTO customers VALUES ( 287, "Pinner", "M", "Took Ltd", "DB6 
8YU", 4); INSERT INTO customers VALUES ( 287, "Pinner", "M", "Took Ltd", 
"DB6 8YU", 5);  

CREATE TABLE address (Postcode TEXT PRIMARY KEY, town TEXT);  
INSERT INTO address VALUES ( "DB5 3ER", "Derby"); 
INSERT INTO address VALUES ( "DB6 8YU", "Derby");

How can this be solved?

Using any form of address detail as a primary key is already a bad idea. What happens when people have the same address? That data is not unique — Ryan Gadsdon, Oct 24 '17 at 16:07
@RyanGadsdon actually it is fine because I am only using the postcode as a primary key in the address table to map a town to each postcode as the town will never change. For example, the postcode "DB5 3ER" is always going to belong to "Derby", hence the "DB". I only want to bring it in as a foreign key to the customers table because it needs the customer's address but having town and postcode would not be a 3rd normalised form as town can be derived from the postcode so it would essentially be repeated data, thus I created a separate table for postcode and town. — Shahzeb Manjlai, Oct 24 '17 at 16:11

score 0 · Accepted Answer · answered Oct 24 '17 at 16:19

Basically all I had to do was create the address table and thus create postcode values before I created the customers table because you can't have a foreign key of an attribute that doesn't currently exist, as there would be no constraints. Parent table must be created before customers table.

score 0 · Answer 2 · answered Oct 24 '17 at 16:28

0

foreign key works if the data is already inserted in the foreign table in which that foreign key, is the primary key of that table. try doing subqueries (insert inside the insert statement)

answered Oct 24 '17 at 16:28

Ruby Rae Olaya

29
9

Using foreign keys in SQL

2 Answers2