A problem with FOREIGN KEY

[Former Member]

Hello

when I update a record on a table whit a FOREIGN KEY I get the following error:

Could not execute 'UPDATE AZIENDA_GERARCHIA SET DESC_AZIENDA_ELEGER0 = 'Entity - Default view', DESC_AZIENDA_ELEGER1 = ...' in 69 ms 626 µs .

SAP DBTech JDBC: [462]: failed on update or delete by foreign key constraint violation: 

The example

CREATE COLUMN TABLE RACCOLTA_AZIENDA_DEF (

COD_RACCOLTA                  VARCHAR(15) NULL,

COD_AZIENDA                   VARCHAR(30) NULL,

COD_AZIENDA_GERARCHIA         VARCHAR(2) NULL,

COD_AZIENDA_ELEGER            VARCHAR(30) NULL

);

CREATE COLUMN TABLE AZIENDA_GERARCHIA (

COD_AZIENDA_GERARCHIA            VARCHAR(2) NOT NULL,

COD_AZIENDA_ELEGER               VARCHAR(30) NOT NULL,

TIPO_AZIENDA_ELEGER              VARCHAR(1) NULL,

COD_AZIENDA_GERARCHIA_PADRE      VARCHAR(2) NULL,

COD_AZIENDA_ELEGER_PADRE         VARCHAR(30) NULL,

FLAG_ORGANIZZAZIONE              SMALLINT NULL DEFAULT 0,

TIPO_CERT_CONTRIBUTOR            VARCHAR(1) NULL DEFAULT 'X',

TIPO_CERT_CONSOLIDATORE          VARCHAR(1) NULL DEFAULT 'X',

TIPO_NODO                        VARCHAR(1) NULL DEFAULT 'X',

COD_AZIENDA_RIFERIMENTO          VARCHAR(30) NULL,

FLAG_TV                          SMALLINT NULL DEFAULT 0,

FLAG_GENERA_FLAT                 SMALLINT NULL DEFAULT 0,

FLAG_AZIENDA_ELEGER_DEFAULT      SMALLINT NULL DEFAULT 0,

FLAG_LIV_APPROV_CONTRIBUTOR      SMALLINT NULL DEFAULT 0,

FLAG_LIV_APPROV_CONSOLIDATORE    SMALLINT NULL DEFAULT 0,

ORDINAMENTO                      INTEGER NULL DEFAULT 0,

PROVENIENZA                      VARCHAR(80) NULL,

USERUPD                          VARCHAR(30) NULL,

DATEUPD                          TIMESTAMP NULL,

DESC_AZIENDA_ELEGER0             VARCHAR(2000) NULL,

DESC_AZIENDA_ELEGER1             VARCHAR(2000) NULL,

DESC_AZIENDA_ELEGER2             VARCHAR(2000) NULL,

DESC_AZIENDA_ELEGER3             VARCHAR(2000) NULL,

FLAG_NOREPORT                    SMALLINT NULL DEFAULT 0,

DATEUPD_ELEGER_PADRE             TIMESTAMP NULL,

TIPO_DIMENSIONE_CUBO                         VARCHAR(1) NULL DEFAULT 'S'

);

ALTER TABLE AZIENDA_GERARCHIA

ADD CONSTRAINT PK_AZIENDA_GERARCHIA

    PRIMARY KEY (COD_AZIENDA_GERARCHIA, COD_AZIENDA_ELEGER);

ALTER TABLE RACCOLTA_AZIENDA_DEF

ADD CONSTRAINT PK_RACCOLTA_AZIENDA_DEF

    PRIMARY KEY (COD_RACCOLTA);

ALTER TABLE RACCOLTA_AZIENDA_DEF

ADD CONSTRAINT FK_RACCOLTA_AZIENDA_DEF_003

    FOREIGN KEY (COD_AZIENDA_GERARCHIA, COD_AZIENDA_ELEGER)

    REFERENCES AZIENDA_GERARCHIA;

INSERT INTO AZIENDA_GERARCHIA (COD_AZIENDA_GERARCHIA, COD_AZIENDA_ELEGER, DESC_AZIENDA_ELEGER0, DESC_AZIENDA_ELEGER1, DESC_AZIENDA_ELEGER2, DESC_AZIENDA_ELEGER3, TIPO_AZIENDA_ELEGER, COD_AZIENDA_GERARCHIA_PADRE, COD_AZIENDA_ELEGER_PADRE, FLAG_ORGANIZZAZIONE, TIPO_CERT_CONTRIBUTOR, TIPO_CERT_CONSOLIDATORE, TIPO_NODO, COD_AZIENDA_RIFERIMENTO, FLAG_TV, FLAG_AZIENDA_ELEGER_DEFAULT, PROVENIENZA, DATEUPD_ELEGER_PADRE)

