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

Question

我正在准备考试，我决定做一些我编造的示例任务，但我被困在一个特定的任务上。关系。我已经在 SQL 上苦苦挣扎了 3-4 个小时，但进展甚微。我有 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`))

Answer 1

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);

这是一对一的关系。

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