Relationship btw Tables?

is it possible to set relationship constraints (stop on delete or cascade on update, etc) btw tables in informix? because recently we took over some informix database in a unix box. There's about 500 over tables in the database and when i did a schema, i noticed there's no relationship constraints betw a "MAIN" table and its "SUB" tables. So i was wondering if its because informix does not have this feature? I was using mssql and its common practise to set such relationship, thus i was puzzled when i saw this. Thanks

>>is it possible to set relationship constraints yes >> do you have to? no. these days it depends on your application software vendor/developers. >>mssql how long has myssql been around? early on in my informix programming years (10+ yrs ago..ouch!), the common practice was the programmers coded relationships (partent/child etc) into programs. But as coding has moved to a point/click world the relationship control has more and more fallen upon the database. is that because it belongs there? or because the programmers don't go that deep? (i won't go off on that tangent) Some old systems may still follow the old practices (hey, if it ain't broke, don't fix it... .. there's a lot of informix out there that just doesn't break). You may be dealing with an older system that follows the common practice of it's time, thereby database controls may not be necessary/might be overkill. You may also be dealing with a vendor that claims to be database independent. Since informix, oracle, mssql, etc, may or may not implement relationships the same way... the software vendor controls it so the customer can put it on their database of choice. We run SAP. We have a 1.2TB Informix database, with no database defined table-to-table relationships. SAP controls it all. We also have SAP on Oracle.... same deal... SAP controls all the relationships, not the database. thanks, Norma Jean -----Original Message----- From: michael@mikeymall.com [mailto:michael@mikeymall.com] Sent: Thursday, April 01, 2004 12:24 AM To: ids@iiug.org; forum.subscriber@iiug.org Subject: Relationship btw Tables? [2772] is it possible to set relationship constraints (stop on delete or cascade on update, etc) btw tables in informix? because recently we took over some informix database in a unix box. There's about 500 over tables in the database and when i did a schema, i noticed there's no relationship constraints betw a "MAIN" table and its "SUB" tables. So i was wondering if its because informix does not have this feature? I was using mssql and its common practise to set such relationship, thus i was puzzled when i saw this. Thanks ----------------------------------------- ============================================================ The information contained in this message may be privileged and confidential and protected from disclosure. If the reader of this message is not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any reproduction, dissemination or distribution of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by replying to the message and deleting it from your computer. Thank you. Tellabs ============================================================

Yes, Informix supports and enforces relational integrity constraints. See ALTER TABLE ADD CONSTRAINT in the Guide to SQL Syntax manual available online at the IBM website. Informix supports cascading deletes on FOREIGN KEY constraints, I do not know what a cascading update is? Art S. Kagel ----- Original Message ----- From: Michael <michael@mikeymall.com> At: 4/ 1 2:14 > is it possible to set relationship constraints (stop on delete or cascade on > update, etc) btw tables in informix? > > because recently we took over some informix database in a unix box. There's > about 500 over tables in the database and when i did a schema, i noticed there's > no relationship constraints betw a "MAIN" table and its "SUB" tables. So i was > wondering if its because informix does not have this feature? > > I was using mssql and its common practise to set such relationship, thus i was > puzzled when i saw this. > > Thanks

