At 10:18 PM 31/12/2010, you wrote:

>--- In firebird-support@yahoogroups.com, "Woody" <woody-tmw@...> wrote:
>> Nice to see you giving FB a go. The syntax is a little off for Firebird
>> when creating primary key constraints. Try something like this:
>>
>> CREATE TABLE HL_Oids(
>> OID OidKey NOT NULL CONSTRAINT OID_Key PRIMARY KEY
>> );
>
>Thanks, Woody. These subtle syntax differences between SQL servers are a major pain in the butt <g>. I'm more familiar with Oracle.
>
>Anyway, your suggestion does not resolve the problem.
>
>OidKey is a domain:
>
>CREATE DOMAIN OidKey AS CHAR(22);
>
>Forgot to include that, sorry. If I try to create the table with
>
>CREATE TABLE HL_Oids (
> OID OidKey NOT NULL CONSTRAINT PK_OIDs PRIMARY KEY
>);
>
>this is again accepted without errors (I would expect invalid syntax to fail at this stage already) but fails on the subsequent commit with
>
>*** IBPP::SQLException ***
>Context: Transaction::Commit
>
>SQL Message : -607
>This operation is not defined for system tables.
>
>Engine Code : 335544351
>Engine Message :
>unsuccessful metadata update
>cannot create index PK_OIDS
>
>If I replace the OidKey type with CHAR(22) in the CREATE TABLE statement nothing changes, this still fails with the same error on commit.

c:\Programs\Firebird\Firebird_2_0\bin>isql test -user sysdba -password mas
terkey
Database: test, User: sysdba
SQL> CREATE DOMAIN OidKey AS CHAR(22);
SQL> commit;
SQL> CREATE TABLE HL_Oids (
CON> OID OidKey NOT NULL CONSTRAINT PK_OIDs PRIMARY KEY
CON> );
SQL> commit;
SQL> show table hl_oids;
OID (OIDKEY) CHAR(22) Not Null
CONSTRAINT PK_OIDS:
Primary key (OID)
SQL> show indices hl_oids;
PK_OIDS UNIQUE INDEX ON HL_OIDS(OID)
SQL>

No exceptions and it's all there.

So - ?????

Nevertheless, I don't consider this a tidy way to do table DDL. You save a couple of keystrokes and have a messy DDL script. I prefer:

create table HL_Oids (
OID OidKey NOT NULL,
<other columns>,
constraint PK_OIDS PRIMARY KEY(OID)
);
commit;

Some people prefer:
create table HL_Oids (
OID OidKey NOT NULL,
<other columns>);
commit;
alter table HL_Oids
add constraint PK_OIDS PRIMARY KEY(OID);
commit;

>So i'm still stuck...

Can't tell what you are using to submit the DDL requests, other than that IBPP is involved. Were you aware that the statement terminator (default: semicolon) is not valid outside isql? it is used in isql (including scripts processed by isql) to mark the end of a statement request....note in my example how isql responds to a string that has no terminator...it accepts it and waits for CONtinuation input. The reasons become obvious once you start running isql scripts that include PSQL.

You'll need to make your app code strip out the terminator before you pass the statement. I don't know whether Flamerobin does this automatically...

./heLen