Create destinations table

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

constraint visit_key primary key (destination, purpose, date_of_visit)

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

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

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);

DIS - Quick Share

SQL Scripts

Analytics

How to create a composite key

Related content