Concerning Oracle (and I don't have a lot of experience) you can set up
autincrementing fields I don't remember the details because it has been
about a year but "select from dual" or something similiar will get the next
value. You can also reset the starting pt etc.
----- Original Message -----
From: "Mark Warrick" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Wednesday, June 13, 2001 5:44 PM
Subject: RE: SQL VS ACCESS
> 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
