473,322 Members | 1,501 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Sequence alternative on UDB 7.2

Hi there,

is there any way besides a trigger to create a unique number for a column in
a "insert into... select from" statement?

Thx, sr

--
Kein Plan überlebt die erste Feindberührung.
Nov 12 '05 #1
15 2761
Steffen Ramlow wrote:
Hi there,

is there any way besides a trigger to create a unique number for a column
in a "insert into... select from" statement?


You could write your own UDF or use the GENERATE_UNIQUE function. In this
something you would be looking for?

--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Nov 12 '05 #2
Can you use an identity column?
Specify generated by default and then in your select statement you can
specify 'DEFAULT' for that column.

Steffen Ramlow wrote:
Hi there,

is there any way besides a trigger to create a unique number for a column in
a "insert into... select from" statement?

Thx, sr

--
Kein Plan überlebt die erste Feindberührung.


--
Anton Versteeg
IBM Certified DB2 Specialist
IBM Netherlands

Nov 12 '05 #3
Knut Stolze wrote:
Steffen Ramlow wrote:
Hi there,

is there any way besides a trigger to create a unique number for a
column in a "insert into... select from" statement?


You could write your own UDF or use the GENERATE_UNIQUE function. In
this something you would be looking for?


Exactly what I found out :) Thanx!

I need a 44 char Id, this works:

CHAR(TIMESTAMP(GENERATE_UNIQUE())) || REPEAT('0', 18)

Is there a better way? Does it ensure 44 chars?
--
Kein Plan überlebt die erste Feindberührung.
Nov 12 '05 #4
Anton Versteeg wrote:
Can you use an identity column?
Specify generated by default and then in your select statement you can
specify 'DEFAULT' for that column.


Hm, can i generate special formats here? I need a 44 char Id.
--
Kein Plan überlebt die erste Feindberührung.
Nov 12 '05 #5
Steffen Ramlow wrote:
Knut Stolze wrote:
Steffen Ramlow wrote:
is there any way besides a trigger to create a unique number for a
column in a "insert into... select from" statement?


You could write your own UDF or use the GENERATE_UNIQUE function. In
this something you would be looking for?


Exactly what I found out :) Thanx!

I need a 44 char Id, this works:

CHAR(TIMESTAMP(GENERATE_UNIQUE())) || REPEAT('0', 18)

Is there a better way? Does it ensure 44 chars?


Yes, it does. The string representation of a timestamp from the
CHAR(TIMESTAMP(...)) is always 26 characters long.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Nov 12 '05 #6
Knut Stolze wrote:
CHAR(TIMESTAMP(GENERATE_UNIQUE())) || REPEAT('0', 18)

Is there a better way? Does it ensure 44 chars?


Yes, it does. The string representation of a timestamp from the
CHAR(TIMESTAMP(...)) is always 26 characters long.

Ok, thx.

--
Kein Plan überlebt die erste Feindberührung.
Nov 12 '05 #7
>> is there any way besides a trigger to create a unique number for a
column in a "insert into... select from" statement? <<

Well, you could use a REAL key instead of mimicking a 1950's
sequential record number in SQL. You do not create attributes in the
PHYSICAL storage in a properly designed RDBMS; you discover them in
the LOGICAL data model.
Nov 12 '05 #8
> >> is there any way besides a trigger to create a unique number for a
column in a "insert into... select from" statement? << "--CELKO--" <jc*******@earthlink.net> wrote in message
Well, you could use a REAL key instead of mimicking a 1950's
sequential record number in SQL. You do not create attributes in the
PHYSICAL storage in a properly designed RDBMS; you discover them in
the LOGICAL data model.


I completely disagree. That's why you often see primary keys with 10-15
columns, which get propagated onto other many other tables as foreign keys.
This is not only bad for the database performance, it is cumbersome for
programmers and end users alike.

There are many primary keys, such as customer number, order number, etc,
that should be non-significant unique numbers that do not map to a logical
model.

One of the biggest mistakes one can make is to map a logical model directly
to a physical design 100% of the time.
Nov 12 '05 #9
Mark A wrote:
I completely disagree. That's why you often see primary keys with 10-15
columns, which get propagated onto other many other tables as foreign
keys. This is not only bad for the database performance, it is
cumbersome for programmers and end users alike.
I agree (although that does not matter much).
There are many primary keys, such as customer number, order number,
etc, that should be non-significant unique numbers that do not map to a
logical model.

One of the biggest mistakes one can make is to map a logical model
directly to a physical design 100% of the time.

