mysql - 添加外键约束的问题

Question

我使用 v.5.6.18 在 MySQL 中构建了下表。最初尝试创建带有外键约束的表，但没有成功。所以现在我只是想做出改变。到目前为止，一切都是这样的:

Create table Campaign_Overviews(
    -> Campaign varchar(45),
    -> Petition varchar(45),
    -> DateStarted date,
    -> PRIMARY KEY(Campaign,Petition,DateStarted);
ERROR 1046 (3D000): No database selected
mysql> USE ReclaimEmailAcq;
Database changed
mysql> Create table Campaign_Overviews(
    -> Campaign varchar(45),
    -> Petition varchar(45),
    -> DateStarted date,
    -> PRIMARY KEY(Campaign,Petition,DateStarted);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '' at
line 5
mysql> Create table Campaign_Overviews(
    -> Campaign varchar(45) NOT NULL,
    -> Petition varchar(45) NOT NULL,
    -> DateStarted date NOT NULL,
    -> PRIMARY KEY(Campaign, Petition, DateStarted)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TABLE Petitions(
    -> Petition varchar(45) NOT NULL,
    -> Medium varchar(45),
    -> AdID varchar(45) NOT NULL,
    -> URL varchar(45),
    -> Cost float,
    -> PRIMARY KEY(AdID)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE Email_Addresses(
    -> Email varchar(45) NOT NULL,
    -> Date_Acquired DATETIME,
    -> Petition varchar(45),
    -> AdID varchar(45) NOT NULL,
    -> PRIMARY KEY (Email, AdID)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> ALTER TABLE Petitions
    -> ADD FOREIGN KEY (Petition)
    -> REFERENCES Campaign_Overviews (Petition);
ERROR 1215 (HY000): Cannot add foreign key constraint
mysql> show tables;
+---------------------------+
| Tables_in_reclaimemailacq |
+---------------------------+
| campaign_overviews        |
| email_addresses           |
| petitions                 |
+---------------------------+
3 rows in set (0.00 sec)

mysql> DESCRIBE campaign_overviews;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| Campaign    | varchar(45) | NO   | PRI | NULL    |       |
| Petition    | varchar(45) | NO   | PRI | NULL    |       |
| DateStarted | date        | NO   | PRI | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> DESCRIBE email_addresses;
+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| Email         | varchar(45) | NO   | PRI | NULL    |       |
| Date_Acquired | datetime    | YES  |     | NULL    |       |
| Petition      | varchar(45) | YES  |     | NULL    |       |
| AdID          | varchar(45) | NO   | PRI | NULL    |       |
+---------------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

DESCRIBE petitions;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| Petition | varchar(45) | NO   |     | NULL    |       |
| Medium   | varchar(45) | YES  |     | NULL    |       |
| AdID     | varchar(45) | NO   | PRI | NULL    |       |
| URL      | varchar(45) | YES  |     | NULL    |       |
| Cost     | float       | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

Answer 1

您的外键位于错误的表中。外键应该存在于引用表(多个)中，并且应该引用被引用的(一个)表。应该是:

ALTER TABLE Campaign_Overviews
ADD FOREIGN KEY (Petition)
REFERENCES Petitions (Petition);

此外，您还需要申请表中该字段的索引(键)。

ALTER TABLE Petition
ADD KEY Petition (Petition);

供引用: http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html