VALUES ( '$', '$', 'prova', 'prova', 'prova', 'prova', 'R', NULL, NULL, 1, 'A', 'X', 'X', NULL, 0, 1, 'INPUT_WEB', null);

INSERT INTO AZIENDA_GERARCHIA (COD_AZIENDA_GERARCHIA, COD_AZIENDA_ELEGER, DESC_AZIENDA_ELEGER0, DESC_AZIENDA_ELEGER1, DESC_AZIENDA_ELEGER2, DESC_AZIENDA_ELEGER3, TIPO_AZIENDA_ELEGER, COD_AZIENDA_GERARCHIA_PADRE, COD_AZIENDA_ELEGER_PADRE, FLAG_ORGANIZZAZIONE, TIPO_CERT_CONTRIBUTOR, TIPO_CERT_CONSOLIDATORE, TIPO_NODO, COD_AZIENDA_RIFERIMENTO, FLAG_TV, FLAG_AZIENDA_ELEGER_DEFAULT, PROVENIENZA, DATEUPD_ELEGER_PADRE)

VALUES ( '$1', '$1', 'prova', 'prova', 'prova', 'prova', 'R', NULL, NULL, 1, 'A', 'X', 'X', NULL, 0, 1, 'INPUT_WEB', null);

INSERT INTO RACCOLTA_AZIENDA_DEF ( COD_RACCOLTA, COD_AZIENDA, COD_AZIENDA_GERARCHIA, COD_AZIENDA_ELEGER) VALUES ( 'XXX', '001', '$', '$');

-- change description

UPDATE AZIENDA_GERARCHIA

SET DESC_AZIENDA_ELEGER0 = 'Entity - Default view',

        DESC_AZIENDA_ELEGER1 = 'Entity - Default view',

        DESC_AZIENDA_ELEGER2 = 'Entity - Default view',

        DESC_AZIENDA_ELEGER3 = 'Entity - Default view'

WHERE COD_AZIENDA_GERARCHIA = '$'

AND COD_AZIENDA_ELEGER = '$';

Could not execute 'UPDATE AZIENDA_GERARCHIA SET DESC_AZIENDA_ELEGER0 = 'Entity - Default view', DESC_AZIENDA_ELEGER1 = ...' in 69 ms 626 µs .

SAP DBTech JDBC: [462]: failed on update or delete by foreign key constraint violation:

Could you please help us with Issue? Which is the correct procedure to create a FK if I whant update value in to table referenced?

Thanks Paola

[henrique_pinto]

The default FK behavior is "RESTRICT" for both Updates and Deletes, where it won't allow an update or delete in a referenced record.

You might want to force the "CASCADE" behavior (with the "ON UPDATE CASCADE" statement in the end of your FK definition).

Check this for more details:

http://help.sap.com/saphelp_hanaplatform/helpdata/en/20/d329a6751910149d5fdbc4800f92ff/frameset.htm

[Former Member]

I have see it but

<<If a record is updated in the referenced table, the
corresponding records in the referencing table are also
updated with the same values >>

if I declare a fk with this clause we have that
- I can also update the primary key of the table pointed to by the fk
- The scope of which is defined as the pk is updated accordingly

I don't want it

This behavior is quite different from that normally expected when we define fk (Oracle and SQL in cases like this do not allow the update)

by Paola

[former_member184768]

Hi Paola,

Please refer to the earlier discussion on the similar topic. The discussion is quite old and HANA has changed since that. You may check with once again if there is any further update on this topic.

Regards,

Ravi

[Former Member]

Hi Ravindra

I have see Update fail because of foreign key on unrelated field

I work with Revision 60 and the I hoped it was implemented

Thanks

[lbreddemann]

Hi there,

nope - still the same behavior at least up to rev. 67.

As mentioned elsewhere already, ref-constraints actually are not that far up in the priority list of the SAP HANA development team.

Most SAP HANA users don't need them, as they use SAP HANA based on SAP NetWeaver data where consistency is handled on application/framework level.

Not sure when the current implementation will be extended.

Let's wait for SP 7 then...

- Lars