creating auto increment id field via ddl

[Engin Nalbant]

how can i create auto increment id field via ddl commands ?

try the following code ;

CREATE TABLE APP_TRY.GLB1 (
ID INTEGER PRIMARY KEY ,
CONSTRAINT IDKEY PRIMARY KEY (ID),
CITYCODE VARCHAR(50),
CITYNAME VARCHAR(50),
);
CREATE UNIQUE INDEX B ON APP_TRY.GLB1(CITYCODE);
CREATE INDEX C001 ON APP_TRY.GLB1(CITYNAME);

INSERT INTO APP_TRY.GLB1 (CITYCODE,CITYNAME) VALUES ('34','ISTANBUL');

and get error : (ID) required field
but i try the following code ;

ID INTEGER PRIMARY KEY DEFAULT OBJECTSCRIPT '$I(^APP.TRY.GLB1D)',
CONSTRAINT IDKEY PRIMARY KEY (ID),

get no error.
is this method true ? or how can do via ddl ?

thanks.

[Eric]

This seems to work here (without semi-colons):

CREATE TABLE APP_TRY.GLB1 (
ID INTEGER Identity(1,1) PRIMARY KEY ,
CITYCODE VARCHAR(50),
CITYNAME VARCHAR(50))

INSERT INTO APP_TRY.GLB1 (CITYCODE,CITYNAME) VALUES ('34','ISTANBUL')

Select * From APP_TRY.GLB1

ID CITYCODE CITYNAME
1 34 ISTANBUL

But you will have the same class definition with just this:
CREATE TABLE APP_TRY.GLB (
ID INTEGER Identity ,
CITYCODE VARCHAR(50),
CITYNAME VARCHAR(50))

I usually use classes rather than DDL because they are more flexible
and powerful so I am not sure what is the best practice with DDLs.

Eric

[Engin Nalbant]

> ID INTEGER Identity(1,1) PRIMARY KEY

not working, still get the same error.

"ID' is a required field"

> > thanks.- Alıntıyı gizle -
>
> - Alıntıyı göster -

[Sukesh]

When creating a table via SQL or via Object, one cannot specify ID as
the IDKey / Primary Key.
Your query should be

CREATE TABLE APP_TRY.GLB1 (CITYCODE VARCHAR(50), CITYNAME VARCHAR(50))

Cache will automatically designate ID as the IDkey / Primary Key.

If you wish to define some other property/field as the primary key
(other than ID), say PersonID, then your query should be as

CREATE TABLE APP_TRY.GLB1 (
PERSONID INTEGER PRIMARY KEY ,
CITYCODE VARCHAR(50),
CITYNAME VARCHAR(50),
CONSTRAINT PERSONID_PK PRIMARY KEY (ID));

HTH

Regards
Sukesh Hoogan
Bombay, India
- Enterprise Resource Planning
- Business Intelligence
- Financial Accounting
- Offshore Development

[Sukesh]

More appropriate name would be CITYID instead of PERSONID in this
particular case.

[Eric]

Sukesh,

I agree with you but I would like to mention the syntax I sent works
on 2010.1.
And this also works for renaming ID->CITYID:

CREATE TABLE APP_TRY.GLB (
CITYID INTEGER Identity ,
CITYCODE VARCHAR(50),
CITYNAME VARCHAR(50))

While the syntax you suggest doesn't:

CREATE TABLE APP_TRY.GLB1 (
PERSONID INTEGER PRIMARY KEY ,
CITYCODE VARCHAR(50),
CITYNAME VARCHAR(50),
CONSTRAINT PERSONID_PK PRIMARY KEY (ID));

Unless it is modified like this:

CREATE TABLE APP_TRY.GLB (
PERSONID INTEGER ,
CITYCODE VARCHAR(50),
CITYNAME VARCHAR(50),
CONSTRAINT PERSONID_PK PRIMARY KEY (PERSONID))

But then you have a PK and not an identity as requested.

Eric

[Sukesh]

Sorry that was a typo

CREATE TABLE APP_TRY.GLB1 (
PERSONID INTEGER PRIMARY KEY ,
CITYCODE VARCHAR(50),
CITYNAME VARCHAR(50),

CONSTRAINT PERSONID_PK PRIMARY KEY (PERSONID));

As far as I know, in Caché, ID as the IDKey is always automatically
defined (via DDL or Object definition). You can have another property/
field as the primary key, if you wish. To set the user defined Primary
Key as the IDKey, please have a look at the SET OPTION statement

http://localhost:57772/csp/docbook/DocBook.UI.Page.cls?KEY=RSQL_setoption

Regards
Sukesh

[Sukesh]

Eric

Also check out regarding Identity field.

http://localhost:57772/csp/docbook/DocBook.UI.Page.cls?KEY=RSQL_createtable

QUOTE

IDENTITY Field

Caché SQL automatically creates a RowID field for each table, which
contains a system-generated integer that serves as a unique record
identifier (see section below). The optional IDENTITY keyword allows
you to define a named field with the same properties as a RowID record
identifier field. You can only define one field per table as an
IDENTITY field. Attempting to define more than one IDENTITY field for
a table returns an SQLCODE -308 error. An IDENTITY field has the
following properties:

* Its data type is automatically defined as INTEGER. You do not
need to define a data type. If a data type is specified for an
IDENTITY field, it is ignored. Any specified field constraints, such
as NOT NULL, UNIQUE, or NOT FOR REPLICATION are accepted, but ignored.
* The class definition of the IDENTITY field “myfieldname” is:
SqlRowIdName=myfieldname, Not SqlRowIdPrivate.
* Data values are system-generated. They consist of unique,
nonzero, positive integers.
* By default, IDENTITY field data values cannot be user-specified.
By default, an INSERT statement does not, and can not, specify an
IDENTITY field value. Attempting to do so returns an SQLCODE -111
error. This default can be changed using the Caché ObjectScript $ZUTIL
(115,11) function. For further details, refer to the INSERT statement.
* IDENTITY field data values cannot be user-modified. An UPDATE
statement can not specify an IDENTITY field value. Attempting to do so
returns an SQLCODE -107 error.
* Caché automatically projects a primary key on the IDENTITY field
to ODBC and JDBC. If a CREATE TABLE or ALTER TABLE statement defines a
primary key constraint or a unique constraint on an IDENTITY field, or
on a set of columns including an IDENTITY field, the constraint
definition is ignored.
* A SELECT * statement does return a table's IDENTITY field.

Following an INSERT, UPDATE, or DELETE operation, you can use the
LAST_IDENTITY function to return the value of the IDENTITY field for
the most-recently modified record.

UNQUOTE

Regards
Sukesh

On Dec 24, 8:02 pm, Eric <eric.ane...@gmail.com> wrote:

[Engin Nalbant]

>>>> * A SELECT * statement does return a table's IDENTITY field.

ok, problem solved.
best regards.

On 24 Aralık, 19:57, Sukesh <sukesh_hoo...@yahoo.co.in> wrote:
> Eric
>
> Also check out regarding Identity field.
>

> http://localhost:57772/csp/docbook/DocBook.UI.Page.cls?KEY=RSQL_creat...

> > CITYNAME VARCHAR(50))- Alıntıyı gizle -
>
> - Alıntıyı göster -