Fernando:
This "KEEP ANY REFERENCING FOREIGN KEY" clause is not documented in the online docs for v14.10! FWIW.
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.
------Original Message------
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
------------------------------
#Informix