Let me sat this back to you to see if I understand you.

(real example)

Right now I have a table with three columns: CN, N, ID
where CN is a character string (not unique), ID is a unique identifier
number, and N is supposed to be a unique externally supplied positive
integer, but it is missing for many of the table entries and when missing
is given the value -1.

Now N presumably has significance to whoever supplies them, but not to me.
I would have used that as the primary key, but as many of the Ns are
missing and are given the value -1 because the field is non-Null, it is
not sufficient. Id is just an acquisition number of no significance,
generated by a trigger whenever a table row is entered; it is guaranteed
unique. Where N does not exist (i.e., is -1), I must rely on ID.

So I use ID as the foreigh key in all the other tables.

Are you saying that N is, in some sense, unclean? Id is not really a
physical thing like a record number might be. I think it is a
"non-significant unique numbers that do[es] not map to a logical model."

OTOH, it seems to me that to stuff (CN,N) as the foreign key would lead to
inefficiency, and anyway, I cannot guarantee that (CN,N) is unique (but I
think it is).

In other words, should I work harder at eliminating ID from this table and
use (CN,N) as the primary key of this table and use the whole thing as the
foreign key in all the other tables? I would think I would be doing a lot
of string compares unnecessarily this way.
--
.~. Jean-David Beyer Registered Linux User 85642.
/V\ Registered Machine 241939.
/( )\ Shrewsbury, New Jersey http://counter.li.org
^^-^^ 21:15:00 up 2 days, 4:44, 4 users, load average: 6.67, 5.84, 5.11

Nov 12 '05 #10
"Jean-David Beyer" <jd*****@exit109.com> wrote in message
news:10*************@corp.supernews.com...
Mark A wrote:
I completely disagree. That's why you often see primary keys with 10-15
columns, which get propagated onto other many other tables as foreign
keys. This is not only bad for the database performance, it is
cumbersome for programmers and end users alike.


I agree (although that does not matter much).

There are many primary keys, such as customer number, order number,
etc, that should be non-significant unique numbers that do not map to a
logical model.

One of the biggest mistakes one can make is to map a logical model
directly to a physical design 100% of the time.

Let me sat this back to you to see if I understand you.

(real example)

Right now I have a table with three columns: CN, N, ID
where CN is a character string (not unique), ID is a unique identifier
number, and N is supposed to be a unique externally supplied positive
integer, but it is missing for many of the table entries and when missing
is given the value -1.

Now N presumably has significance to whoever supplies them, but not to me.
I would have used that as the primary key, but as many of the Ns are
missing and are given the value -1 because the field is non-Null, it is
not sufficient. Id is just an acquisition number of no significance,
generated by a trigger whenever a table row is entered; it is guaranteed
unique. Where N does not exist (i.e., is -1), I must rely on ID.

So I use ID as the foreigh key in all the other tables.

Are you saying that N is, in some sense, unclean? Id is not really a
physical thing like a record number might be. I think it is a
"non-significant unique numbers that do[es] not map to a logical model."

OTOH, it seems to me that to stuff (CN,N) as the foreign key would lead to
inefficiency, and anyway, I cannot guarantee that (CN,N) is unique (but I
think it is).

In other words, should I work harder at eliminating ID from this table and
use (CN,N) as the primary key of this table and use the whole thing as the
foreign key in all the other tables? I would think I would be doing a lot
of string compares unnecessarily this way.
--
.~. Jean-David Beyer Registered Linux User 85642.


In a logical model, the thing that many times uniquely identifies an entity
is a combination of attributes, which taken together, may be unique. However
if any of the attributes in the primary key have significance, then it is
questionable whether they should be included in the primary key. As
previously stated, one of the reasons for this is database performance and
the convenience of the programmers and end users. The other reason is that
these attributes may change, which causes all kinds of data integrity
problems if that entity has been propagated through many tables, including
historical data.

I am not suggesting that every primary key should be a single non-meaningful
unique number. But when the unique key gets beyond a few columns in length,
or one of values in a primary key column may be changed by the user in the
future, then I don't believe that it is a good design to include those
columns in the primary key (which may necessitate creating a non-significant
number as the primary key).

In the above example (if you are asking me) ID should be the primary key and
CN and N are non-unique attributes. However, if you need to create indexes
on these other columns (alone or in conjunction with other columns in a
composite index) to improve performance, then that is perfectly acceptable.
Nov 12 '05 #11
Mark A wrote:
"Jean-David Beyer" <jd*****@exit109.com> wrote in message
news:10*************@corp.supernews.com...
Mark A wrote:

