does reference improve access time ?

Will adding a reference constraint to a column in a table improve my access time when I have millions of records in the child table and I ask for only those child records for a particular parent ? or do I have to add an index to that field also ? In the 80's, databases like UNIFY used to create linked lists that would allow you to navigate the chain of children starting from the parent. It added to the amount of space necessary to store that column, both in the parent table and in the child table. Is this how informix is set up, or do I only get a pointer from the child to the parent ? Also, when I create a reference pointing to the key of a parent table (which is unique, primary and possibly not null), the SQL syntax reference manual says that the children can exist with null values in that field. Yet when I tried to do this, I got error messages and the record wasn't loaded. Only when I had a record in the parent table that was null keyed (had to delete the not null constraint of course) was I able to add the child record with null in the reference field. I have IDS 7.3 running on linux 2.0.36 redhat

If you already have an index on the foreign key column then there is no speed advantage to adding the reference, only documentation gains. The constraint will add any needed index if it does not already exist and can use an existing index on the exact foreign key (whether ASC, DESC, or, if composite, mixed direction). dja7 wrote: > > Will adding a reference constraint to a column in a table improve my > access time when I have millions of records in the child table and I ask > for only those child records for a particular parent ? > > or do I have to add an index to that field also ? > > In the 80's, databases like UNIFY used to create linked lists that would > allow you to navigate the chain of children starting from the parent. > It added to the amount of space necessary to store that column, both in > the parent table and in the child table. Is this how informix is set > up, or do I only get a pointer from the child to the parent ? > > Also, when I create a reference pointing to the key of a parent table > (which is unique, primary and possibly not null), the SQL syntax > reference manual says that the children can exist with null values in > that field. Yet when I tried to do this, I got error messages and the > record wasn't loaded. Only when I had a record in the parent table > that was null keyed (had to delete the not null constraint of course) > was I able to add the child record with null in the reference field. > > I have IDS 7.3 running on linux 2.0.36 redhat

On Wed, 25 Aug 1999 10:20:38 -0700, dja7 <dja7@jps.net> wrote: >Will adding a reference constraint to a column in a table improve my >access time when I have millions of records in the child table and I ask >for only those child records for a particular parent ? > >or do I have to add an index to that field also ? > Informix automatically adds a supporting index to foreign key constraints. This improves access to children and also ensures that 'orphan' children do not get created (inserting orphans or deleting parents). >In the 80's, databases like UNIFY used to create linked lists that would >allow you to navigate the chain of children starting from the parent. >It added to the amount of space necessary to store that column, both in >the parent table and in the child table. Is this how informix is set >up, or do I only get a pointer from the child to the parent ? > >Also, when I create a reference pointing to the key of a parent table >(which is unique, primary and possibly not null), the SQL syntax >reference manual says that the children can exist with null values in >that field. Yet when I tried to do this, I got error messages and the >record wasn't loaded. Worked fine on my instance (7.30uc7 on Redhat 6.0). > Only when I had a record in the parent table >that was null keyed (had to delete the not null constraint of course) >was I able to add the child record with null in the reference field. > >I have IDS 7.3 running on linux 2.0.36 redhat Rudy