Which is the correct procedure to add new columns to a table with one or more FKs

[Former Member]

When I add a column on a table that has the foreign key constraint with istruction

ALTER TABLE REGOLA_ELISIONE ADD (FLAG_PERIODICO SMALLINT DEFAULT 0);

I get the following error: 

“Could not execute 'ALTER TABLE REGOLA_ELISIONE4 ADD (FLAG_PERIODICO SMALLINT DEFAULT 0)' in 49 ms 502 µs .

SAP DBTech JDBC: [7]: feature not supported: cannot add column of referencing row table”

If I delete a foreign key from a table, add a new column and then I try to ecreate the FK just deleted, I get the following error 

“Could not execute 'ALTER TABLE DATI_CAUSALI ADD CONSTRAINT FK_DATI_CAUSALI_002 FOREIGN KEY (COD_ELISIONE) REFERENCES ...' in 49 ms 516 µs .

SAP DBTech JDBC: [7]: feature not supported: cannot create foreign key constraint on referenced table having added column”

  Could you please help us with Issue? Which is the correct procedure to add new columns to a table with one or more FKs?

Thanks Paola

[rindia]

Hi Paola,

I guess first you need to drop constraint, add the column and then add back the constraint.

You can get the ALTER TABLE syntax here.

If you can send the create statement of the tables with all the constraints then I can try and see.

Regards

Raj

[Former Member]

tahnks for your answer

I have create two tables

-- create the table

--1 - table conto

CREATE TABLE CONTO (

COD_CONTO                    VARCHAR(30) NOT NULL,

DESC_CONTO0                  VARCHAR(2000) NULL,

NATURA_CONTO                 VARCHAR(1) NULL

);

-- PK

ALTER TABLE CONTO

ADD CONSTRAINT PK_CONTO

    PRIMARY KEY (COD_CONTO);

-- 2 - table regola_elisione

CREATE TABLE REGOLA_ELISIONE (

COD_ELISIONE                          VARCHAR(30) NOT NULL,

DESC_ELISIONE0                        VARCHAR(200) NULL,

DESC_ELISIONE1                        VARCHAR(200) NULL,

COD_CONTO_DC_DARE                     VARCHAR(30) NULL,

COD_CONTO_DC_AVERE                    VARCHAR(30) NULL 

);

--'Create primary key on table REGOLA_ELISIONE';

ALTER TABLE REGOLA_ELISIONE

ADD CONSTRAINT PK_REGOLA_ELISIONE

    PRIMARY KEY (COD_ELISIONE);

--'Create foreign key on table REGOLA_ELISIONE';

ALTER TABLE REGOLA_ELISIONE

ADD CONSTRAINT FK_REGOLA_ELISIONE_001

    FOREIGN KEY (COD_CONTO_DC_DARE)

    REFERENCES CONTO;

ALTER TABLE REGOLA_ELISIONE

ADD CONSTRAINT FK_REGOLA_ELISIONE_002

    FOREIGN KEY (COD_CONTO_DC_AVERE)

    REFERENCES CONTO;

i add a column

ALTER TABLE REGOLA_ELISIONE ADD (FLAG_PERIODICO SMALLINT DEFAULT 0);

I get the following error:

“Could not execute 'ALTER TABLE REGOLA_ELISIONE4 ADD (FLAG_PERIODICO SMALLINT DEFAULT 0)' in 49 ms 502 µs .

SAP DBTech JDBC: [7]: feature not supported: cannot add column of referencing row table”

if I drop the FK and after add new column

ALTER TABLE REGOLA_ELISIONE  DROP CONSTRAINT FK_REGOLA_ELISIONE_001;

ALTER TABLE REGOLA_ELISIONE  DROP CONSTRAINT FK_REGOLA_ELISIONE_002;

ALTER TABLE REGOLA_ELISIONE4 ADD (FLAG_PERIODICO SMALLINT DEFAULT 0);

I get the following error

“Could not execute 'ALTER TABLE DATI_CAUSALI ADD CONSTRAINT FK_DATI_CAUSALI_002 FOREIGN KEY (COD_ELISIONE) REFERENCES ...' in 49 ms 516 µs .

