MySQL: FOREIGN KEYS are easy (kind of)

#MySQL #course #tutorial

00:00:00 intro
00:00:50 CREATE TABLE customers
00:01:17 INSERT INTO customers
00:01:58 CREATE TABLE transactions w/ FOREIGN KEY
00:02:20 Add FOREIGN KEY to a new table
00:03:34 DROP FOREIGN KEY
00:04:07 ADD a named FOREIGN KEY to an existing table
00:05:28 re-populating our transactions table
00:05:45 AUTO_INCREMENT transactions
00:06:05 inserting new rows into transactions
00:07:21 attempting to delete a referenced primary key
00:07:53 conclusion

CREATE TABLE customers (
customer_id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50),
last_name VARCHAR(50)
);
INSERT INTO customers (first_name, last_name)
VALUES (“Fred”, “Fish”),
(“Larry”, “Lobster”),
(“Bubble”, “Bass”);
SELECT * FROM customers;

— Add a named foreign key constraint to a new table
CREATE TABLE transactions (
transaction_id INT PRIMARY KEY AUTO_INCREMENT,
amount DECIMAL(5, 2),
customer_id INT,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
ALTER TABLE transactions
AUTO_INCREMENT = 1000;

— Add a named foreign key constraint to an existing table
ALTER TABLE customers
ADD CONSTRAINT fk_customer_id
FOREIGN KEY (customer_id) REFERENCES customers(customer_id);