Sérgio,
The feature Paul is referring to (I think) is implemented in 12.10.xC12W1+
To be clear, you don't need to do the alter on the child table. The SERIAL will generate a new value when you INSERT (with a 0) on the parent table. When you INSERT on the child table you just use the parent's table matching value.
What you need to keep in mind, and this is the tricky part, is that once you do the change on the parent table, all the child table's foregin keys will be dropped (if the above feature is not available).
On those cases you need to recreate the foreign keys again, depending on the version you can use the "NOVALIDATE" option to speed it up (make sure no changes on the parent table can occur while you do this).
Here's a (hopefully) full example:
drop table if exists parent_tab;
drop table if exists child_tab;
create table parent_tab
(
col1 INTEGER,
col2 INTEGER,
PRIMARY KEY (col1)
);
create table child_tab
(
col1 INTEGER,
col2 INTEGER,
PRIMARY KEY (col1)
);
ALTER TABLE child_tab ADD CONSTRAINT FOREIGN KEY (col2) REFERENCES parent_tab CONSTRAINT fk_child_tab_1;
INSERT INTO parent_tab VALUES (1,1);
INSERT INTO child_tab VALUES(1,1);
-- USE on 12.10.xC12w1+
--ALTER TABLE parent_tab MODIFY (col1 SERIAL primary key constraint pk_parent_tab KEEP ANY REFERENCING FOREIGN KEY);
-- USE on 12.10.xC12-
ALTER TABLE parent_tab MODIFY (col1 SERIAL primary key constraint pk_parent_tab);
-- Just to illustrate the NOVALIDATE
DELETE FROM parent_tab WHERE col1 = 1;
-- USE on 11.70.xC7- (before 11.17.xC8)
--ALTER TABLE child_tab ADD CONSTRAINT FOREIGN KEY (col2) REFERENCES parent_tab CONSTRAINT fk_child_tab_1;
-- Use on 11.70.xC8+ and 12.10.xC3 <= and 12.10.xC12
ALTER TABLE child_tab ADD CONSTRAINT FOREIGN KEY (col2) REFERENCES parent_tab CONSTRAINT fk_child_tab_1 NOVALIDATE;
Hope this helps. Please test it...
Regards.
------------------------------
FERNANDO NUNES
------------------------------
Original Message:
Sent: Mon December 30, 2019 05:02 AM
From: Sergio Peres
Subject: integer to serial conversion
well, that's one of the problems, since we have client versions from 11.70 to 14.10 we are also defining a base version so we can make the change.
What minimum version is required?
Does alter table propagate changes through constraints?
Regards,
SP
------------------------------
Sergio Peres
AIRC
Coimbra
Original Message:
Sent: Sun December 29, 2019 11:18 AM
From: Paul Watson
Subject: integer to serial conversion
What version ? Only the latter releases - not sure when - can do this
Paul Watson
Oninit LLC
+1-913-387-7529
www.oninit.com
Oninit®️ is a registered trademark of Oninit LLC
Original Message------
Hi,
I have several tables with integer key fields and I want to change to serial, is there a way to make the change propagating this to linked constraints?
Thanks for any help,
SP
------------------------------
Sergio Peres
AIRC
Coimbra
------------------------------
#Informix