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

[Boris Kolpackov]

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