· 8 years ago · Mar 26, 2018, 03:10 PM
1DROP DATABASE IF EXISTS library;
2
3CREATE DATABASE library;
4
5USE library;
6
7CREATE TABLE AUTHOR (
8 NAME VARCHAR(50) NOT NULL,
9 COUNTRY VARCHAR(50) NOT NULL,
10 TITLE VARCHAR(50) NOT NULL,
11 PRIMARY KEY (NAME,TITLE)
12) ENGINE = INNODB;
13
14CREATE TABLE BOOK (
15 TITLE VARCHAR(50) NOT NULL,
16 ISBN CHAR(10) NOT NULL,
17 PUBLISHER VARCHAR(50) NOT NULL,
18 SUBJECT VARCHAR(50) NOT NULL,
19 PRIMARY KEY (ISBN)
20) ENGINE = INNODB;
21
22CREATE TABLE WRITES (
23 NAME VARCHAR(50) NOT NULL,
24 TITLE VARCHAR(50) NOT NULL,
25 ISBN CHAR(10) NOT NULL,
26 PRIMARY KEY (NAME,ISBN)
27) ENGINE = INNODB;
28
29ALTER TABLE WRITES ADD CONSTRAINT FK_BOOK_WRITES
30 FOREIGN KEY(ISBN) REFERENCES BOOK(ISBN)
31 ON DELETE RESTRICT ON UPDATE RESTRICT;
32
33ALTER TABLE WRITES ADD CONSTRAINT FK_AUTHOR_WRITES
34 FOREIGN KEY(NAME) REFERENCES AUTHOR(NAME)
35 ON DELETE RESTRICT ON UPDATE RESTRICT;
36
37INSERT INTO AUTHOR (NAME,COUNTRY,TITLE) VALUES("Dorothy King","England","Springtime Gardening");
38INSERT INTO AUTHOR (NAME,COUNTRY,TITLE) VALUES("Jon Nkoma","Kenya","Birds of Africa");
39INSERT INTO AUTHOR (NAME,COUNTRY,TITLE) VALUES("Won Lau","China","Early Tangs Paintings");
40INSERT INTO AUTHOR (NAME,COUNTRY,TITLE) VALUES("Bert Kovalsco","United States","Baskets for Today");
41INSERT INTO AUTHOR (NAME,COUNTRY,TITLE) VALUES("Tom Quercos","Mexico","Mayan Art");
42INSERT INTO AUTHOR (NAME,COUNTRY,TITLE) VALUES("Jimmy Chan","China","Early Tangs Paintings");
43INSERT INTO AUTHOR (NAME,COUNTRY,TITLE) VALUES("Dorothy King","England","Autumn Annuals");
44INSERT INTO AUTHOR (NAME,COUNTRY,TITLE) VALUES("Jane East","United States","Springtime Gardening");
45
46INSERT INTO BOOK (TITLE,ISBN,PUBLISHER,SUBJECT) VALUES("Springtime Gardening","0816354219","Harding","Nature");
47INSERT INTO BOOK (TITLE,ISBN,PUBLISHER,SUBJECT) VALUES("Early Tangs Paintings","036487547X","Ballman","Art");
48INSERT INTO BOOK (TITLE,ISBN,PUBLISHER,SUBJECT) VALUES("Birds of Africa","0115012141","Loraine","Nature");
49INSERT INTO BOOK (TITLE,ISBN,PUBLISHER,SUBJECT) VALUES("Springtime Gardening","0560001428","Swift-Key","Nature");
50INSERT INTO BOOK (TITLE,ISBN,PUBLISHER,SUBJECT) VALUES("Baskets for Today","0816537054","Harding","Art");
51INSERT INTO BOOK (TITLE,ISBN,PUBLISHER,SUBJECT) VALUES("Autumn Annuals","0816885060","Harding","Nature");
52
53INSERT INTO WRITES (NAME,TITLE,ISBN) VALUES("Jimmy Chan","Early Tangs Paintings","036487547X");
54INSERT INTO WRITES (NAME,TITLE,ISBN) VALUES("Dorothy King","Autumn Annuals","0816885060");
55INSERT INTO WRITES (NAME,TITLE,ISBN) VALUES("Jane East","Springtime Gardening",0560001428);
56INSERT INTO WRITES (NAME,TITLE,ISBN) VALUES("Bert Kovalsco","Baskets for Today","0816537054");
57INSERT INTO WRITES (NAME,TITLE,ISBN) VALUES("Won Lau","Early Tangs Paintings","036487547X");
58INSERT INTO WRITES (NAME,TITLE,ISBN) VALUES("Jon Nkoma","Birds of Africa","0115012141");
59INSERT INTO WRITES (NAME,TITLE,ISBN) VALUES("Dorothy King","Springtime Gardening","0816354219");
60
61ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`library`.`WRITES`, CONSTRAINT `FK_BOOK_WRITES` FOREIGN KEY (`ISBN`) REFERENCES `BOOK` (`ISBN`))