mysql - 在MySQL中的表之间创建一对一和一对多关系

我正在为考试做准备，因此我决定做一些示例性的工作，但我只专注于一个特定的任务。关系。我一直在3-4个小时的时间里紧紧抓住SQL墙，但进展甚微。我有2个表，要在其中创建一对一和一对多关系。

CREATE TABLE article (
    price INT(30) NOT NULL,
    published_on DATE NOT NULL
);

CREATE TABLE tag(
    descption VARCHAR(30) NOT NULL,
    priority INT(30) NOT NULL
);

CREATE TABLE category(
    date_created_on DATE NOT NULL,
    name VARCHAR(30) NOT NULL
);

INSERT INTO article VALUES (10.0, "2001-01-01", 0);
INSERT INTO article VALUES (20.0, "1992-05-08", 0);

INSERT INTO tag VALUES ("wtf", 1, 1);
INSERT INTO tag VALUES ("is this", 2, 2);

ALTER TABLE article ADD article_id INT(30) NOT NULL;
ALTER TABLE article ADD PRIMARY KEY(article_id);
ALTER TABLE article MODIFY article_id AUTO_INCREMENT;

ALTER TABLE tag ADD tag_id INT(30) NOT NULL;
ALTER TABLE tag ADD PRIMARY KEY(tag_id);
ALTER TABLE tag MODIFY tag_id AUTO_INCREMENT;

ALTER TABLE tag ADD FOREIGN KEY (tag_id) REFERENCES (article_id);

我要使一篇文章与文章一对一，然后使文章与类别一对多。我无法一对一甚至尝试一对多。您能否给我一些有关如何实现它以及如何建立关系的见解？

这是来自数据库内部的信息-

mysql> select * from Tag;
+-------------+----------+--------+
| description | priority | tag_id |
+-------------+----------+--------+
| wtf         |        1 |      1 |
| is this     |        2 |      2 |
+-------------+----------+--------+

mysql> select * from Article;
+-------+--------------+------------+
| price | published_on | article_id |
+-------+--------------+------------+
|    10 | 2001-01-01   |          7 |
|    20 | 1992-05-08   |          8 |
+-------+--------------+------------+

mysql> describe Article;
+--------------+---------------+------+-----+---------+----------------+
| Field        | Type          | Null | Key | Default | Extra          |
+--------------+---------------+------+-----+---------+----------------+
| price        | decimal(30,0) | YES  |     | NULL    |                |
| published_on | date          | YES  |     | NULL    |                |
| article_id   | int(30)       | NO   | PRI | NULL    | auto_increment |
+--------------+---------------+------+-----+---------+----------------+

mysql> describe Tag;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| description | varchar(30) | NO   |     | NULL    |       |
| priority    | int(30)     | NO   |     | NULL    |       |
| tag_id      | int(30)     | NO   | PRI | NULL    |       |
+-------------+-------------+------+-----+---------+-------+    mysql> select * from Tag;
+-------------+----------+--------+
| description | priority | tag_id |
+-------------+----------+--------+
| wtf         |        1 |      1 |
| is this     |        2 |      2 |
+-------------+----------+--------+

mysql> select * from Article;
+-------+--------------+------------+
| price | published_on | article_id |
+-------+--------------+------------+
|    10 | 2001-01-01   |          7 |
|    20 | 1992-05-08   |          8 |
+-------+--------------+------------+

mysql> describe Article;
+--------------+---------------+------+-----+---------+----------------+
| Field        | Type          | Null | Key | Default | Extra          |
+--------------+---------------+------+-----+---------+----------------+
| price        | decimal(30,0) | YES  |     | NULL    |                |
| published_on | date          | YES  |     | NULL    |                |
| article_id   | int(30)       | NO   | PRI | NULL    | auto_increment |
+--------------+---------------+------+-----+---------+----------------+

mysql> describe Tag;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| description | varchar(30) | NO   |     | NULL    |       |
| priority    | int(30)     | NO   |     | NULL    |       |
| tag_id      | int(30)     | NO   | PRI | NULL    |       |
+-------------+-------------+------+-----+---------+-------+

这是我创建外键时遇到的错误：

mysql> ALTER TABLE Tag ADD FOREIGN KEY(tag_id) REFERENCES Article(article_id);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`exam_example`.`#sql-2836_1`,
CONSTRAINT `#sql-2836_1_ibfk_1` FOREIGN KEY (`tag_id`) REFERENCES `Article` (`article_id`))

最佳答案

CREATE TABLE article (
    price INT(30) NOT NULL,
    published_on DATE NOT NULL,
    article_id INT(30) NOT NULL,
    CONSTRAINT pk_article_id PRIMARY KEY(article_id)
);

CREATE TABLE tag(
    descption VARCHAR(30) NOT NULL,
    priority INT(30) NOT NULL,
    tag_id INT(30) NOT NULL,
    CONSTRAINT pk_tag_article PRIMARY KEY(tag_id),
    CONSTRAINT fk_tag_article FOREIGN KEY(tag_id) REFERENCES article(article_id)
);

INSERT INTO article VALUES (10.0, "2001-01-01", 0);
INSERT INTO article VALUES (20.0, "1992-05-08", 1);

INSERT INTO tag VALUES ("wtf", 1, 1);
INSERT INTO tag VALUES ("is this", 2, 2);

这是一对一的关系。

我在这里说的是标签的主键与文章中的相同。

关于mysql - 在MySQL中的表之间创建一对一和一对多关系，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/22791479/