SAP DBTech JDBC: [7]: feature not supported: cannot create foreign key constraint on referenced table having added column”

Thanks Paola

[Former Member]

Hi Paola,

I got with what Raj said above. I just tested  by

dropping constraints-->Add new column on table --> recreate constraints

Script goes here:

-----------------

-- Drop constraints   

Alter table    REGOLA_ELISIONE  drop constraint FK_REGOLA_ELISIONE_001;

Alter table    REGOLA_ELISIONE  drop constraint FK_REGOLA_ELISIONE_002;

-- Add column

ALTER TABLE REGOLA_ELISIONE ADD (FLAG_PERIODICO SMALLINT DEFAULT 0);

-- Again Add constraints

ALTER TABLE REGOLA_ELISIONE

ADD CONSTRAINT FK_REGOLA_ELISIONE_001

    FOREIGN KEY (COD_CONTO_DC_DARE)

    REFERENCES CONTO;

ALTER TABLE REGOLA_ELISIONE

ADD CONSTRAINT FK_REGOLA_ELISIONE_002

    FOREIGN KEY (COD_CONTO_DC_AVERE)

    REFERENCES CONTO;

-----------------------------

And it works perfect!

Thanks

Lokesh

[former_member184768]

Hi Paola,

Please change the create table syntax and add COLUMN. You are creating row tables, which is not advisable.

Hence make the following changes and it should work.

paola vivarelli wrote:
-- create the table
--1 - table conto 
CREATE COLUMN TABLE CONTO (
 COD_CONTO                    VARCHAR(30) NOT NULL,
 DESC_CONTO0                  VARCHAR(2000) NULL,
 NATURA_CONTO                 VARCHAR(1) NULL
);
-- PK 
ALTER TABLE CONTO
ADD CONSTRAINT PK_CONTO
    PRIMARY KEY (COD_CONTO);
-- 2 - table regola_elisione 
CREATE COLUMN TABLE REGOLA_ELISIONE (
 COD_ELISIONE                          VARCHAR(30) NOT NULL,
 DESC_ELISIONE0                        VARCHAR(200) NULL,
 DESC_ELISIONE1                        VARCHAR(200) NULL,
 COD_CONTO_DC_DARE                     VARCHAR(30) NULL,
 COD_CONTO_DC_AVERE                    VARCHAR(30) NULL  
);
--'Create primary key on table REGOLA_ELISIONE';
ALTER TABLE REGOLA_ELISIONE
ADD CONSTRAINT PK_REGOLA_ELISIONE
    PRIMARY KEY (COD_ELISIONE);
--'Create foreign key on table REGOLA_ELISIONE';
ALTER TABLE REGOLA_ELISIONE
ADD CONSTRAINT FK_REGOLA_ELISIONE_001
    FOREIGN KEY (COD_CONTO_DC_DARE)
    REFERENCES CONTO;
ALTER TABLE REGOLA_ELISIONE
ADD CONSTRAINT FK_REGOLA_ELISIONE_002
    FOREIGN KEY (COD_CONTO_DC_AVERE)
    REFERENCES CONTO;
i add a column 
ALTER TABLE REGOLA_ELISIONE ADD (FLAG_PERIODICO SMALLINT DEFAULT 0);

Regards,

Ravi

[rindia]

Hi Paola,

As Ravi said whenever you create tables it is better to create column table instead of row tables as column tables has some advantages. Also there are situations where we can use row tables also.

Below is the complete syntax for Row based table and Column based table.

Based on type of tables syntax varies.

For your scenario (row tables), syntax is:

DROP TABLE CONTO CASCADE;
DROP TABLE REGOLA_ELISIONE;
CREATE TABLE CONTO (
COD_CONTO                    VARCHAR(30) NOT NULL,
DESC_CONTO0                  VARCHAR(2000) NULL,
NATURA_CONTO                 VARCHAR(1) NULL
);

ALTER TABLE CONTO
ADD CONSTRAINT PK_CONTO
    PRIMARY KEY (COD_CONTO);