I completely disagree. That's why you often see primary keys with 10-15
columns, which get propagated onto other many other tables as foreign
keys. This is not only bad for the database performance, it is
cumbersome for programmers and end users alike.


I agree (although that does not matter much).
There are many primary keys, such as customer number, order number,
etc, that should be non-significant unique numbers that do not map to a
logical model.

One of the biggest mistakes one can make is to map a logical model
directly to a physical design 100% of the time.


Let me sat this back to you to see if I understand you.

(real example)

Right now I have a table with three columns: CN, N, ID
where CN is a character string (not unique), ID is a unique identifier
number, and N is supposed to be a unique externally supplied positive
integer, but it is missing for many of the table entries and when missing
is given the value -1.

Now N presumably has significance to whoever supplies them, but not to me.
I would have used that as the primary key, but as many of the Ns are
missing and are given the value -1 because the field is non-Null, it is
not sufficient. Id is just an acquisition number of no significance,
generated by a trigger whenever a table row is entered; it is guaranteed
unique. Where N does not exist (i.e., is -1), I must rely on ID.

So I use ID as the foreigh key in all the other tables.

Are you saying that N is, in some sense, unclean? Id is not really a
physical thing like a record number might be. I think it is a
"non-significant unique numbers that do[es] not map to a logical model."

OTOH, it seems to me that to stuff (CN,N) as the foreign key would lead to
inefficiency, and anyway, I cannot guarantee that (CN,N) is unique (but I
think it is).

In other words, should I work harder at eliminating ID from this table and
use (CN,N) as the primary key of this table and use the whole thing as the
foreign key in all the other tables? I would think I would be doing a lot
of string compares unnecessarily this way.
--
.~. Jean-David Beyer Registered Linux User 85642.

In a logical model, the thing that many times uniquely identifies an entity
is a combination of attributes, which taken together, may be unique. However
if any of the attributes in the primary key have significance, then it is
questionable whether they should be included in the primary key. As
previously stated, one of the reasons for this is database performance and
the convenience of the programmers and end users. The other reason is that
these attributes may change, which causes all kinds of data integrity
problems if that entity has been propagated through many tables, including
historical data.

I am not suggesting that every primary key should be a single non-meaningful
unique number. But when the unique key gets beyond a few columns in length,
or one of values in a primary key column may be changed by the user in the
future, then I don't believe that it is a good design to include those
columns in the primary key (which may necessitate creating a non-significant
number as the primary key).

In the above example (if you are asking me) ID should be the primary key and
CN and N are non-unique attributes. However, if you need to create indexes
on these other columns (alone or in conjunction with other columns in a
composite index) to improve performance, then that is perfectly acceptable.


You might wish to read Joe Celko's comments on this subject ... many of
which can be found at c.d.ms-sqlserver.

The definition of what is, and should be, a primary key is well defined
in relational theory. Most usage of surrogate keys is caused by lazy or
untrained developers (not all ... but most).

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
da******@x.washington.edu
(replace 'x' with a 'u' to reply)

Nov 12 '05 #12
"Daniel Morgan" <da******@x.washington.edu> wrote in message
news:1088568376.212844@yasure...
You might wish to read Joe Celko's comments on this subject ... many of
which can be found at c.d.ms-sqlserver.

The definition of what is, and should be, a primary key is well defined
in relational theory. Most usage of surrogate keys is caused by lazy or
untrained developers (not all ... but most).

--
Daniel Morgan


Here are some articles that compares the differences between the "judicious"
use of surrogate keys (advocated by Chris Date) and the exclusive use of
natural keys (advocated by Joe Celko). One can find many others on the web
with a google search:

http://www.bcarter.com/intsurr1.htm
http://www.dbdebunk.com/page/page/857309.htm
I would note that in the example given in first article above, there is not
one Customer Order System on the planet (that I know of) that uses natural
keys for the Order entity. Not even Oracle's application.

On the contrary to what Daniel says, some natural keys are used by untrained
developers. I am not suggested that natural keys never be used, but when
they get past a certain number columns, and/or the column values in the key
may change, then a surrogate key is usually called for. Non-meaningful
primary keys also allow for the use of check-digits, and important
consideration in many businesses.

As usual, Daniel is flaming (calling people lazy) on a subject he knows
nothing about. I find it amazing that people actually pay for his advice,
and discouraged that he is teaching people. In fact, creating surrogate
(non-meaningful) keys in the proper situations is not laziness, but it
requires an extra step in the physical design process (since it is a
departure from 3rd normal form).

