Cockroach: Foreign Key Constriant Not Enforced

[xudongzheng]

I170812 11:26:56.300392 1 util/log/clog.go:1011 [config] binary: CockroachDB CCL v1.0.2 (linux amd64, built 2017/06/15 12:58:59, go1.8.3)

https://pastebin.com/kndb2ULZ

CREATE TABLE IF NOT EXISTS one (name BYTES PRIMARY KEY);
INSERT INTO one VALUES ('hello');

CREATE TABLE IF NOT EXISTS two (
        name BYTES REFERENCES one (name) NOT NULL,
        main BOOL NOT NULL,
        UNIQUE (name, main)
);
INSERT INTO two VALUES ('hello', TRUE);

DELETE FROM one;
SELECT * FROM two;

The DELETE FROM statement should not succeed given the constraint.

[jordanlewis]

Thanks for the report. This anomaly seems to be caused by the presence of the UNIQUE constraint within the CREATE TABLE. As a workaround, if you create a unique index on the table afterward instead, the foreign key constraint will be properly respected.

[jordanlewis]

SHOW CREATE TABLE reveals the issue:

root@:26257/test> show create table two;
+-------+-------------------------------------------------------------------------------------+
| Table |                                     CreateTable                                     |
+-------+-------------------------------------------------------------------------------------+
| two   | CREATE TABLE two (␤                                                                 |
|       |     "name" BYTES NOT NULL,␤                                                         |
|       |     main BOOL NOT NULL,␤                                                            |
|       |     CONSTRAINT fk_name_ref_one FOREIGN KEY ("name", main) REFERENCES one ("name"),␤ |
|       |     UNIQUE INDEX two_name_main_key ("name" ASC, main ASC),␤                         |
|       |     FAMILY "primary" ("name", main, rowid)␤                                         |
|       | )                                                                                   |
+-------+-------------------------------------------------------------------------------------+
(1 row)

The foreign key was created against two input columns, but references a single foreign column. This is a malformed constraint and points to an issue in CREATE TABLE.

[jordanlewis]

@xudongzheng I've prepared a fix in #17638. The problem was caused by a bug in foreign key constraint checks against referencing tables whose foreign key was a prefix of a previously existing index. (In this example, the foreign key (name) is a prefix of the index (name, main)).

When this happens, we re-use the existing index rather than create a new one to enforce the FK constraint. However, we were creating a key into that existing index whose length was not truncated to match the length of the foreign key - instead, it was the length of the full index. This was the root cause of the bug.

Sorry for the trouble and many thanks for this helpful bug report.