Costas,
Your point is well taken, but I never meant to imply that I would allow a
primary key field to be anything other than a number (INT).
The name of the primary key field doesn't matter, however I typically do
what you do and prefix (or suffix) the primary key field name with "ID".
I setup one table in the database called KEYS. (Very simple: ID_KEY (INT) &
USED_DATETIME (date/time). Whenever I need a unique key, I call the
following template:
<!-- begin act_global_get_key.cfm -->
<!--- this template generates a unique key which can be used in any table in
the database --->
<cfsetting enablecfoutputonly="yes">
<cftransaction>
<cfquery name="max" datasource="#session.dsn#">
SELECT max (ID_KEY) as maxid
FROM KEYS
</cfquery>
<cfset ID_KEY = max.maxid+1>
<cfquery name="insert_key" datasource="#session.dsn#">
INSERT INTO KEYS (ID_KEY, USED_DATETIME) VALUES (#id_key#,
#CreateODBCDateTime(Now())#)
</cfquery>
</cftransaction>
<cfsetting enablecfoutputonly="no">
<!-- end act_global_get_key.cfm -->
If I was using Oracle as the DB, I would call a stored procedure to have the
DB create a unique key for me.
One of the benefits of this is that all numbers used for primary keys in all
tables are unique. So you never have to worry about the "scope" (persay -
that's not the right term for that) of the number because you know it could
have only come from one of the tables in the system.
The issue about joins is true as well. I'll RARELY join text fields.
Your point about upsizing as an INT field and then changing it back to an
INT PK SEED {n} field is also absolutely valid - if the database supports
autonumbering. Last I checked, Oracle does not support autonumber fields.
(I could be wrong about that.)
---mark
------------------------------------
Mark Warrick - Fusioneers.com
Personal Email: [EMAIL PROTECTED]
Business Email: [EMAIL PROTECTED]
Phone: 714-547-5386
Efax: 801-730-7289
Personal URL: http://www.warrick.net
Business URL: http://www.fusioneers.com
ICQ: 125160 / AIM: markwarric
====================================
> -----Original Message-----
> From: Costas Piliotis [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, June 13, 2001 2:24 PM
> To: CF-Talk
> Subject: RE: SQL VS ACCESS
>
>
> Great, but what happens when you wish to change the name of a field in a
> lookup table?
>
> Do you run update statements on EVERY foreign key field?
>
> Wouldn't it be easier to just change a value in the database?
>
> Further, requesting that user's supply indeces isn't the best way to work;
> user's have a tendancy to want to change the value of their indices from
> time to time. For example, If I have table a:
>
> CustomerID int
> AccountingID char
> CompanyName char
>
> And I want to change the accounting ID (for whatever reason), I'd be okay.
> However, if I were to omit the customerID field and stick with the
> Accounting ID Field, the change would be acceptable.
>
> Further, joins on integer fields work much faster than joins on character
> fields. The database has that much less to work with matching up.
>
> You are right though, autonumber fields can be a real pain. What
> you should
> usually do in a migration from access to sql server is first go
> into all of
> your tables, turn off autonumber, and leave the field as an integer field,
> when you upsize, go back in and revert to autonumber.
>
>
>
>
> -----Original Message-----
> From: Mark Warrick [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, June 13, 2001 11:27 AM
> To: CF-Talk
> Subject: RE: SQL VS ACCESS
>
>
> I've learned to avoid using autonumber fields whenever possible. It makes
> the job of moving databases between platforms simple. At worst, I have to
> put the primary key constraints back on the index fields of each table -
> which is no big deal.
>
> ---mark
>
> ------------------------------------
> Mark Warrick - Fusioneers.com
> Personal Email: [EMAIL PROTECTED]
> Business Email: [EMAIL PROTECTED]
> Phone: 714-547-5386
> Efax: 801-730-7289
> Personal URL: http://www.warrick.net
> Business URL: http://www.fusioneers.com
> ICQ: 125160 / AIM: markwarric ====================================
>
> > -----Original Message-----
> > From: Bud [mailto:[EMAIL PROTECTED]]
> > Sent: Wednesday, June 13, 2001 4:02 AM
> > To: CF-Talk
> > Subject: RE: SQL VS ACCESS
> >
> >
> > On 6/12/01, Norman Elton penned:
> > >Make sure all your fields that should be indexed are indexed. I think
> > >the upsizing wizard forgets about this.
> >
> > The upsizing wizard does a pretty good job of rebuilding the indexes
> > and relationships. It's DTS that loses that stuff.
> > --
> >
> > Bud Schneehagen - Tropical Web Creations
> >
> > _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
> > ColdFusion Solutions / eCommerce Development [EMAIL PROTECTED]
> > http://www.twcreations.com/
> > 954.721.3452
> >
> >
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
