unique index versus primary key

[willy]

Hi,

The following works in 1.4.200, but gives the following error in
1.4.201   :
Any ideas ?

Kind regards

Willy

 Constraint "PRIMARY KEY | UNIQUE (TYPES)" not found; SQL statement:
CREATE  TABLE FCOUNTS(     ID IDENTITY primary key ,     NR VARCHAR,    
NAME VARCHAR,     TYPES VARCHAR,     TAG VARCHAR, Foreign key(types)
references ftypes(types),     Foreign key(tag) references ftags(tag) )

Code :

create  table ftypes(
    ID IDENTITY primary key ,
    types VARCHAR
);

create unique index ftype_idx on ftypes(types);

create  table ftags(
    ID IDENTITY primary key ,
    tag VARCHAR
);
create unique index ftag_idx on fintags(tag);


CREATE  TABLE FCOUNTS(
    ID IDENTITY primary key ,
    NR VARCHAR,
    NAME VARCHAR,
    TYPES VARCHAR,
    TAG VARCHAR,
    Foreign key(types) references ftypes(types),
    Foreign key(tag) references ftags(tag)
);

[Andrei Tokar]

I wonder if that's because of create unique index ftag_idx on fintags(tag);
contains invalid table name?

[Gmail]

Hi,

sorry wrong cut/paste

Still the same error :

Constraint "PRIMARY KEY | UNIQUE (TYPES)" not found; SQL statement:

CREATE  TABLE FCOUNTS(     ID IDENTITY primary key ,     NR VARCHAR,     NAME VARCHAR,     TYPES VARCHAR,     TAG VARCHAR,     Foreign key(types) references ftypes(types),     Foreign key(tag) references ftags(tag) ) [90057-200]

create  table ftypes(
    ID IDENTITY primary key ,
    types VARCHAR
);

create unique index ftype_idx on ftypes(types);

create  table ftags(
    ID IDENTITY primary key ,
    tag VARCHAR
);

create unique index ftag_idx on ftags(tag);

CREATE  TABLE FCOUNTS(
    ID IDENTITY primary key ,
    NR VARCHAR,
    NAME VARCHAR,
    TYPES VARCHAR,
    TAG VARCHAR,
    Foreign key(types) references ftypes(types),
    Foreign key(tag) references ftags(tag)

);

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/3d57f368-0b7a-4960-81ec-4cdc82ab4b3an%40googlegroups.com.

[Andrei Tokar]

Please note that it is complaining about missing constraint. Indeed, you create a unique index, but not a unique constraint, which is required for referential integrity constraint.

If instead you define table as

create table ftypes(ID IDENTITY primary key , types VARCHAR UNIQUE );

it will generate a constraint (and index shouldl be created automatically), and everything just works.

[Gmail]

Hi,

This is what I need,
Where can I find a document whith changes between 1.4.200 and 1.4.201 ?

Thanks a lot

Willy

To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/d130a84a-5e58-480c-9faa-c25000d6e089n%40googlegroups.com.