...
| Code Block | ||
|---|---|---|
| ||
CREATE TABLE destinations
(
destinationI_id int primary key,
country varchar(40),
location varchar(40),
traveller int,
foreign key (traveller) references travellers(traveller_id)
); |
How to create a composite key
You will to define the column using this syntax, anything in bold is keyword, the other parts your field names
| Info |
|---|
constraint |
| Code Block | ||
|---|---|---|
| ||
CREATE TABLE visits
(
date_of_visit date,
destination int,
purpose int,
foreign key (destination) references destinations(destination_id),
foreign key (purpose) references purpose(id),
constraint visit_key primary key (destination, purpose, date_of_visit)
); |
Partially completed script
| Code Block | ||
|---|---|---|
| ||
CREATE DATABASE places_visited;
CREATE TABLE travellers
(
traveller_id int primary key auto_increment,
first_name varchar(40),
last_name varchar(40)
);
INSERT into travellers
(traveller_id, first_name, last_name)
values
(1, "Sel", "RTR"),
(2, "Ligia", "Who"),
(3, "Ariana", "What");
CREATE TABLE destinations
(
destination_id int primary key,
country varchar(40),
location varchar(40),
traveller int,
foreign key (traveller) references travellers(traveller_id)
);
insert into destinations
(destination_id, traveller, country, location)
values
(1, 2, "Brasil", "Buzios"),
(2, 1, "South Africa", "Jo/Burgh"),
(3, 1, "Nepal", ""),
(4, 2, "Brasil", "Rio De Janeiro"),
(5, 1, "South Africa", "Mpumalanga"),
(6, 1, "Jamaica", "Flogaman"); |
Complete script
| Code Block |
|---|
create database hikers_paradise;
use hikers_paradise;
CREATE TABLE travellers
(
traveller_id int primary key auto_increment,
first_name varchar(40),
last_name varchar(40)
);
INSERT into travellers
(traveller_id, first_name, last_name)
values
(1, "Sel", "RTR"),
(2, "Ligia", "Who"),
(3, "Ariana", "What");
CREATE TABLE destinations
(
destination_id int primary key,
country varchar(40),
location varchar(40),
traveller int,
foreign key (traveller) references travellers(traveller_id)
);
insert into destinations
(destination_id, traveller, country, location)
values
(1, 2, "Brasil", "Buzios"),
(2, 1, "South Africa", "Jo/Burgh"),
(3, 1, "Nepal", ""),
(4, 2, "Brasil", "Rio De Janeiro"),
(5, 1, "South Africa", "Mpumalanga"),
(6, 1, "Jamaica", "Flogaman");
CREATE TABLE purpose
(
id int primary key auto_increment,
reason varchar(40)
);
INSERT into purpose
(reason)
values
("Sunshine"),
("Mountains"),
("Work"),
("Lifestyle"),
("Safari"),
("Family");
CREATE TABLE visits
(
date_of_visit date,
destination int,
purpose int,
foreign key (destination) references destinations(destination_id),
foreign key (purpose) references purpose(id),
constraint visit_key primary key (destination, purpose, date_of_visit)
);
INSERT into visits
(date_of_visit, destination, purpose)
VALUES
("2015-03-15", 3, 2),
("2007-09-21", 2, 3),
("2009-03-03", 5, 5),
("2011-03-11", 5, 5),
("2017-05-08", 1, 1),
("2020-02-04", 1, 6),
("2018-08-23", 6, 6); |