CREATE TABLE REGOLA_ELISIONE (
COD_ELISIONE                          VARCHAR(30) NOT NULL,
DESC_ELISIONE0                        VARCHAR(200) NULL,
DESC_ELISIONE1                        VARCHAR(200) NULL,
COD_CONTO_DC_DARE                     VARCHAR(30) NULL,
COD_CONTO_DC_AVERE                    VARCHAR(30) NULL  
);
ALTER TABLE REGOLA_ELISIONE
ADD CONSTRAINT PK_REGOLA_ELISIONE
    PRIMARY KEY (COD_ELISIONE);
ALTER TABLE REGOLA_ELISIONE
ADD CONSTRAINT FK_REGOLA_ELISIONE_001
    FOREIGN KEY (COD_CONTO_DC_DARE)
    REFERENCES CONTO;
ALTER TABLE REGOLA_ELISIONE
ADD CONSTRAINT FK_REGOLA_ELISIONE_002
    FOREIGN KEY (COD_CONTO_DC_AVERE)
    REFERENCES CONTO;
ALTER TABLE REGOLA_ELISIONE  DROP CONSTRAINT FK_REGOLA_ELISIONE_001;
ALTER TABLE REGOLA_ELISIONE  DROP CONSTRAINT FK_REGOLA_ELISIONE_002;

ALTER TABLE REGOLA_ELISIONE ADD (FLAG_PERIODICO SMALLINT DEFAULT 0);
ALTER TABLE REGOLA_ELISIONE
ADD CONSTRAINT FK_REGOLA_ELISIONE_001
    FOREIGN KEY (COD_CONTO_DC_DARE)
    REFERENCES CONTO;
ALTER TABLE REGOLA_ELISIONE
ADD CONSTRAINT FK_REGOLA_ELISIONE_002
    FOREIGN KEY (COD_CONTO_DC_AVERE)
    REFERENCES CONTO;

Here is the screenshot of the result:

  

For column tables, syntax is:

DROP TABLE CONTO CASCADE;
DROP TABLE REGOLA_ELISIONE;
CREATE COLUMN TABLE CONTO (
COD_CONTO                    VARCHAR(30) NOT NULL,
DESC_CONTO0                  VARCHAR(2000) NULL,
NATURA_CONTO                 VARCHAR(1) NULL
);

ALTER TABLE CONTO
ADD CONSTRAINT PK_CONTO
    PRIMARY KEY (COD_CONTO);
CREATE COLUMN TABLE REGOLA_ELISIONE (
COD_ELISIONE                          VARCHAR(30) NOT NULL,
DESC_ELISIONE0                        VARCHAR(200) NULL,
DESC_ELISIONE1                        VARCHAR(200) NULL,
COD_CONTO_DC_DARE                     VARCHAR(30) NULL,
COD_CONTO_DC_AVERE                    VARCHAR(30) NULL  
);
ALTER TABLE REGOLA_ELISIONE
ADD CONSTRAINT PK_REGOLA_ELISIONE
    PRIMARY KEY (COD_ELISIONE);
ALTER TABLE REGOLA_ELISIONE
ADD CONSTRAINT FK_REGOLA_ELISIONE_001
    FOREIGN KEY (COD_CONTO_DC_DARE)
    REFERENCES CONTO;
ALTER TABLE REGOLA_ELISIONE
ADD CONSTRAINT FK_REGOLA_ELISIONE_002
    FOREIGN KEY (COD_CONTO_DC_AVERE)
    REFERENCES CONTO;
--ALTER TABLE REGOLA_ELISIONE  DROP CONSTRAINT FK_REGOLA_ELISIONE_001;
--ALTER TABLE REGOLA_ELISIONE  DROP CONSTRAINT FK_REGOLA_ELISIONE_002;

ALTER TABLE REGOLA_ELISIONE ADD (FLAG_PERIODICO SMALLINT DEFAULT 0);

Here is the screenshot of the result.

 

Regards

Raj

[Former Member]

TANKS TO ALL

Paola

[former_member184768]

Hi Paola,

If your issue is resolved, kindly close the discussion thread. It is still appearing as open / not answered on the discussions.

Regards,

Ravi