No, we've used Reactor with SQL Server using unique identifiers as primary
keys and had no problems (well, we've had some problems but not related to
the fact that we were using unique identifiers as our primary keys). The
separate issue is the SQL Server-specific ROWGUIDCOL, which can be applied
to any column, not just the primary key. The issue here is not that someone
is trying to change the primary key. First, it is perfectly valid to change
the primary key as long as the new key meets the unique constraint. Second,
what if you are not altering the value but are simply inserting the same
value into the table again along with other data? This is what Reactor does
when you save via a DAO and it works just fine, EXCEPT for columns with the
ROWGUIDCOL property enabled. Those columns are not allowed to be part of an
update statement at all.
So the long and the short is: you can use SQL Server as a back end for
Reactor just fine, as long as you do NOT enable the ROWGUIDCOL property. To
make Reactor work with this property will require fairly significant changes
to the way Reactor generates the components for a table, becuase it will
have to leave ROWGUIDCOL columns in the Records (getter and setter) but will
have to intelligently omit those columns from any insert or update
statements. The same solution will also probably allow Reactor to work with
computed columns. It isn't going to be a simple fix though, and since the
ROWGUIDCOL is specific to SQL Server only, it is probably not an extremely
high priority to modify the whole framework just to allow this one
database-specific bit of behavior.
Regarding your specific issue, make sure you have the latest from SVN,
delete ALL the generated Reactor files, purge your template cache in the CF
administrator, and if necessary for a reinitialization of your application
(if you have such a setup) to force Reactor to re-read the XML and
regenerate the CFCs.
Hope that helps,
Brian
On 12/12/06, Marc <[EMAIL PROTECTED]> wrote:
Brian et al,
From the SQL Server BOL:
----------------------------------------------------------------------------
Globally Unique Identifiers
Although the IDENTITY property automates row numbering within one table,
separate tables, each with its own identifier column, can generate the same
values.* *This is because the IDENTITY property is guaranteed to be unique
only for the table on which it is used. If an application must generate an
identifier column that is unique across the entire database, or every
database on every networked computer in the world, use the ROWGUIDCOL
property, the *uniqueidentifier* data type, and the NEWID function.
When you use the ROWGUIDCOL property to define a globally unique
identifier column, consider that:
- A table can have only one ROWGUIDCOL column, and that column must
be defined using the *uniqueidentifier* data type.
- SQL Server does not automatically generate values for the column.
To insert a globally unique value, create a DEFAULT definition on the column
that uses the NEWID function to generate a globally unique value.
- The column can be referenced in a select list by using the
ROWGUIDCOL keyword after the ROWGUIDCOL property is set. This is similar to
the way an IDENTITY column can be referenced using the IDENTITYCOL keyword.
- The OBJECTPROPERTY function can be used to determine if a table
has a ROWGUIDCOL column, and the COLUMNPROPERTY function can be used to
determine the name of the ROWGUIDCOL column.
- Because the ROWGUIDCOL property does not enforce uniqueness, the
UNIQUE constraint should be used to ensure that unique values are inserted
into the ROWGUIDCOL column.
----------------------------------------------------------------------------
Because the above mentions the use of the UNIQUE constraint, I looked that
up as well... I've highlighted relevant text in *bold green*.
UNIQUE Constraints
You can use UNIQUE constraints to ensure that no duplicate values are
entered in specific columns *that do not participate in a primary key*.
Although both a UNIQUE constraint and *a PRIMARY KEY constraint enforce
uniqueness*, use a UNIQUE constraint instead of a PRIMARY KEY constraint
when you want to enforce the uniqueness of:
- A column, or combination of columns, that is not the primary key.
Multiple UNIQUE constraints can be defined on a table, whereas only
one PRIMARY KEY constraint can be defined on a table.
- A column that allows null values.
UNIQUE constraints can be defined on columns that allow null values,
whereas PRIMARY KEY constraints can be defined only on columns that do not
allow null values.
A UNIQUE constraint can also be referenced by a FOREIGN KEY constraint.
----------------------------------------------------------------------------
Finally, my main reason for using uniqueidentifier as the primary key
datatype for all my tables is also covered by the BOL:
Using uniqueidentifier Data
The *uniqueidentifier* data type stores 16-byte binary values that operate
as globally unique identifiers (GUIDs). A GUID is a unique binary number; no
other computer in the world will generate a duplicate of that GUID value.
The main use for a GUID is for assigning an identifier that must be unique
in a network that has many computers at many sites.
----------------------------------------------------------------------------
The above represents the exact qualities I am looking for in a primary key
for all my tables that are to be used with Reactor.
For a simple example, I just created a table using Enterprise Manager, and
did not relate it to any other tables. I created it with a uniqueidentifier
column, and set that column as the primary key. I then turned on
"isRowGUID" for the myKey column, which automatically populated the default
value to newid()... consistent with the BOL instructions above. Finally, I
added a sample column "myFirstColumn" for regular data. As expected, adding
a sample row to this table automatically generated a key in myKey column,
for that row.
Since I set myKey as the primary key (little yellow key appears, in
Enterprise Manager), and since I used a datatype of uniqueidentifier, and
since I set is Is RowGUID (ROWGUIDCOL) and made the default value of for the
column newid(), SQL Server will now generate a GUID for each new row
inserted into the table, and guarantee their uniqueness within the table...
with the added benefit that the GUIDs will likely be unique across all
databases, theoretically across the entire planet. (This has saved my butt
several times, btw.)
So, based on all of the above, here is the sample table schema that was
generated by Enterprise Manager:
----------------------------------------------------------------------------
CREATE TABLE [sampleGUID] (
[myKey] uniqueidentifier ROWGUIDCOL NOT NULL CONSTRAINT
[DF_sampleGUID_myKey] DEFAULT (newid()),
[myFirstColumn] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
CONSTRAINT [PK_sampleGUID] PRIMARY KEY CLUSTERED
(
[myKey]
) ON [PRIMARY]
) ON [PRIMARY]
GO
----------------------------------------------------------------------------
This is a perfectly valid configuration, that I think should work with
Reactor. The more complex (real world) table schema I submitted on
12/10/2006 follows the same exact logic as above, only it had an additional
constraint for it's foreign key, which again, should not cause problems for
Reactor.
So... can anyone confirm that this table schema / primary key datatype
will in fact work with Reactor, and if not, can Reactor be modified to do
so?
Brian, simply put, once should NOT be updating a column that has the
ROWGUIDCOL attribute, as that would mean someone is trying to insert or
change a Primary Key manually... defeating the purpose of using
uniqueidentifier / ROWGUIDCOL / newid(), which lets the DATABASE do what
it's supposed to do - generate and maintain uniqueness of primary keys.
Foreign Keys, on the other hand, one would expect to insert/update. But
then, I I've never set any of my foreign key columns to have the ROWGUIDCOL
property, since they are not the, uh, row's GUID column... the primary key
column is. Therefore, a foreign key column can certainly have a datatype of
uniqueidentifier (but would not possess the ROWGUIDCOL or newid() default
value like the primary key column does), so the table can be related to
another table(s)... but again, still using primary keys of datatype
uniqueidentifier.
Welcoming community input on this... am I the only one trying to use MSSQL
db-generated uniqueidentifiers as my table primary keys, with Reactor?
Marc
------------------------------
*From:* [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] *On
Behalf Of *Brian Kotek
*Sent:* Monday, December 11, 2006 11:26 PM
*To:* [email protected]
*Subject:* Re: [Reactor for CF] Two questions
Note also that the ROWGUIDCOL attribute is probably going to cause
additional problems because SQL Server will throw an error if you try to
update a column with this attribute.
On 12/11/06, Tom Chiverton <[EMAIL PROTECTED]> wrote:
>
> On Monday 11 December 2006 16:26, Marc wrote:
> > Isn't it BOTH? It's a primary key, and it's of datatype
> > "uniqueidentifier"...?
>
> There may be many unique IDs in a row ('candidate primary keys').
> There can be only one actual 'primary key'.
> --
> Tom Chiverton
> Helping to synergistically optimize 24/365 clusters
>
> ****************************************************
>
> This email is sent for and on behalf of Halliwells LLP.
>
> Halliwells LLP is a limited liability partnership registered in England
> and Wales under registered number OC307980 whose registered office address
> is at St James's Court Brown Street Manchester M2 2JF. A list of members is
> available for inspection at the registered office. Any reference to a
> partner in relation to Halliwells LLP means a member of Halliwells LLP.
> Regulated by the Law Society.
>
> CONFIDENTIALITY
>
> This email is intended only for the use of the addressee named above and
> may be confidential or legally privileged. If you are not the addressee you
> must not read it and must not use any information contained in nor copy it
> nor inform any person other than Halliwells LLP or the addressee of its
> existence or contents. If you have received this email in error please
> delete it and notify Halliwells LLP IT Department on 0870 365 8008.
>
> For more information about Halliwells LLP visit www.halliwells.com.
>
>
>
> -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
> -- -- --
> Reactor for ColdFusion Mailing List
> [email protected]
> Archives at: http://www.mail-archive.com/reactor%40doughughes.net/
> -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
> -- -- --
>
>
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- --
Reactor for ColdFusion Mailing List
[email protected]
Archives at: http://www.mail-archive.com/reactor%40doughughes.net/
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- --
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- --
Reactor for ColdFusion Mailing List
[email protected]
Archives at: http://www.mail-archive.com/reactor%40doughughes.net/
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- --
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Reactor for ColdFusion Mailing List
[email protected]
Archives at: http://www.mail-archive.com/reactor%40doughughes.net/
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