----LNX_Thu_Apr_01_2004_18:53:15_V3.33-- Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable >Datum: 2004.04.01 10:21:09 >Sender: Michael <michael@mikeymall.com> > >is it possible to set relationship constraints (stop on delete or cascad= e on >update, etc) btw tables in informix? = Yes, for the exact possibilities you will have to look into the documenta= tion. There is one PDF-File about SQL Syntax and there you find the information= at 'create table' and 'alter table' statements. = >because recently we took over some informix database in a unix box. Ther= e's >about 500 over tables in the database and when i did a schema, i noticed= there's >no relationship constraints betw a "MAIN" table and its "SUB" tables. >So i was wondering if its because informix does not have this feature? > >I was using mssql and its common practise to set such relationship, thus= i >was puzzled when i saw this. = The decision to use this 'common practise' is done (or not) by the applic= ation developer (database creator). Informix supports such relationships, but s= ome application designers like to have most of the logic in the application (= one very famous example is SAP with thousands of tables an no constraints/relati= onships in the database except from some primary keys. = Regards, Andreas = ------------------------------------------------ SPAR Oesterreichische Warenhandels-AG Hauptzentrale Europastra=DFe 3 A-5015 Salzburg = Telefon : +43 662 4470 24423 E-Mail : Andreas.KUTSCHE@spar.at Internet: http://www.spar.at ------------------------------------------------ = ----LNX_Thu_Apr_01_2004_18:53:15_V3.33----

Art Kagel wrote on 04/01/2004 05:43:27 AM: > Yes, Informix supports and enforces relational integrity > constraints. See ALTER TABLE ADD CONSTRAINT in the Guide > to SQL Syntax manual available online at the > IBM website. Informix supports cascading deletes on FOREIGN KEY > constraints, I do not know what a cascading update is? > Michael <michael@mikeymall.com> wrote: > > is it possible to set relationship constraints (stop on delete or cascade on > > update, etc) btw tables in informix? > > > > because recently we took over some informix database in a unix box. There's > > about 500 over tables in the database and when i did a schema, i noticed > > there's no relationship constraints betw a "MAIN" table and its "SUB" > > tables. So i was wondering if its because informix does not have this feature? > > > > I was using mssql and its common practise to set such > > relationship, thus i was puzzled when i saw this. As Art said, more recent versions of Informix do support most of these features. By default, if you have a foreign key, the behaviour is 'stop on delete' (more usually termed 'restrict'); you can optionally say 'on delete cascade'. If the database you are inheriting was designed more than, say, 5 years ago, then there is a chance that it was designed on a system without that sort of support. A cascading update is the analogue of 'ON DELETE CASCADE'; if you alter the primary key value to which the foreign key refers, then instead of being restricted, the DBMS automatically updates the matching key values in the table with the foreign key. IDS 9.40 does not support cascading updates. -- Jonathan Leffler (jleffler@us.ibm.com) STSM, Informix Database Engineering, IBM Data Management 4100 Bohannon Drive, Menlo Park, CA 94025 Tel: +1 650-926-6921 Tie-Line: 630-6921 "I don't suffer from insanity; I enjoy every minute of it!"

Hi, your more detailed description of this confirms what I was afraid it would be ... Of course it is possible to implement all sorts of logic in the client application, even though the database could handle it. Triggers, stored procedures, constraints (including referential constraints) have been around in OnLine (the predecessor to IDS) and IDS itself for a long time. Therefore I can just now think of only three explanations for implementing such logic in the client application instead of using the servers functionality: - the application is very old, from a time where such functionality was not fully available in the server. (But I think in that case we would be talking about 10+ years). - the application was written to be independent of the database server product. Some of the really good stuff on offer in servers is not really standardized. Therefore by moving all such logic to client application you would be more portable. But then again you could almost use C-ISAM instead of a full blown database server ... - job security on the side of the application developers. Obviously it didn't help that much in this category. This is a really wide topic you're looking at. From what you tell I think a lot can be done, i.e. a lot of the logic now in the client application can be off-loaded to the server to be handled there much cleaner and easier. However, since you say you do not yet have much experience with IDS, I think it is not really possible to work out a good database design via news-group help ... :-( I'm not sure what trainings are on offer for IBM Informix products in your geography. There used to be a couple of trainings available on such topics (like database design, application development using ESQL/C, even "Triggers" had its own training I think). Another possibility (and probably faster) might be to get a knowlegable and experienced consultant (from IBM or an IBM Partner) to help you (not for free though ...) ? Regards, Martin -- Martin Fuerderer IBM Informix Development Munich Data Management Solutions "Michael" <michael@mikeymall.com> 01.04.2004 16:12 To Martin Fuerderer/Germany/IBM@IBMDE cc <forum.subscriber@iiug.org>, <ids@iiug.org> Subject RE: Relationship btw Tables? [2772] Hi, Actually I used the "-ss" option when I did the schema. Can I confirm with you that if I used "-ss" it should actually list out everything? Including relationship constraints, etc? The problem with the current database is that I don't seem to see any relationship connection. And they have 500 over tables.. so you can imagine the mess. I believe like some of the people had said, they're using the application to "relate" the tables. But won't that be a mess? I don't know, coz usually I always create the relationship in the database. What if I connect directly to the database, added a record in the MAIN table and some records in the SUB table. And if I delete that record in the MAIN table and forgot about the records in the SUB table, wont there be redundant data? Right now because the client had a bad experience with the previous developer and remove them from the development and found us instead. Although the previous developer had thick manuals describing the tables but its not detailed and didn't state the relationship. So you can imagine we got to look through the application source codes and the tables and relate them through instinct. And its my first time using Informix with UNIX, so I were just puzzled with all the questions. Thus, I apologise if I irritate any users for my novice questions. Regards, Michael -----Original Message----- From: Martin Fuerderer [mailto:MARTINFU@de.ibm.com] Sent: Thursday, April 01, 2004 9:21 PM To: Michael Cc: forum.subscriber@iiug.org; ids@iiug.org Subject: Re: Relationship btw Tables? [2772] Hi, there's "delete on cascade". I'm not aware of anything like "stop on delete" or "cascade on update", etc. However, with IDS you have "Triggers". With these you can implement and customize much more. Since they offer realization of quite complex things, I recommend reading the manual (probably the three SQL manuals (Tutorial, Syntax, Reference). Regarding dbschema you might want to check that you used the "-ss" option. It will add Informix IDS specific things to the created statements, and some things you're looking for might be just these ... Regards, Martin -- Martin Fuerderer IBM Informix Development Munich Data Management Solutions "Michael" <michael@mikeymall.com> Sent by: forum.subscriber@iiug.org 01.04.2004 08:23 To ids@iiug.org cc Subject Relationship btw Tables? [2772] is it possible to set relationship constraints (stop on delete or cascade on update, etc) btw tables in informix? because recently we took over some informix database in a unix box. There's about 500 over tables in the database and when i did a schema, i noticed there's no relationship constraints betw a "MAIN" table and its "SUB" tables. So i was wondering if its because informix does not have this feature? I was using mssql and its common practise to set such relationship, thus i was puzzled when i saw this. Thanks

i see... thanks everybody for all the help!