I use identity columns with Sybase -- the only problem I've experienced is a tendency to occasionally "burn" ID values, resulting in gaps. If you're not worried about non-sequentiality, identity columns are generally not a problem. Do you anticipate crashes? I have run Sybase for about a year, and never experienced a single crash -- meanwhile, Zope has crashed and/or been restarted many, many times. Aside for identity columns, here's an alternative scheme I used some time ago with an Access database (where, aside from DAO/ADO, you simply cannot obtain the previously inserted "autoincrement" value, causing problems for web-based forms): Create a table such as this create table Ids ( Id int not null primary key nonclustered ) Then create either a stored procedure (don't like 'em) or a Zope DTML Method that will (a) get the lowest available ID, and (b) if the table is getting empty (count < delta), then replenish the table with new, unique values, starting at the ID you just obtained. (If the table is empty when you start out, assume this is the first time the method is run, and simply start with a seed of, say, 1.) Because of transaction logic, IDs will never be "burned", and the only gaps you will experience will come from IDs that disappear when their parent records are deleted. Hope this helps. -- Alexander Staubo http://alex.mop.no/ The difference between theory and practice is that, in theory, there is no difference between theory and practice.
-----Original Message----- From: Hung Jung Lu [mailto:hungjunglu@hotmail.com] Sent: Tuesday, March 21, 2000 12:24 AM To: zope@zope.org Subject: [Zope] Sybase: How generate id keys? (surrogate keys)
Hi,
How do people generate id keys (surrogate primary keys) in Sybase+Zope? There are some recommended ways of doing it in Sybase with stored procedures but I can't run stored procedures in Sybase.
I am looking at heavy-traffic strategies. So I need something slightly better than a simple increment table for the keys.
Any recommendations? Thanks in advance.
Hung Jung
______________________________________________________ Get Your Private, Free Email at http://www.hotmail.com
_______________________________________________ Zope maillist - Zope@zope.org http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )