At 10:41 AM 28/09/2004 -0400, you wrote:

>Good day,
>
>I am trying to create a unique constraint in one of my tables but it is
>not working as I expected. I tried both of these approaches:
>
>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)
>);
>
>In both cases, I was able to do the following wthout error:
>
>insert into Vehicle values ('1', 'Foo');
>insert into Vehicle values ('2', 'Foo');

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

>I expected the second statement to cause an error.

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

>What I'm after is a
>way to insert entries in a table with unique names. What is the proper
>way to do this?

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; /* !!!!!! */

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 am using Firebird 1.5 on Windows 2000. The exact version is
>WI-V1.5.1.4481 Firebird 1.5.

Same as here.

./heLen