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/
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
