Helen Borrie wrote:

>>CREATE TABLE Vehicle
>>(
>> Id char(10),
>> Name varchar(32) NOT NULL unique,
>> CONSTRAINT PK_Vehicle PRIMARY KEY (Id)
>>);
>>
>>and
>>
>>CREATE TABLE Vehicle
>>(
>> Id char(10),
>> Name varchar(32) NOT NULL ,
>> CONSTRAINT PK_Vehicle PRIMARY KEY (Id),
>> CONSTRAINT UNQ_VEHICLE_NAME UNIQUE (NAME)
>>);
>>
>
>
> Both of these would fail with the following message:
>
> ISC ERROR CODE:335544351
>
> ISC ERROR MESSAGE:
> unsuccessful metadata update
> Column: ID not defined as NOT NULL - cannot be used in PRIMARY KEY
> constraint definition
>

Typo on my part. The NOT NULL is present.

> Would you please review the statement that you actually used to create this
> table.
>

The above is correct, if you add NOT NULL for the primary key. The is the
exact way it is done:

CREATE DOMAIN UUID AS char(10) not null;
CREATE DOMAIN NAMETYPE AS varchar(32) not null;

CREATE TABLE Vehicle
(
Id UUID,
Name NAMETYPE,
CONSTRAINT PK_Vehicle PRIMARY KEY (Id),
CONSTRAINT UNQ_VEHICLE_NAME UNIQUE (NAME)
);

> CREATE TABLE Vehicle
> (
> Id char(10) NOT NULL,
> Name varchar(32) NOT NULL ,
> CONSTRAINT PK_Vehicle PRIMARY KEY (Id),
> CONSTRAINT UNQ_VEHICLE_NAME UNIQUE (NAME)
> );
>
> COMMIT; /* !!!!!! */
>

Why is this (commit) needed? In my case, I'm actually executing two
different scripts so I believe the commit should be done after the script is
completed, no?

Specifically, I'm using an Ant task to do this (the task loads a script and
executes the bulk statements). I also tried entering one element at a time
using two other applications (DbVisualizer and IBConsole) and I see the same
behaviour.

What's worse, I am also seeing that I can insert entries with identical
primary keys... I am really stumped.

When I do add the commit, I get this error:

BUILD FAILED: D:\dev\eclipse-3.0-fleetmind\fm-tme-data\build.xml:445:
org.firebirdsql.jdbc.FBSQLException: Resource Exception. invalid transaction
handle (expecting explicit transaction start)
Reason: invalid transaction handle (expecting explicit transaction start)

> insert into vehicle values ('1', 'Foo');
> insert in vehicle values ('2', 'Foo');
>
> IISC ERROR CODE:335544665
>
> ISC ERROR MESSAGE:
> violation of PRIMARY or UNIQUE KEY constraint "UNQ_VEHICLE_NAME" on table
> "VEHICLE"
>

I don't see this at all.

>
>>I am using Firebird 1.5 on Windows 2000. The exact version is
>>WI-V1.5.1.4481 Firebird 1.5.
>
>
> Same as here.
>

*Sigh* Is it possible that my database wasn't created properly?

L

P.S. I'm not seeing *any* traffic from the list, even though I'm subscribed.
Does anyone know why?