From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 0F0AE2B529 for ; Fri, 29 Mar 2019 14:24:32 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 4Hs50qIqU174 for ; Fri, 29 Mar 2019 14:24:31 -0400 (EDT) Received: from smtpng3.m.smailru.net (smtpng3.m.smailru.net [94.100.177.149]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id B07D52B515 for ; Fri, 29 Mar 2019 14:24:31 -0400 (EDT) From: Nikita Pettik Subject: [tarantool-patches] [PATCH 4/4] sql: disallow creation of FK referencing space without PK Date: Fri, 29 Mar 2019 21:24:24 +0300 Message-Id: <5657d038be3b8b62757568e56bfb391bcceb09ab.1553883575.git.korablev@tarantool.org> In-Reply-To: References: In-Reply-To: References: Sender: tarantool-patches-bounce@freelists.org Errors-to: tarantool-patches-bounce@freelists.org Reply-To: tarantool-patches@freelists.org List-Help: List-Unsubscribe: List-software: Ecartis version 1.0.0 List-Id: tarantool-patches List-Subscribe: List-Owner: List-post: List-Archive: To: tarantool-patches@freelists.org Cc: v.shpilevoy@tarantool.org, Nikita Pettik In our SQL implementation we can omit list of referenced columns in FOREIGN KEY constraint: ... FOREIGN KEY (id) REFERENCES parent; In this case columns composing primary key are used. Hence, it makes it impossible to use this variant of FK statement if space doesn't have primary key. Let's now raise an error in this case. --- src/box/sql/build.c | 7 ++++++- test/sql/foreign-keys.result | 8 ++++++++ test/sql/foreign-keys.test.lua | 6 ++++++ 3 files changed, 20 insertions(+), 1 deletion(-) diff --git a/src/box/sql/build.c b/src/box/sql/build.c index 73ce5b7bf..0a461f69e 100644 --- a/src/box/sql/build.c +++ b/src/box/sql/build.c @@ -1797,7 +1797,12 @@ sql_create_foreign_key(struct Parse *parse_context, struct SrcList *child, * columns of parent table are used as referenced. */ struct index *parent_pk = space_index(parent_space, 0); - assert(parent_pk != NULL); + if (parent_pk == NULL) { + diag_set(ClientError, ER_CREATE_FK_CONSTRAINT, + constraint_name, + "referenced space doesn't feature PRIMARY KEY"); + goto tnt_error; + } if (parent_pk->def->key_def->part_count != child_cols_count) { diag_set(ClientError, ER_CREATE_FK_CONSTRAINT, constraint_name, error_msg); diff --git a/test/sql/foreign-keys.result b/test/sql/foreign-keys.result index 97c3e2442..e0b3eb51f 100644 --- a/test/sql/foreign-keys.result +++ b/test/sql/foreign-keys.result @@ -404,6 +404,14 @@ box.sql.execute("ALTER TABLE t1 ADD CONSTRAINT fk FOREIGN KEY (id) REFERENCES t2 - error: 'Failed to create foreign key constraint ''FK'': foreign key refers to nonexistent field' ... +-- Make sure that if referenced columns (of parent space) are +-- ommitted and parent space doesn't have PK, then error is raised. +-- +box.sql.execute("ALTER TABLE t2 ADD CONSTRAINT fk FOREIGN KEY (id) REFERENCES t1;") +--- +- error: 'Failed to create foreign key constraint ''FK'': referenced space doesn''t + feature PRIMARY KEY' +... t1:drop() --- ... diff --git a/test/sql/foreign-keys.test.lua b/test/sql/foreign-keys.test.lua index 078c10c54..3fb39ea43 100644 --- a/test/sql/foreign-keys.test.lua +++ b/test/sql/foreign-keys.test.lua @@ -181,6 +181,12 @@ box.space.T1:format({'ID'}) t2 = box.schema.create_space("T2") i1 = box.space.T2:create_index('I1') box.sql.execute("ALTER TABLE t1 ADD CONSTRAINT fk FOREIGN KEY (id) REFERENCES t2;") + +-- Make sure that if referenced columns (of parent space) are +-- ommitted and parent space doesn't have PK, then error is raised. +-- +box.sql.execute("ALTER TABLE t2 ADD CONSTRAINT fk FOREIGN KEY (id) REFERENCES t1;") + t1:drop() t2:drop() -- 2.15.1