Leveling up your mind

Primary Keys and Fo...
 
Notifications
Clear all

Primary Keys and Foreign Keys  

 
Sohaib
(@sohaib-ops)
Member Admin

Define a table with a PRIMARY KEY constraint:

  1. CREATE TABLE unique_cats
  2. (
  3. cat_id INT NOT NULL,
  4. name VARCHAR(100),
  5. age INT,
  6. PRIMARY KEY (cat_id)
  7. );

DESC unique_cats; 

Insert some new cats:

  1. INSERT INTO unique_cats(cat_id, name, age) VALUES(1, 'Fred', 23);
  2.  
  3. INSERT INTO unique_cats(cat_id, name, age) VALUES(2, 'Louise', 3);
  4.  
  5. INSERT INTO unique_cats(cat_id, name, age) VALUES(1, 'James', 3);

Notice what happens:

SELECT * FROM unique_cats; 

Adding in AUTO_INCREMENT:

  1. CREATE TABLE unique_cats2 (
  2. cat_id INT NOT NULL AUTO_INCREMENT,
  3. name VARCHAR(100),
  4. age INT,
  5. PRIMARY KEY (cat_id)
  6. );

INSERT a couple new cats:

  1. INSERT INTO unique_cats2(name, age) VALUES('Skippy', 4);
  2. INSERT INTO unique_cats2(name, age) VALUES('Jiff', 3);
  3. INSERT INTO unique_cats2(name, age) VALUES('Jiff', 3);
  4. INSERT INTO unique_cats2(name, age) VALUES('Jiff', 3);
  5. INSERT INTO unique_cats2(name, age) VALUES('Skippy', 4);

Notice the difference:

SELECT * FROM unique_cats2; 

 
This topic was modified 4 months ago by Sohaib
Quote
Posted : 28/04/2020 3:28 am
Sohaib
(@sohaib-ops)
Member Admin

Working With Foreign  Keys:

 

Creating the customers and orders tables

create table customers(
id int auto_increment primary key,
first_name varchar(100),
last_name varchar(100),
email varchar(100)
);

create table orders(
id int auto_increment primary key,
order_date date,
amount decimal(8,2),
customer_id int,
foreign key(customer_id) references customers(id)
);

 

 

Inserting some customers and orders

insert into customers(first_name, last_name, email)
values ('Eric', 'Cartment', 'eric.cartmen@gmail.com'),
('Stan', 'Marsh', 'stan.marsh@gmail.com'),
('Kenny', 'Mcormick', 'kenny.mccormick@gmail.com'),
('Kyle', 'Brovoslki', 'kyle.brovoslki@gmail.com'),
('Butters', 'Stotch', 'butters.stotch@gmail.com'),
('Randy', 'Marsh', 'randy.marsh@gmail.com');

insert into orders (order_date, amount, customer_id)
values ('2013/03/10', 77.56, 1),
('2016/04/12', 23.75, 2),
('2011/01/06', 54.23, 3),
('2019/06/07', 45.44, 4),
('2003/11/04', 65.26, 5),
('2006/12/08', 558.46, 6);

 

 

This INSERT fails because of our fk constraint.  No user with id: 98

  1. INSERT INTO orders (order_date, amount, customer_id)
  2. VALUES ('2016/06/06', 33.67, 98);
ReplyQuote
Posted : 04/07/2020 4:27 am