I've tried using two different methods to setup a GUID as a primary key
using the ElevateDB Manager.  Below are a few questions.

Method 1
=======
When I converted a database from DBISAM that had a table with a GUID primary
key, it was configured such that in the Alter Table window:

   - The "Generated" box was not selected.
   - The "Nullable" box was not selected.
   - The default expression was "CURRENT_GUID".

With this method, a GUID appears to be created when a record is posted from
a dataset in Delphi 7.  If I refresh the dataset after posting, the GUID
value is preserved.  The behavior is pretty much as I would expect.

Method 2
=======
Without the example from the DBISAM import, if I were creating a table from
scratch, my intuition (a scary thing) would tell me to check the "Generated"
box, unselect the "Nullable" box, and choose the "Generate as Expression"
radio button with an expression of "CURRENT_GUID".  I wound up using these
settings in my trial:

   - The "Generated" box *IS* selected.
   - The "Nullable" box *IS* selected (Note: The Alter Table window would
not allow me to create a generated GUID field with the nullable box
unselected).
   - The "Generated as Expression" expression was "CURRENT_GUID".

With this method, a GUID appears to be created when a record is created for
editing.  If I refresh the dataset after posting, the GUID value is *NOT*
preserved.  Instead, a different GUID value appears each time I hit refresh
.... not particularly what I want for my primary key.  

I'm interested in using this method (if I can stabilize the GUID value)
because it would be handy sometimes to have the GUID value of the primary
key before it gets posted.

=======

So, now for my questions:

1.  Why can't I deselect the Nullable box for my generated GUID in Method 2?
It seems to me that a primary key should not be nullable.
2 . Why would refreshing a dataset cause a new GUID to appear each time
(with Method 2)?  How can I prevent this?
3.  What is the preferred method to create a GUID primary key?  Using a
default expression or using a generated GUID with an expression?  What is
the actual difference between a default expression and a generated value
from an expression?

Thanks in advance for any help.

Forgot to mention, I'm using EDB v1 Build 7 of the Beta with Delphi 7 Pro.

Greg,

<< Without the example from the DBISAM import, if I were creating a table
from scratch, my intuition (a scary thing) would tell me to check the
"Generated" box, unselect the "Nullable" box, and choose the "Generate as
Expression" radio button with an expression of "CURRENT_GUID".  I wound up
using these settings in my trial: >>

You're misunderstanding what the generated columns are.  In EDB generated
columns are just that - generated.  Meaning that they don't actually exist
anywhere and are calculated as needed.  Thus, using any of the CURRENT_*
functions in a generated expression is a bad idea because it will simply
result in a different value each time.

<< 1.  Why can't I deselect the Nullable box for my generated GUID in Method
2? >>

I tried this here and it worked fine.  Perhaps you could give me the steps
that you're using to define the column and I'll see if I can recreate the
issue.

<< It seems to me that a primary key should not be nullable. >>

This is enforced automatically by EDB anyways.

<< 2 . Why would refreshing a dataset cause a new GUID to appear each time
(with Method 2)?  How can I prevent this? >>

See above.

<< 3.  What is the preferred method to create a GUID primary key?  Using a
default expression or using a generated GUID with an expression? >>

Default expression.

<< What is the actual difference between a default expression and a
generated value from an expression? >>

See above.

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi, Tim:

Thanks for the explanation of generated columns ... I was not thinking of
them being calculated fields.  Now it all makes sense to me.

> << 1.  Why can't I deselect the Nullable box for my generated GUID in
> Method 2? >>
>
> I tried this here and it worked fine.  Perhaps you could give me the steps
> that you're using to define the column and I'll see if I can recreate the
> issue.

The steps that I can take to reproduce this are (all in the ElevateDB
Manager -- I've tried it in Builds 7 and 9 with the same response):

1.  Create a new database.
2.  In the new database, create a new table and assign it a name.
3.  In the Create New Table dialog, do the following:
   a.  Add a column and name it "ID";
   b.  Set the column type to "GUID"*
   c.  Unselect the nullable box;
   d.  Select the Generated box;
   e.  Hit the Generation tab;
   f.  Enter "CURRENT_GUID" in the "Generate as Expression" box (now I
realise this doesn't make much sense, but this is what I did); and
   g.  Click "Add Column".
4.  Click "OK" at the bottom of the form.

After clicking "OK", I get the following error message:

"ElevateDB Error #700 An error was found in the statement at line 3 and
column 63 (Expected BETWEEN, LIKE, IN but instead found NULL)"

Next, after clearing the error message, and with the ID field selected, I:

1.  Select the nullable box;
2.  Click "Alter Column"; and
3.  Click "OK" at the bottom of the form.

At this point, the table is created, error free.  So, my conclusion was that
a field cannot be both non-nullable and generated.  Is this correct?

*I also tried doing this with a field type of VarChar and a generated
expression of "FRED" and it appears to have the same response ... rejecting
a generated field with non-nullable selected upon attempting to create the
table.  So, it probably is not GUID-specific.

====
Having said all of this, now that I realize that a generated field is
calculated on-the-fly, I can't think of a reason why I would need to make a
generated field non-nullable.  So, I'm left wondering: Is the rejection of
the non-nullable, generated field upon table creation by design?  If so,
perhaps a check in the ElevateDB Manager when adding a field for this
combination would be appropriate, just in case some other schlep like me
tries to do this.  

Greg,

<< The steps that I can take to reproduce this are (all in the ElevateDB
Manager -- I've tried it in Builds 7 and 9 with the same response): >>

Ahh, okay.  I thought you meant that the actual dialog wasn't allowing you
to uncheck the Nullable check box.  What you're running into is an issue
with the ordering of the clauses in the column definitions.  Basically, you
can't use NOT NULL with a generated column that consists of a single
function or column because the parser thinks that the NOT NULL is part of
the generated expression.  It's an unfortunate quirk in the way column
definitions work.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim Young

<<You're misunderstanding what the generated columns are.  In EDB generated
columns are just that - generated.  Meaning that they don't actually exist
anywhere and are calculated as needed.  Thus, using any of the CURRENT_*
functions in a generated expression is a bad idea because it will simply
result in a different value each time.>>

So, I will not be able to create an index on a generated column, that's correct?

Mauricio Campana Nonino
Nonino Software

Mauricio,

<< So, I will not be able to create an index on a generated column, that's
correct? >>

No, that's not what I said.  What I said is that you shouldn't use any of
the CURRENT* functions in a generated column (or any function that returns a
different result each time given the same input parameters).  As long as you
don't use any of these functions in a generated column, then indexing the
generated column will work just fine.

--
Tim Young
Elevate Software
www.elevatesoft.com