0

I'm using Cayenne 3.2M1 and Postgres 9.0.1 to create a database. Right now I'm having problems with the primary key generation of Cayenne since I have tables with more than one primary key and as far as I've read Cayenne cant generate more that one primary key per table. So I want the Postgres to do that work. I have this table:

 CREATE TABLE telefonocliente
(
cod_cliente integer NOT NULL DEFAULT currval('cliente_serial'::regclass),
cod_telefono integer NOT NULL DEFAULT nextval('telefonocliente_serial'::regclass),
fijo integer,
CONSTRAINT telefonocliente_pkey PRIMARY KEY (cod_cliente, cod_telefono)
)
WITH (
OIDS=FALSE
);



TelefonoCliente telefono = context.newObject(TelefonoCliente.class);
    telefono.setFijo(4999000);
 context.commitChanges();

and this is the error I get:

INFO: --- transaction started.
19/11/2013 22:46:17 org.apache.cayenne.access.dbsync.CreateIfNoSchemaStrategy     processSchemaUpdate
INFO: Full or partial schema detected, skipping tables creation
19/11/2013 22:46:17 org.apache.cayenne.log.CommonsJdbcEventLogger logQuery
INFO: SELECT nextval('pk_telefonocliente')
Exception in thread "main" org.apache.cayenne.CayenneRuntimeException: [v.3.2M1 Jul 07     2013 16:23:58] Commit Exception
at org.apache.cayenne.access.DataContext.flushToParent(DataContext.java:759)
at org.apache.cayenne.access.DataContext.commitChanges(DataContext.java:676)
at org.example.cayenne.Main.main(Main.java:45)
Caused by: org.postgresql.util.PSQLException: ERROR: no existe la relaci?n     ≪pk_telefonocliente≫
  Position: 16
at     org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:374)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:254)
at org.apache.cayenne.dba.postgres.PostgresPkGenerator.longPkFromDatabase(PostgresPkGenerator.java:79)
at org.apache.cayenne.dba.JdbcPkGenerator.generatePk(JdbcPkGenerator.java:272)
at org.apache.cayenne.access.DataDomainInsertBucket.createPermIds(DataDomainInsertBucket.java:171)
at org.apache.cayenne.access.DataDomainInsertBucket.appendQueriesInternal(DataDomainInsertBucket.java:76)
at org.apache.cayenne.access.DataDomainSyncBucket.appendQueries(DataDomainSyncBucket.java:78)
at org.apache.cayenne.access.DataDomainFlushAction.preprocess(DataDomainFlushAction.java:188)
at org.apache.cayenne.access.DataDomainFlushAction.flush(DataDomainFlushAction.java:144)
at org.apache.cayenne.access.DataDomain.onSyncFlush(DataDomain.java:685)
at org.apache.cayenne.access.DataDomain$2.transform(DataDomain.java:651)
at org.apache.cayenne.access.DataDomain.runInTransaction(DataDomain.java:712)
at org.apache.cayenne.access.DataDomain.onSyncNoFilters(DataDomain.java:648)
at org.apache.cayenne.access.DataDomain$DataDomainSyncFilterChain.onSync(DataDomain.java:852)
at org.apache.cayenne.access.DataDomain.onSync(DataDomain.java:629)
at org.apache.cayenne.access.DataContext.flushToParent(DataContext.java:727)
... 2 more

I've been trying the suggestions on Cayenne tutorial "generated columns", "primary key support" but I seems to always get some error. 

INFO: SELECT nextval('pk_telefonocliente')
Exception in thread "main" org.apache.cayenne.CayenneRuntimeException: [v.3.2M1 Jul 07 2013 16:23:58] Primary Key autogeneration only works for a single attribute.

I want to know how to solve this. Thanks in advance

Vanessa
  • 51
  • 7
  • Cayenne most certainly supports tables with more than one PK column. But to answer your question, could you please explain what 'cod_cliente' and 'cod_telefono' mean in your schema? Are those really generated sequences, or are they also foreign keys to some other tables? – andrus_a Nov 20 '13 at 06:46
  • I have two tables "cliente" and "telefonoCliente". "cliente" has one PK 'cod_cliente'. "telefonocliente" PK is 'cod_telefonocliente, cod_cliente': CONSTRAINT pk_telefonocliente PRIMARY KEY (cod_cliente, cod_telefono), CONSTRAINT fk_telefono_relations_cliente FOREIGN KEY (cod_cliente) REFERENCES cliente (cod_cliente) MATCH SIMPLE – Vanessa Nov 20 '13 at 20:27
  • so did it work with the answer below? – andrus_a Nov 25 '13 at 06:37
  • No, it didn't. My teacher said that I should create a method in java that handles the sequences. I couldn't do it so I'm giving up with multiples PK. – Vanessa Nov 26 '13 at 02:49

2 Answers2

3

From your description in comments, out of 2 columns comprising the PK of 'telefonocliente', only one is truly independent - 'cod_telefono'. This will be what Cayenne will generate. In case of PosgreSQL, you will need the following sequence in DB for this to happen:

CREATE SEQUENCE pk_telefonocliente INCREMENT 20 START 200;

Now, where does the second PK 'cod_cliente' come from? Since it is also FK to another table, it means it is a "dependent" PK, and must come from a relationship. So first you need to map a many-to-one relationship between 'telefonocliente' and 'cliente'. Check "To Dep Pk" checkbox on the 'telefonocliente' side. Generate a matching ObjRelationship for your Java objects. Now you can use it in your code:

Cliente c = .. // get a hold of this object somehow   
TelefonoCliente telefono = context.newObject(TelefonoCliente.class);
telefono.setFijo(4999000);
telefono.setCliente(c); // this line is what will populate 'cod_cliente' PK/FK

That should be it.

andrus_a
  • 2,528
  • 1
  • 16
  • 10
  • Hi @andrus_a I have read on the official doc the following sentence: "you must select the relationship going in the direction from "master" to "dependent" and check the "To Dep PK" checkbox on this relationship." Then I think that "cliente" is the maser, because "telefonocliente" can't exist without it (see the "cod_cliente" PK) So I have thought I should check the "dependent PK" flag on the relation which is on the "cliente" DbEntity definition. Where I'm wrong ? – Andrea Borgogelli Avveduti Feb 20 '17 at 10:33
1

The primary key is allowed just to be one per a table! In your case you create a primary key on two columns, that is right, it is defined in an SQL standard and Postgres supports it well.

However there is a not in Cayenne documentation:

Cayenne only supports automatic PK generation for a single column per table.

see http://cayenne.apache.org/docs/3.0/primary-key-generation.html at the bottom of the page.

Probably they can fix it in a newer version or you can put a request to the Cayenne community.

Martin Strejc
  • 4,307
  • 2
  • 23
  • 38