Re: create table with primary key and rowid field

Hey Chuck, having weight issues?

<snip>
Thanks Chunk. ...
</snip>

Lim, if he is, it's considered bad form to bring it up. :-)

Rob Berendt
--
Group Dekko Services, LLC
Dept 01.073
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From:
"Lim Hock-Chai" <Lim.Hock-Chai@xxxxxxxxxxxxxxx>
To:
<midrange-l@xxxxxxxxxxxx>
Date:
06/17/2009 12:53 PM
Subject:
Re: create table with primary key and rowid field
Sent by:
midrange-l-bounces@xxxxxxxxxxxx



Thanks Chunk. Below works:
CREATE TABLE pdfsplit.invoice_files (
file_name varchar(128) default NULL,
creation_date timestamp default NULL,
last_update_date timestamp default NULL,
page_count int default 0,
invoice_count int default 0,
sequence_number int default 0,
max_sequence_number int default 0,
type varchar(5) default NULL,
id int not null generated always as identity
(start with 1 increment by 1 cycle),
index_number int default 0,
file_path varchar(128) default NULL,
server_id int default NULL,
status varchar(20) default NULL,
PRIMARY KEY (id))

"CRPence" <CRPbottle@xxxxxxxxx> wrote in message
news:<mailman.12870.1245256612.23468.midrange-l@xxxxxxxxxxxx>...

I do not recall specifically [the details], but IIRC the IDENTITY

is preferred over ROWID. I believe that when using the ROWID type,
might be implicitly generating the constraint for that column, thus
making the PRIMARY KEY specification redundant [and an error]. A
PRIMARY KEY or UNIQUE KEY constraint *does* need to be specified
when using IDENTITY however, to ensure that the values will be
maintained as unique.

Regards, Chuck

Lim Hock-Chai wrote:

<<SNIP>>
One particular adjustment I did is the auto_increment keyword on

id

field. I removed that keyword and change the field type to rowid.

When

I run this sql statement, I'm getting this error: Duplicate UNIQUE
constraint exists for table INVOICE_FILES in PDFSPLIT. Any idea

I'm

getting the error?

CREATE TABLE pdfsplit.invoice_files (
file_name varchar(128) default NULL,
creation_date timestamp default NULL,
last_update_date timestamp default NULL,
page_count int default 0,
invoice_count int default 0,
sequence_number int default 0,
max_sequence_number int default 0,
type varchar(5) default NULL,
id rowid not null,
index_number int default 0,
file_path varchar(128) default NULL,
server_id int default NULL,
status varchar(20) default NULL,
PRIMARY KEY (id))

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.