Re: relations

"Martin A. Marques" wrote: > > On Wed, 28 Jun 2000, Obnoxio The Clown wrote: > > From: "Martin A. Marques" <martin@math.unl.edu.ar> > > > > > >I'm about to insert data into a database which has some relations between > > >three tables. > > > > > >base1.col2 -> base2.col1 (integers) > > >base1.col3 -> base3.col1 (integers) > > > > > >Now, my question is: when I insert data into the database, how do I > > >maintane > > >the relation between the inserts of table base1, base2 and base3? > > > > > >More clearer: base1 is the mail table of articles, and in base2 I put re > > >resumes, and in base3 the whole article. When I'm inserting a new article, > > >how do I keep the relation? > > > > Assuming I read your requirement correctly, http://www.informix.com/answers, > > you have base1 as master of base2 and base3, in which case > > http://www.informix.com/answers, you should insert first into base1 > > http://www.informix.com/answers, then base2 and base3. > > This only answers to one of my doubts. Do I have to do the relational > insertion at programing level? > > Example: > > INSERT INTO base1 VALUES (a,b,c,d,e) > INSERT INTO base2 VALUES (A,B,C) > INSERT INTO base3 VALUES (x,y)> > Now, I need b=A and c=x. I have to do this at a programing level? What are you asking? You of course should have performed either: INSERT INTO base1 VALUES (a,b,c,d,e); INSERT INTO base2 VALUES (b,B,C); INSERT INTO base3 VALUES (c,y); -OR- INSERT INTO base1 VALUES (a,A,x,d,e); INSERT INTO base2 VALUES (A,B,C); INSERT INTO base3 VALUES (x,y); Are you asking how to keep users from messing up or how to force compliance? The first problem is that your tables are non-relational. If the relationships are one-to-one then these columns should all be part of a single table. If the relationship between the tables is one to many then you should have instead: CREATE TABLE base1 ( a INTEGER PRIMARY KEY, d ...., e .... ); CREATE TABLE base2 ( a INTEGER FOREIGN KEY REFERENCES base1( a ), A INTEGER, B ...., C .... ); ALTER TABLE base2 ADD CONSTRAINT PRIMARY KEY ( a, A ); CREATE TABLE base3 ( a INTEGER FOREIGN KEY REFERENCES base1( a ), x INTEGER, y ... ); ALTER TABLE base3 ADD CONSTRAINT PRIMARY KEY (a, x ); If on the other hand there are many-to-many relationships, ie the base2 records can be referred to be many base 1 records and each base1 record can refer to many base 2 records then you need a gerund (a linkage table) to link each base1 row to all of the base2 rows it refers to and each base2 row to each of the base1 rows that refer to it: CREATE TABLE base1 ( a INTEGER PRIMARY KEY, d ...., e .... ); CREATE TABLE base2 ( A INTEGER PRIMARY KEY, B ...., C .... ); CREATE TABLE base1_to_2 ( a INTEGER FOREIGN KEY REFERENCES base1( a ), A INTEGER FOREIGN KEY REFERENCES base2( A ) ); Once the correct relationships are established the engine will take care of making sure they are all sensible and complete. Art S. Kagel Art S. Kagel