Re: create table with primary key and rowid field

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 the

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 why

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))