You are viewing a plain text version of this content. The canonical link for it is here.

Posted to user@phoenix.apache.org by mferlay <mf...@gnubila.fr> on 2017/03/02 13:32:40 UTC

How to migrate sql cascade and foreign keys

Hi everybody, 
I need to migrate an sql script which creates all our database in apache
phoenix format. I do not manage to translate foreign keys and delete cascade
from this following sample:

CREATE TABLE IF NOT EXISTS A(
  ID VARCHAR(255) NOT NULL,
  colX VARCHAR(255) NULL,
  PRIMARY KEY(ID)
);

CREATE TABLE IF NOT EXISTS B(
  ID VARCHAR(255) NOT NULL,
  colY VARCHAR(255) NOT NULL,
  PRIMARY KEY(colY)
);

CREATE TABLE IF NOT EXISTS C(
  colY VARCHAR(255) NOT NULL,
  ID VARCHAR(255) NOT NULL,
  PRIMARY KEY(ID,colY),
  FOREIGN KEY(ID)
    REFERENCES A(ID)
      ON DELETE CASCADE
      ON UPDATE NO ACTION,
  FOREIGN KEY(colY)
    REFERENCES B(colY)
      ON DELETE CASCADE
      ON UPDATE NO ACTION
)


thanks for your help,

Regards



--
View this message in context: http://apache-phoenix-user-list.1124778.n5.nabble.com/How-to-migrate-sql-cascade-and-foreign-keys-tp3226.html
Sent from the Apache Phoenix User List mailing list archive at Nabble.com.

Re: How to migrate sql cascade and foreign keys

Posted by mferlay <mf...@gnubila.fr>.

Thanks Sergey for your answer, i'll try it.

Regards,

Mathieu



--
View this message in context: http://apache-phoenix-user-list.1124778.n5.nabble.com/How-to-migrate-sql-cascade-and-foreign-keys-tp3226p3255.html
Sent from the Apache Phoenix User List mailing list archive at Nabble.com.

Re: How to migrate sql cascade and foreign keys

Posted by Sergey Soldatov <se...@gmail.com>.

Well, Apache Phoenix doesn't support foreign key, so you need to manage
this functionality on your application layer. Sometimes, depending on the
scenario you may emulate this functionality using VIEWs for user table with
additional columns instead of creating a set of separated tables. More
information about views you may find at
https://phoenix.apache.org/views.html


Thanks,
Sergey

On Thu, Mar 2, 2017 at 5:32 AM, mferlay <mf...@gnubila.fr> wrote:

> Hi everybody,
> I need to migrate an sql script which creates all our database in apache
> phoenix format. I do not manage to translate foreign keys and delete
> cascade
> from this following sample:
>
> CREATE TABLE IF NOT EXISTS A(
>   ID VARCHAR(255) NOT NULL,
>   colX VARCHAR(255) NULL,
>   PRIMARY KEY(ID)
> );
>
> CREATE TABLE IF NOT EXISTS B(
>   ID VARCHAR(255) NOT NULL,
>   colY VARCHAR(255) NOT NULL,
>   PRIMARY KEY(colY)
> );
>
> CREATE TABLE IF NOT EXISTS C(
>   colY VARCHAR(255) NOT NULL,
>   ID VARCHAR(255) NOT NULL,
>   PRIMARY KEY(ID,colY),
>   FOREIGN KEY(ID)
>     REFERENCES A(ID)
>       ON DELETE CASCADE
>       ON UPDATE NO ACTION,
>   FOREIGN KEY(colY)
>     REFERENCES B(colY)
>       ON DELETE CASCADE
>       ON UPDATE NO ACTION
> )
>
>
> thanks for your help,
>
> Regards
>
>
>
> --
> View this message in context: http://apache-phoenix-user-
> list.1124778.n5.nabble.com/How-to-migrate-sql-cascade-
> and-foreign-keys-tp3226.html
> Sent from the Apache Phoenix User List mailing list archive at Nabble.com.
>