Re: Cannot add unique constraint with fk column

[Martin Sköld]

Actually there seems to be a problem with the ADD CONSTRAINT (will create a bug). This is equivalent to what you want to do and more efficient:
mysql> CREATE TABLE networks (
-> tenant_id VARCHAR(255),
-> id VARCHAR(36) NOT NULL,
-> name VARCHAR(255),
-> status VARCHAR(16),
-> admin_state_up BOOL,
-> shared BOOL,
-> PRIMARY KEY (id)
-> );
Query OK, 0 rows affected (0,68 sec)
mysql> CREATE TABLE ports (
-> tenant_id VARCHAR(255),
-> id VARCHAR(36) NOT NULL,
-> name VARCHAR(255),
-> network_id VARCHAR(36) NOT NULL,
-> mac_address VARCHAR(32) NOT NULL,
-> admin_state_up BOOL NOT NULL,
-> status VARCHAR(16) NOT NULL,
-> device_id VARCHAR(255) NOT NULL,
-> device_owner VARCHAR(255) NOT NULL,
-> PRIMARY KEY (id)
-> );
Query OK, 0 rows affected (0,90 sec)
mysql> CREATE UNIQUE INDEX uniq_ports0network_id0mac_address on ports (network_id, mac_address);
Query OK, 0 rows affected (0,82 sec)
mysql> ALTER TABLE ports ADD FOREIGN KEY(network_id) REFERENCES networks (id);
Query OK, 0 rows affected (0,50 sec)
Records: 0 Duplicates: 0 Warnings: 0