Colleague: “Can you have foreign keys referencing other databases?”

Me: “Erm… I don’t know, but it I’ll find out.”

I’ve always thought tables with these relationships should exist in the same database so I’ve never attempted this. But I don’t like not knowing things so I set to find out. The answer, as it often seems to be, is “it depends!”

Q1: Does MySQL support cross-database foreign keys?

First lets create some test databases and tables. The table called test1 will reference a table called test2 in another database.

CREATE DATABASE test1;
CREATE DATABASE test2;

USE test1;

CREATE TABLE test1
(
	idnum INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
	fk_id INT NOT NULL
) ENGINE = INNODB;

USE test2;

CREATE TABLE test2
(
	idnum INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
	fk_id INT NOT NULL,
	FOREIGN KEY fk_idnum (fk_id) REFERENCES test1.test1(idnum)
) ENGINE = INNODB;

OK, MySQL seemed to parse that without complaint. Now lets try to insert a record into table test2.

USE test2;

INSERT INTO test2
(
	fk_id
)
VALUES
(
	1
);

We get an error which shows the constraint is working as you would expect.

# Error Code : 1452
# Cannot add or update a child row: a foreign key constraint fails (`test2`.`test2`, CONSTRAINT `test2_ibfk_1` FOREIGN KEY (`fk_id`) REFERENCES `test1`.`test1` (`idnum`)

Insert some data into both tables now.

USE test1;

INSERT INTO test1
(
	fk_id
)
VALUES
(
	1
);

USE test2;

INSERT INTO test2
(
	fk_id
)
VALUES
(
	1
);

SELECT *
FROM test2.test2
UNION ALL
SELECT *
FROM test1.test1;

What happens if we try to drop the test1 database?

DROP DATABASE test1;

# Error Code : 1217
# Cannot delete or update a parent row: a foreign key constraint fail

To drop both the databases we need to drop them in the below sequence

# Clean up
DROP DATABASE test2;
DROP DATABASE test1;

A1: Yes! MySQL does support cross-database foreign key references. *

* Assuming the storage engine in use supports foreign keys.

Q2: Does SQL Server support cross-database foreign keys?

CREATE DATABASE test1;
CREATE DATABASE test2;

USE test1;

CREATE TABLE test1
(
	idnum INT NOT NULL PRIMARY KEY IDENTITY(1,1),
	fk_id INT NOT NULL
);

USE test2;

CREATE TABLE test2
(
	idnum INT NOT NULL PRIMARY KEY IDENTITY(1,1),
	fk_id INT NOT NULL,
	FOREIGN KEY (fk_id) REFERENCES test1.dbo.test1(idnum)
);

Msg 1763, Level 16, State 0, Line 1
Cross-database foreign key references are not supported. Foreign key 'test1.dbo.test1'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

A2: No! SQL Server does not support cross-database foreign key references.

Chris Rock once said “You can drive a car with your feet if you want to, that don’t make it a good ******* idea!”. That’s kind of how I feel about cross-database foreign keys. Just because you can, doesn’t mean you should! I’d be inclined to have no foreign key relationship if we’re spitting up data for performance reasons. Otherwise I’d put both tables in the same database where they logically belong.