MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE project(
id int PRIMARY KEY);
CREATE TABLE publicationsource ( 
id int(11) NOT NULL AUTO_INCREMENT, 
name varchar(128) COLLATE utf8_bin DEFAULT NULL, 
type varchar(16) COLLATE utf8_bin DEFAULT NULL, 
PRIMARY KEY (id), 
KEY UK_publicationsource_type_name (type,name) ) 
ENGINE=InnoDB AUTO_INCREMENT=10921959 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
CREATE TABLE reference ( 
id int(11) NOT NULL AUTO_INCREMENT,
end_page int(11) DEFAULT NULL, 
number int(11) DEFAULT NULL,
published datetime DEFAULT NULL, 
reference_id varchar(64) COLLATE utf8_bin DEFAULT NULL, 
start_page int(11) DEFAULT NULL,
volume int(11) DEFAULT NULL, project int(11) NOT NULL,
publication_source int(11) NOT NULL, seed tinyint(1) NOT NULL DEFAULT '0', 
accession_number varchar(128) COLLATE utf8_bin DEFAULT NULL, 
database_name varchar(1024) COLLATE utf8_bin DEFAULT NULL, 
has_full_text tinyint(1) NOT NULL DEFAULT '0',
external_id varchar(255) COLLATE utf8_bin DEFAULT NULL,
model_seed varchar(128) COLLATE utf8_bin DEFAULT 'NONE', 
url varchar(2083) COLLATE utf8_bin DEFAULT NULL, 
doi varchar(1024) COLLATE utf8_bin DEFAULT NULL, 
issue varchar(64) COLLATE utf8_bin DEFAULT NULL, 
issn varchar(1024) COLLATE utf8_bin DEFAULT NULL,
call_number varchar(2083) COLLATE utf8_bin DEFAULT NULL,
work_type varchar(1024) COLLATE utf8_bin DEFAULT NULL, 
notes varchar(10000) COLLATE utf8_bin DEFAULT NULL, 
custom_tags blob,
PRIMARY KEY (id), 
KEY FK_reference_project (project), 
KEY FK_reference_publication_source (publication_source),
CONSTRAINT FK_reference_project FOREIGN KEY (project) REFERENCES project (id),
CONSTRAINT FK_reference_publication_source FOREIGN KEY (publication_source) REFERENCES publicationsource (id) ) 
ENGINE=InnoDB AUTO_INCREMENT=10512575 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
INSERT INTO project (id)
SELECT DISTINCT 1 
WHERE NOT EXISTS
(SELECT id
FROM project
WHERE id = 1)
INSERT INTO publicationsource
 (id, name, type)
SELECT DISTINCT 
  1, 
  'publication name 1',
  'type name 1'
WHERE NOT EXISTS
(SELECT id 
FROM publicationsource
WHERE id = 1);
INSERT INTO reference 
(id, publication_source, project, issue, doi, url, issn) VALUES
(9433356, 1, 1, '4', '', 'https://app.dimensions.ai/details/publication/pub.1077608314', '' ) 
ON DUPLICATE KEY UPDATE    
issue ='4', doi ='', url ='https://app.dimensions.ai/details/publication/pub.1077608314' ,issn='';
SELECT * FROM project;


id


1


SELECT * FROM publicationsource;


id
name
type


1
publication name 1
type name 1


SELECT * FROM reference;


id
end_page
number
published
reference_id
start_page
volume
project
publication_source
seed
accession_number
database_name
has_full_text
external_id
model_seed
url
doi
issue
issn
call_number
work_type
notes
custom_tags


9433356
null
null
null
null
null
null
1
1
0
null
null
0
null
NONE
https://app.dimensions.ai/details/publication/pub.1077608314

4

null
null
null