>>> You're right about keeping the rules and regulations of data
>>> integrity in the database rather than the application. Good
>>> working practice.
> Indeed, and one that I wish all my vendors here would follow! :-(
>
>>> Maybe Firebird is different from Oracle in that I have
>>> successfully created my ID column as a primary key without
>>> the non-null constraint. So, it's possible even if not
>>> perhaps advisable.
> Possibly not! In Oracle if I do such a thing then Oracle silently adds
> NOT NULL to the column(s) I have used in the PK.
>
> On Firebird (2.1) I can do this:
>
> SQL> create table norman (
> CON> id integer primary key,
> CON> b varchar(100));
> SQL> commit;
>
> So far so good, but ....
>
> SQL> show table norman;
> ID INTEGER Not Null
> B VARCHAR(100) Nullable
> CONSTRAINT INTEG_2: Primary key (ID)
>
> So, Firebird has quietly added NOT NULL to my ID column. Trying again,
> another way:

Kinda makes sense, doesn't it ;-)

Why not fetch the ID first (from a generator) and use it in Insert?

With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!

Database questions? Check the forum:
http://www.databasedevelopmentforum.com

> SQL> create table norman (
> CON> id integer,
> CON> b varchar(100));
>
> SQL> alter table norman2
> CON> add constraint pk_norman2
> CON> primary key (id);
>
> Statement failed, SQLCODE = -607
> unsuccessful metadata update
> -Column: ID not defined as NOT NULL - cannot be used in PRIMARY KEY
> constraint definition.
>
> So it looks like you might have a NOT NULL constraint (back) on your ID
> columns after all? (In the latter example, Oracle would have NOT NULLed
> the ID column silently and allowed me to make the PK constraint work.)
>
>
> Cheers,
> Norman.
>
> Norman Dunbar
> Contract Senior Oracle DBA
> CIS Engineering Services
> Internal : 7 28 2051
> External : 0113 231 2051
>
>
> Information in this message may be confidential and may be legally
> privileged. If you have received this message by mistake, please notify
> the sender immediately, delete it and do not copy it to anyone else. We
> have checked this email and its attachments for viruses. But you should
> still check any attachment before opening it. We may have to make this
> message and any reply to it public if asked to under the Freedom of
> Information Act, Data Protection Act or for litigation. Email messages
> and attachments sent to or from any Environment Agency address may also be
> accessed by someone other than the sender or recipient, for business
> purposes. If we have sent you information and you wish to use it please
> read our terms and conditions which you can get by calling us on 08708 506
> 506. Find out more about the Environment Agency at
> www.environment-agency.gov.uk
>
> Information in this message may be confidential and may be legally
> privileged. If you have received this message by mistake, please notify
> the sender immediately, delete it and do not copy it to anyone else.
>
> We have checked this email and its attachments for viruses. But you should
> still check any attachment before opening it.
> We may have to make this message and any reply to it public if asked to
> under the Freedom of Information Act, Data Protection Act or for
> litigation. Email messages and attachments sent to or from any
> Environment Agency address may also be accessed by someone other than the
> sender or recipient, for business purposes.
>
> If we have sent you information and you wish to use it please read our
> terms and conditions which you can get by calling us on 08708 506 506.
> Find out more about the Environment Agency at
> www.environment-agency.gov.uk
>
>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Yahoo! Groups Links
>
>
>