[MonetDB-users] question about composite uniqueness constraints
Hi, I have a question about creating composite uniqueness constraints in monetdb. Say I create the following table called test_property with 3 columns: subject, p1, p2. I want uniqueness contraints for each pair of (subject, p1) and (subject, p2). I try to do this with the following: sql>create table test_property(subject integer, p1 integer, p2 integer, unique(subject, p1), unique(subject, p2)); sql>\d test_property CREATE TABLE "sys"."test_property" ( "subject" int, "p1" int, "p2" int, CONSTRAINT "test_property_subject_p2_unique" UNIQUE ("subject"), CONSTRAINT "test_property_subject_p1_unique" UNIQUE ("subject", "p2", "p1") ); But somehow, I end up with a constraint on "subject" by itself and one on ("subject", p2, p1). Is this a bug in monetdb or am I missing something? I ran the exact same commands in postgres and I got my expected results (2 uniqueness contraints on (subject, p1) and (subject, p2)). I tried also creating the table first, then adding columns and the uniqueness constraint after using ALTER TABLE, but I got the same results. Does anyone see something I'm doing wrong? Thanks Andrew
On 14-05-2008 17:43:24 -0700, Drew Sky wrote:
Hi,
I have a question about creating composite uniqueness constraints in monetdb. Say I create the following table called test_property with 3 columns: subject, p1, p2. I want uniqueness contraints for each pair of (subject, p1) and (subject, p2). I try to do this with the following:
sql>create table test_property(subject integer, p1 integer, p2 integer, unique(subject, p1), unique(subject, p2));
sql>\d test_property CREATE TABLE "sys"."test_property" ( "subject" int, "p1" int, "p2" int, CONSTRAINT "test_property_subject_p2_unique" UNIQUE ("subject"), CONSTRAINT "test_property_subject_p1_unique" UNIQUE ("subject", "p2", "p1") );
But somehow, I end up with a constraint on "subject" by itself and one on ("subject", p2, p1). Is this a bug in monetdb or am I missing something?
(pegasus:~) fabian% JdbcClient -dAnton Welcome to the MonetDB interactive JDBC terminal! Database: MonetDB v5.5.0, 'Anton' Driver: MonetDB Native Driver v1.7 (Canephora_p2 20080319 based on MCL v1.2) Type \q to quit, \h for a list of available commands auto commit mode: on monetdb-> create table test_property(subject integer, p1 integer, p2 integer, monetdb=( unique(subject, p1), unique(subject, p2)); Operation successful monetdb-> \d test_property CREATE TABLE "sys"."test_property" ( "subject" int, "p1" int, "p2" int, CONSTRAINT "test_property_subject_p1_unique" UNIQUE ("subject", "p1"), CONSTRAINT "test_property_subject_p2_unique" UNIQUE ("subject", "p2") ); monetdb-> You are experiencing a bug in the dumpmode of mclient. If you look carefully, you can see that what it generates is a syntax error, as it inserts a closing parenthesis after the first "subject", and then adds '"p1")'. JdbcClient dumps this correctly, so the constraint is stored correctly in the database, mclient just can't show you that ;)
Fabian Groffen wrote:
On 14-05-2008 17:43:24 -0700, Drew Sky wrote:
Hi,
I have a question about creating composite uniqueness constraints in monetdb. Say I create the following table called test_property with 3 columns: subject, p1, p2. I want uniqueness contraints for each pair of (subject, p1) and (subject, p2). I try to do this with the following:
sql>create table test_property(subject integer, p1 integer, p2 integer, unique(subject, p1), unique(subject, p2));
sql>\d test_property CREATE TABLE "sys"."test_property" ( "subject" int, "p1" int, "p2" int, CONSTRAINT "test_property_subject_p2_unique" UNIQUE ("subject"), CONSTRAINT "test_property_subject_p1_unique" UNIQUE ("subject", "p2", "p1") );
But somehow, I end up with a constraint on "subject" by itself and one on ("subject", p2, p1). Is this a bug in monetdb or am I missing something?
(pegasus:~) fabian% JdbcClient -dAnton Welcome to the MonetDB interactive JDBC terminal! Database: MonetDB v5.5.0, 'Anton' Driver: MonetDB Native Driver v1.7 (Canephora_p2 20080319 based on MCL v1.2) Type \q to quit, \h for a list of available commands auto commit mode: on monetdb-> create table test_property(subject integer, p1 integer, p2 integer, monetdb=( unique(subject, p1), unique(subject, p2)); Operation successful
monetdb-> \d test_property CREATE TABLE "sys"."test_property" ( "subject" int, "p1" int, "p2" int, CONSTRAINT "test_property_subject_p1_unique" UNIQUE ("subject", "p1"), CONSTRAINT "test_property_subject_p2_unique" UNIQUE ("subject", "p2") ); monetdb->
You are experiencing a bug in the dumpmode of mclient. If you look carefully, you can see that what it generates is a syntax error, as it inserts a closing parenthesis after the first "subject", and then adds '"p1")'.
JdbcClient dumps this correctly, so the constraint is stored correctly in the database, mclient just can't show you that ;)
Please report this as a bug in the bug tracker. -- Sjoerd Mullender
participants (3)
-
Drew Sky -
Fabian Groffen -
Sjoerd Mullender