Cannot define Primary key on typed Table DB2

Question

I have a type on db2 like:

create type  tipo_vehiculo as(
    Descripcion varchar(30)
)MODE DB2SQL;

and have to create a table with pk, using the oid as pk doesn't work, so I try to create it on the table, but the IBM syntax doesn't work for me...

create table JORGEMONGE."tipo_vehiculo_t" of tipo_vehiculo(
    "id_tipo_vehiculo_t"  INT NOT NULL GENERATED ALWAYS AS IDENTITY ( START WITH 1 INCREMENT BY 1 MINVALUE -2147483648 MAXVALUE 2147483647 CACHE 20 ),
    ref is oid user generated
);

An unexpected token "INTEGER" was found following "id_tipo_vehiculo_t". Expected tokens may include: "WITH OPTIONS".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.17.30

also tred:

create table JORGEMONGE."tipo_vehiculo_t" of tipo_vehiculo(
    "id_tipo_vehiculo_t"  INT WITH OPTIONS NOT NULL GENERATED ALWAYS AS IDENTITY ( START WITH 1 INCREMENT BY 1 MINVALUE -2147483648 MAXVALUE 2147483647 CACHE 20 ),
    ref is oid user generated
);

but get:

An unexpected token "options" was found following "ehiculo integer with". Expected tokens may include: "".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.17.30

Any idea to set a pk on that typed table?

Brad Brad · Accepted Answer · 2014-07-27T00:02:50

don't know how to set the identity spec on the primary key, but I know how to set the primary key. You need to make it part of your type.

 create type tipo_vehiculo as( Descripcion varchar(30), id_tipo_vehiculo INTEGER ) MODE DB2SQL;

 create table JORGEMONGE."tipo_vehiculo_t" of tipo_vehiculo( ref is oid user generated, "id_tipo_vehiculo_t" WITH OPTIONS NOT NULL, PRIMARY KEY(id_tipo_vehiculo )  );

See: http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000927.html?cp=SSEPGG_9.7.0%2F2-10-6-90

However, I point out that since an oid column is automatically generated for the table anyway (that's what the ref is oid user generated part means), it might be better to use that as the primary key.

Edit: I've figured out how to do this using the oid. Assuming you take the advice above and use the object reference as the primary key, it would go like this

 create type JORGEMONGE."tipo_vehiculo" as( Descripcion varchar(30) ) MODE DB2SQL;

 create table JORGEMONGE."tipo_vehiculo_t" of JORGEMONGE."tipo_vehiculo"( ref is id_tipo_vehiculo user generated, PRIMARY KEY(id_tipo_vehiculo )  );

 create sequence JORGEMONGE."id_tipo_vehiculo_seq" as ref(JORGEMONGE."tipo_vehiculo_t");

 create trigger JORGEMONGE.gen_id_tipo_vehiculo no cascade before insert on JORGEMONGE."tipo_vehiculo_t" referencing new as new for each row mode db2sql set new.id_tipo_vehiculo = next value for JORGEMONGE.id_tipo_vehiculo_seq;

Documentation is here: http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.structypes.doc/doc/t0006621.html

Cannot define Primary key on typed Table DB2

1 Answers