From: Nikita Pettik <korablev@tarantool.org> To: tarantool-patches@freelists.org Cc: v.shpilevoy@tarantool.org, Nikita Pettik <korablev@tarantool.org> Subject: [tarantool-patches] [PATCH 4/4] sql: disallow creation of FK referencing space without PK Date: Fri, 29 Mar 2019 21:24:24 +0300 [thread overview] Message-ID: <5657d038be3b8b62757568e56bfb391bcceb09ab.1553883575.git.korablev@tarantool.org> (raw) In-Reply-To: <cover.1553883575.git.korablev@tarantool.org> In-Reply-To: <cover.1553883575.git.korablev@tarantool.org> 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
prev parent reply other threads:[~2019-03-29 18:24 UTC|newest] Thread overview: 6+ messages / expand[flat|nested] mbox.gz Atom feed top 2019-03-29 18:24 [tarantool-patches] [PATCH 0/4] Fixes in SQL involving no-pk or no-format spaces Nikita Pettik 2019-03-29 18:24 ` [tarantool-patches] [PATCH 1/4] Drop foreign keys before indexes in space:drop() Nikita Pettik 2019-03-29 18:24 ` [tarantool-patches] [PATCH 2/4] Fix creation of FK constraint in case of no child's PK Nikita Pettik 2019-04-01 13:41 ` [tarantool-patches] " Vladislav Shpilevoy 2019-03-29 18:24 ` [tarantool-patches] [PATCH 3/4] sql: disallow creation of index on space without format Nikita Pettik 2019-03-29 18:24 ` Nikita Pettik [this message]
Reply instructions: You may reply publicly to this message via plain-text email using any one of the following methods: * Save the following mbox file, import it into your mail client, and reply-to-all from there: mbox Avoid top-posting and favor interleaved quoting: https://en.wikipedia.org/wiki/Posting_style#Interleaved_style * Reply using the --to, --cc, and --in-reply-to switches of git-send-email(1): git send-email \ --in-reply-to=5657d038be3b8b62757568e56bfb391bcceb09ab.1553883575.git.korablev@tarantool.org \ --to=korablev@tarantool.org \ --cc=tarantool-patches@freelists.org \ --cc=v.shpilevoy@tarantool.org \ --subject='Re: [tarantool-patches] [PATCH 4/4] sql: disallow creation of FK referencing space without PK' \ /path/to/YOUR_REPLY https://kernel.org/pub/software/scm/git/docs/git-send-email.html * If your mail client supports setting the In-Reply-To header via mailto: links, try the mailto: link
This is a public inbox, see mirroring instructions for how to clone and mirror all data and code used for this inbox