首页 > 解决方案 > FOREIGN KEY ... REFERENCE 没有错误,但只是从数据库中空列

问题描述

自动增量似乎工作得很好,但是看起来主键或外键(也许?)我不知道,因为我没有收到任何错误消息。这是我的数据库代码:

DROP TABLE IF EXISTS `Album`;
CREATE TABLE `Album` (
  `id` int NOT NULL AUTO_INCREMENT,
  `album_name` varchar(100) NOT NULL,
  `album_thumbnail` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `Album` (`album_name`,`album_thumbnail`)
VALUES
('est','/media/test.jpg'),
('consequatur','/media/test.jpg'),
('cumque','/media/test.jpg'),
('sunt','/media/test.jpg'),
('maiores','/media/test.jpg'),
('enim','/media/test.jpg'),
('voluptatibus','/media/test.jpg'),
('officiis','/media/test.jpg'),
('sapiente','/media/test.jpg'),
('itaque','/media/test.jpg');



DROP TABLE IF EXISTS `Artist`;
CREATE TABLE `Artist` (
  `id` int NOT NULL AUTO_INCREMENT,
  `artist_name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `Artist` (`artist_name`)
VALUES
('Dr. Liza Gleason'),
('Luz Feest'),
('Nigel Beier'),
('Dan Mosciski DDS'),
('Reta Altenwerth'),
('Felipe Runolfsson'),
('Pamela Kessler'),
('Mr. Chester Von PhD'),
('Nayeli Rosenbaum IV'),
('Haven Roberts');



DROP TABLE IF EXISTS `Track`;
CREATE TABLE `Track` (
  `id` int NOT NULL AUTO_INCREMENT,
  `track_name` varchar(100) NOT NULL,
  `time` time DEFAULT NULL,
  `likes` int(9) unsigned NOT NULL,
  `track_link` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `Track` (`track_name`,`time`,`likes`,`track_link`)
VALUES
('facere','07:29:26','0','http://heaney.com/'),
('placeat','13:31:39','0','http://mclaughlinschowalter.com/'),
('numquam','11:51:06','0','http://www.reicherthowell.com/'),
('accusantium','13:58:47','0','http://www.white.net/'),
('molestiae','14:22:06','0','http://www.leuschke.info/'),
('dolores','05:33:34','0','http://www.schuppe.com/'),
('natus','18:14:07','0','http://klocko.com/'),
('optio','01:44:57','0','http://hillshoeger.biz/'),
('laudantium','23:41:02','0','http://hartmannmiller.org/'),
('illo','05:18:46','0','http://www.sanford.com/');




DROP TABLE IF EXISTS `Album_Track`;
CREATE TABLE `Album_Track` (
  `album_id` int NOT NULL,
  `track_id` int NOT NULL,
  FOREIGN KEY (`album_id`) REFERENCES `Album`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  FOREIGN KEY (`track_id`) REFERENCES `Track`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



DROP TABLE IF EXISTS `Artist_Album`;
CREATE TABLE `Artist_Album` (
  `artist_id` int NOT NULL,
  `album_id` int NOT NULL,
  FOREIGN KEY (`artist_id`) REFERENCES `Artist` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  FOREIGN KEY (`album_id`) REFERENCES `Album` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

我正在获取除 Album_Track 和 Artist_Album 之外的所有表值。他们保持空白,没有任何错误。我真的很感激任何帮助。

标签: mysqlforeign-keysprimary-key

解决方案


好吧,表格Album_Track中的列album_idtrack_id没有任何默认值。如果您不指定它们,则INSERT查询将失败。

您需要指定这些列的值,因为 MySQL 无法猜测它们。它怎么可能猜到?

必须有错误代码或错误消息。出于某种原因,您没有看到它们。

正确的插入方式是,例如:

INSERT INTO Album_Track (album_id, track_id, artist_name)
  VALUES (1, 1, 'Dr. Liza Gleason');

这将正确插入一行。


推荐阅读