Again, this isn't actually true. You can set the default value for the column to be NewID() without also turning on the ROWGUIDCOL attribute.
For your second point, consider a situation where the table has a standard int primary key that is an identity field (it is automatically generated) but they also want to enable replication across server. One would create a separate ID column with the ROWGUIDCOL attribute turned on to be used in replication, but keep the standard int primary key. The two are not necessarily the same thing. On the last point, you're misunderstanding me. Reactor isn't trying to INSERT the same row with the same primary key twice. It is UPDATING the same row. But when Reactor does the update via a DAO, it updates ALL the columns. Reactor has no way of knowing which columns have changed so it assumes that they all have potentially changed, and creates the update statement accordingly. Some of the columns may contain the same values that are already in the table (like the primary key) but they are still included in the update statement. For example, if you create a Record, use a setter to change 1 column value, and then save the Record, the actual SQL that is run includes ALL the columns, it just so happens that in this case only 1 value being inserted is actually different than what is already in the table. Again, any column with ROWGUIDCOL turned on cannot be part of an INSERT or UPDATE statement. To attempt to do so will throw a SQL Server error. Reactor does work fine with database-generated uniqueidentifier fields. What it won't work fine with are columns with the ROWGUIDCOL attribute enabled. These are not the same thing. Hopefully this makes more sense now. On 12/12/06, Marc <[EMAIL PROTECTED]> wrote:
But, the only way to use uniqueidentifier as a primary key datatype, and have SQL Server ** GENERATE ** the primary key when inserting a row, is to use ROWGUIDCOL on that primary key column. For reactor to not support this is similar to saying "do not use autonumbering on your int-based primary key fields"... only in thise case, it's a uniqueidentifier. instead of an int with seed and increment settings. SQL Server can generate these GUIDs, but only if ROWGUIDCOL is set, which enables the default field where one can insert newID(). While ROWGUIDCOL "could" be applied to any column, what good would it be to apply it to anything other than the primary key column? Especially when reactor essentially (rightfully) requires that ALL tables have a primary key... And why would Reactor attempt to insert a row with the same primary key TWICE? I thought the idea was to have reactor know whether to insert or update. Inserting a row with the same primary key as one that's in there is certainly a violation... in such cases, an update should be performed, yes? To clarify/make sure we're all on the same page here, we're talking about SQL SERVER guid's, not CF UUIDs. I'm sorry, I'm just not getting this. I really don't understand what Reactor's problem is here -- it works with db-generated int primary keys... why can't it work with db-generated uniqueidentifiers? Thanks for continuing this conversation with me, and not giving up... I do appreciate it! Marc ------------------------------ *From:* [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] *On Behalf Of *Brian Kotek *Sent:* Tuesday, December 12, 2006 11:26 AM *To:* [email protected] *Subject:* Re: [Reactor for CF] Two questions 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/ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 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/ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
