IBM Data and AI

Welcome to the IBM Data and AI Ideas Portal for Clients!

We welcome and appreciate your feedback on IBM Data and AI Products to help make them even better than they are today!
Before you submit an idea, please perform a search first as a similar idea may have already been reported in the portal. If a related idea is not yet listed, please create a new idea and include with it a description which includes expected behavior as well as why having this feature would improve the service and how it would address your use case.
IBM Employees:
Clients:
  • Our team welcomes any feedback and suggestions you have for improving our offerings / products! This forum allows us to connect your offering / product improvement ideas with IBM product and engineering teams.

  • If you have not registered on this portal please click on the following link and register. To complete registration you will need to open the email you will receive from Aha to confirm your identity. http://ibm.biz/IBM-Data-and-AI-Portal-Register

Additional Information:
  • The shorter URL for this site is: https://ibm.biz/IBM-Data-and-AI-Ideas

  • To view our roadmaps: http://ibm.biz/Data-and-AI-Roadmaps

  • Reminder: This is not the place to submit defects or support needs, please use normal support channel for these cases

  • Please do not use the Ideas Portal for reporting bugs - we ask that you report bugs or issues with the product by contacting IBM support.

Support for ON UPDATE CASCADE

Use case: There is a table called release with a primary key id of type integer and an additional column name (the release name). And further tables (e.g. table_x, table_y, …) with a reference (foreign key) to release.id. The working tables are table_x, table_y, a.s.o. and the selects have to be ordered by the foreign key release.id. An ordering by release.name is not possible because of arbitrary release names that are not sortable.

To the discussion that primary keys should not be changed can I say that this use case is not like a change of the release primary key but more like a movement of primary keys/releases and their referenced data to insert a new release and get the desired order.

  • Avatar32.5fb70cce7410889e661286fd7f1897de Guest
  • Jun 6 2019
  • Submitted
Who would benefit from this IDEA? Who knows? We!
How should it work?

The concrete example to understand it better:

create table release (
  id int primary key constraint pk_id
,name varchar(128)
);
create table table_x (
  id int
,release_id int references release (id) {ON UPDATE CASCADE} constraint fk_release_id
,col_1 varchar(64) not null
,primary key (id, release_id) constraint pk_table_x
);
insert into release values (1, 'Release A');
insert into release values (2, 'Release B');
insert into release values (3, 'Release C');

insert into table_x values (231, 1, 'foo');
insert into table_x values (233, 1, 'bar');
insert into table_x values (231, 2, 'fooo');
insert into table_x values (451, 3, 'baz');
insert into table_x values (231, 3, 'foo!');

-- The desired query to keep it simple as possible.
select *
from table_x as o
where release_id <= 3
and release_id = (
  select max(i.release_id)
  from table_x as i
  where i.id = o.id
  and i.release_id <= 3
  )
order by o.id;

-- Update if on update cascade is supported.
update release set id=id+1 where id>=3;
-- This update should automatically update table_x.release_id, too.
insert into release values (3, 'Interm. Rel. B');
insert into table_x values (231, 3, 'Foo !');

Problem:

  1. With an extra ordering column in table release makes the queries complicated and less understandable.
  2. Doing the movement manually is a high effort and error prone.
  3. Such an architecture should be manifested by the database schema.

Proposed solution: Support for ON UPDATE CASCADE by column references. Other database systems supports it, like Postgresql.

Idea Priority High
Priority Justification Informix is not usable for the planned application.
Customer Name Axians ICT Austria GmbH
  • Attach files

NOTICE TO EU RESIDENTS: per EU Data Protection Policy, if you wish to remove your personal information from the IBM ideas portal, please login to the ideas portal using your previously registered information then change your email to "anonymous@euprivacy.out" and first name to "anonymous" and last name to "anonymous". This will ensure that IBM will not send any emails to you about all idea submissions