Nov 12 '05 #13
AK
>
You might wish to read Joe Celko's comments on this subject ... many of
which can be found at c.d.ms-sqlserver.

The definition of what is, and should be, a primary key is well defined
in relational theory. Most usage of surrogate keys is caused by lazy or
untrained developers (not all ... but most).


Joe Celko's comments on natural vs. surrogate keys sound very biased
to me. To begin with, repeatedly calling the opponents "idiots"
doesn't sound too professional to me.
Besides, some of his arguments are simply not true. For instance, he
claimes that using natural primary keys saves disk space. This is
obviously incorrect as soon as we consider just one child table with a
foregin key to the parent one.
Nov 12 '05 #14
> > You might wish to read Joe Celko's comments on this subject ... many of
which can be found at c.d.ms-sqlserver.

The definition of what is, and should be, a primary key is well defined
in relational theory. Most usage of surrogate keys is caused by lazy or
untrained developers (not all ... but most).


Joe Celko's comments on natural vs. surrogate keys sound very biased
to me. To begin with, repeatedly calling the opponents "idiots"
doesn't sound too professional to me.
Besides, some of his arguments are simply not true. For instance, he
claimes that using natural primary keys saves disk space. This is
obviously incorrect as soon as we consider just one child table with a
foregin key to the parent one.


Most experienced DBA's consider Celko to be a quack, as a quick check with
google will show.
Nov 12 '05 #15
AK
> Here are some articles that compares the differences between the "judicious"
use of surrogate keys (advocated by Chris Date) and the exclusive use of
natural keys (advocated by Joe Celko). One can find many others on the web
with a google search:

http://www.bcarter.com/intsurr1.htm


I didn't completely agree with this article

1. Both Customer_Number and Customer_Id are somehow generated, and I
don't see much difference

2. this sounds strange to me: "The surrogate_id table is quite simple.
It has an intelligent primary key consisting of the table and column
names for each surrogate primary key in the database, and one data
column to hold the next value for that surrogate key: 1, 2, 3, etc.

At first glance the use of surrogate_id to determine the next key
value also seems simple. All one must do is select the next value and
then update it for later use, correct?

Not quite: That approach isn't much better than the select max
solution because it would still allow two users to select the same
next value in a multi-user scenario.
"

implementing this on EEE V7.1, where there weren't neither sequences
nor idenities yet, I simply updated a row first, then selected the
value from an already locked row. That was easy and made it impossible
for two users to select the same next value.
Nov 12 '05 #16

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Paul Rubin | last post by:
I have a file with contents like: Vegetable: spinach Fruit: banana Flower: Daisy Fruit: pear
7
by: Chinook | last post by:
OO approach to decision sequence? --------------------------------- In a recent thread (Cause for using objects?), Chris Smith replied with (in part): > If your table of photo data has...
10
by: Anthony Best | last post by:
I'm working on an idea that uses sequences. I'm going to create a table like this: id serial, sequence int, keyword varchar(32), text text for every keyword there will be a uniq sequence...
0
by: CJJ | last post by:
Hi, I have an xml schema that defines a number of complex structures where the elements are in sequence, though not all elements are required. <ComplexXmlSchema> <Node A>value</Node A> <Node...
2
by: vvenk | last post by:
Hello: I am running VB.Net to access a oracle 10g table that has a sequence. This is what I tried: Dim liID As Integer = 0 Dim lodcCMD As OleDbCommand = New OleDbCommand("SELECT...
4
by: Eric E | last post by:
Hi, I have a question about sequences. I need a field to have values with no holes in the sequence. However, the values do not need to be in order. My users will draw a number or numbers from...
15
by: Robby Russell | last post by:
I am trying to track down a method of determining what a sequence name is for a SERIAL is in postgresql. For example, CREATE TABLE foo (id SERIAL PRIMARY KEY NOT NULL, bar TEXT); \d foo...
14
by: pat270881 | last post by:
hello, I have to implement a sequence class, however the header file is predefined class sequence { public: // TYPEDEFS and MEMBER CONSTANTS
1
davydany
by: davydany | last post by:
Hey guys...a n00b Here for this site. I'm making a sequence class for my C++ class. And The thing is in the array that I have, lets say i put in {13,17,38,18}, when i see the current values for the...
2
by: ram | last post by:
Here's a little issue I run into more than I like: I often need to unpack a sequence that may be too short or too long into a fixed-size set of items: a, b, c = seq # when seq = (1, 2, 3,...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.