AW: [odb-users] many-to-many with two FKs

[Marcel Nehring]

Hi Boris,

sorry, I didn't test that. Because the example in section 6.2.3 of the ODB manual does not create two FKs (is this a bug in the documentation?) and because for my code the CREATE TABLE command also does not I thought that is not done by default.
However, while trying to generate a small code example that reproduces my problem I discovered that ODB later uses an ALTER TABLE command to add the second FK. This is because of the order in which tables get created.
So the resulting table structure does indeed use two FKs.

Sorry for any inconvenience.

Regards,
Marcel

-----Ursprüngliche Nachricht-----
Von: Boris Kolpackov [mailto:boris at codesynthesis.com] 
Gesendet: Donnerstag, 17. September 2015 16:57
An: Marcel Nehring <mne at qosmotec.com>
Cc: odb-users at codesynthesis.com
Betreff: Re: [odb-users] many-to-many with two FKs

Hi Marcel,

Marcel Nehring <mne at qosmotec.com> writes:

> Instead of
> 
> CREATE TABLE project (
> name VARCHAR (255) NOT NULL PRIMARY KEY);
> 
> CREATE TABLE employee (
> id BIGINT UNSIGNED NOT NULL PRIMARY KEY);
> 
> CREATE TABLE employee_projects (
> object_id BIGINT UNSIGNED NOT NULL,
> value VARCHAR (255) NOT NULL REFERENCES project (name));
> 
> I would like
> 
> [...]
> 
> CREATE TABLE employee_projects (
> object_id BIGINT UNSIGNED NOT NULL REFERENCES employee (id), value 
> VARCHAR (255) NOT NULL REFERENCES project (name));

And that's pretty much what I get:

$ cat test.hxx

#include <vector>

#pragma db object
struct project
{
  #pragma db id
  int id;
};

#pragma db object
struct employee
{
  #pragma db id
  int id;

  std::vector<project*> projects;
};

$ odb -d oracle -s test.hxx

$ cat test.sql

CREATE TABLE "project" (
  "id" NUMBER(10) NOT NULL PRIMARY KEY);

CREATE TABLE "employee" (
  "id" NUMBER(10) NOT NULL PRIMARY KEY);

CREATE TABLE "employee_projects" (
  "object_id" NUMBER(10) NOT NULL,
  "index" NUMBER(20) NOT NULL,
  "value" NUMBER(10) NULL,
  CONSTRAINT "employee_projects_object_id_fk"
    FOREIGN KEY ("object_id")
    REFERENCES "employee" ("id")
    ON DELETE CASCADE,
  CONSTRAINT "employee_projects_value_fk"
    FOREIGN KEY ("value")
    REFERENCES "project" ("id")
    DEFERRABLE INITIALLY DEFERRED);

Boris