· 8 years ago · Nov 18, 2017, 03:26 AM
1-- BEGIN list 1, TABLES/LAYERS.SQL
2-- -----------------------------------------------------
3-- Table SUDOCUBE.LAYERS
4-- -----------------------------------------------------
5DROP TABLE IF EXISTS LAYERS;
6
7CREATE TABLE LAYERS (
8 puzzleID INT NULL,
9 layerID INT NOT NULL,
10 created TIMESTAMP NOT NULL,
11 PRIMARY KEY(puzzleID, layerID),
12 CONSTRAINT fk_LAYERS_PUZZLES
13 FOREIGN KEY (puzzleID)
14 REFERENCES PUZZLES (puzzleID)
15 ON DELETE CASCADE
16 ON UPDATE NO ACTION)
17ENGINE = InnoDB;
18
19SHOW WARNINGS;
20------------------
21-- END listing 1
22
23---------------- output from the mysql command line interface --------
24-- 1) RUN SQL file that creates the "PUZZLES" table.
25mysql> \. TABLES/PUZZLES.SQL
26Query OK, 0 rows affected, 1 warning (0.00 sec)
27
28Query OK, 0 rows affected (0.66 sec)
29
30Empty set (0.00 sec)
31
32mysql> SHOW TABLES;
33+--------------------+
34| Tables_in_SUDOCUBE |
35+--------------------+
36| PUZZLES |
37+--------------------+
381 row in set (0.00 sec)
39
40-- 2) RUNNING THE ABOVE TABLES/LAYERS.SQL table results in the following error, which seems to say to me
41-- that either my puzzleID, or my layerID are not being defined as "NOT NULL", which they clearly are
42-- (in the above listing.)
43
44mysql> \. TABLES/LAYERS.SQL
45Query OK, 0 rows affected, 1 warning (0.00 sec)
46
47ERROR 1171 (42000): All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead
48+-------+------+--------------------------------------------------------------------------------------------+
49| Level | Code | Message |
50+-------+------+--------------------------------------------------------------------------------------------+
51| Error | 1171 | All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead |
52+-------+------+--------------------------------------------------------------------------------------------+
531 row in set (0.00 sec)