Wrong/confusing error message when trying to add a FK to a TEMP TABLE

[monetdb-team]

Date: 2014-09-05 18:55:21 +0200
From: Martin van Dinther <<martin.van.dinther>>
To: SQL devs <>
Version: 11.17.21 (Jan2014-SP3)
CC: @njnes

Last updated: 2014-10-31 14:14:04 +0100

Comment 20142

Date: 2014-09-05 18:55:21 +0200
From: Martin van Dinther <<martin.van.dinther>>

User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:31.0) Gecko/20100101 Firefox/31.0
Build Identifier:

ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references PKTABLE(ptest1);
returns error msg: ALTER TABLE: no such table 'fktable'
while the table with that name exists.

A better error msg would be:
ALTER TABLE ADD FOREIGN KEY: not allowed for TEMP tables
if FKs are not supported on TEMP tables, else it should accept it and no error msg given.

Reproducible: Always

Steps to Reproduce:

1. Start MonetDB 5 server v11.17.21 "Jan2014-SP3"
2. Start mclient
3. run SQL:
   CREATE TABLE PKTABLE (ptest1 int PRIMARY KEY);
   CREATE TEMP TABLE FKTABLE (ftest1 int);
   ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references PKTABLE(ptest1);

Actual Results:

Welcome to mclient, the MonetDB/SQL interactive terminal (unreleased)
Database: MonetDB v11.17.21 (Jan2014-SP3), 'demo'
Type \q to quit, ? for a list of available commands
auto commit mode: on
sql>CREATE TABLE PKTABLE (ptest1 int PRIMARY KEY);
operation successful (27.444ms)
sql>CREATE TEMP TABLE FKTABLE (ftest1 int);
operation successful (3.029ms)
sql>ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references PKTABLE(ptest1);
ALTER TABLE: no such table 'fktable'
sql>SELECT * FROM FKTABLE;
+--------+
| ftest1 |
+========+
+--------+
0 tuples (0.936ms)
sql>
sql>DROP TABLE FKTABLE;
operation successful (1.883ms)
sql>CREATE TABLE FKTABLE (ftest1 int);
operation successful (2.852ms)
sql>ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references PKTABLE(ptest1);
operation successful (8.129ms)
sql>

Expected Results:

if FKs are NOT supported on TEMP tables a better error msg should be given.

When both tables are TEMP tables, should Fks be allowed?

CREATE TEMP TABLE PKTABLE (ptest1 int PRIMARY KEY);
CREATE TEMP TABLE FKTABLE (ftest1 int);
ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references PKTABLE(ptest1);
Currently it reports the same error: ALTER TABLE: no such table 'fktable'

Comment 20187

Date: 2014-09-21 21:29:30 +0200
From: MonetDB Mercurial Repository <>

Changeset b8bc6d228e06 made by Niels Nes niels@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=b8bc6d228e06

Changeset description:

fix for bug #3565

Comment 20188

Date: 2014-09-21 21:30:20 +0200
From: @njnes

fixed alters on temp tables are not supported

Comment 20353

Date: 2014-10-31 14:14:04 +0100
From: @sjoerdmullender

Oct2014 has been released.