Collection of foreign keys?
Is it possible in Informix v9.1 to define a collection (SET, LIST ,..) that
has values that are foreign keys?
Something like:
CREATE TABLE address
(
oid INTEGER,
street VARCHAR(20),
number INTEGER,
PRIMARY KEY (oid)
);
CREATE TABLE person
(addresses SET (INTEGER REFERENCES address (oid) ) <--- A COLLECTION OF
ADDRESS OIDS
)
Thanks in advance.
Juan Ignacio Saitua.
In article <3a75dd16$1@dnewserver.firstcom.cl>, "Juan Ignacio Saitua" <jisaitua@cge.cl> wrote: > Is it possible in Informix v9.1 to define a collection (SET, LIST ,..) that > has values that are foreign keys? > Something like: No. Not at this time. But depending on the kind of problem you're trying to solve, there are other ways to go. 1. If you have an N:M relationship, then I would suggest using the more traditional relational approach: create a resolution table. In the general case, this has some performance and scalability advantages over the more denormalized approach. Using a resolution table give the optimizer more options than the denormalized structure, and updates of a relationship between two objects will not lock the row being referred from (only a row in the resolution table). 2. Fairly frequently, folk want to use this kind of data modeling pattern to ensure that the values in the SET are "valid". For example, they want to ensure that the set of values in the set are valid codes. In many cases, instead of enforcing the validity rules using foreign keys, it can be a good idea to create a new type that can be used to represent the values, and then enforce the integrity constraints in the code managing the type. This is similar to concepts like a 'C' enumerated type. I've done this with things like US State codes, FIPS codes, and so on. Of course, if the set of values is just a big old list without any internal consistency, this isn't workable. You can't usually do parts catalogs this way, for instance. 3. If you really, *really* want to do this (and I wouldn't recommend it generally for the reasons listed in #1) then you can always create a type to hold the set of values, and write your own integrity checks in the code used to maintain values of the type. This is, more or less, exactly what IFMX would do if it were required to support the idea, although the syntax used would probably be more seamlessly integrated than is possible using the type mechanism. Hope this helps! I would advise using a resolution table. KR Pb Sent via Deja.com http://www.deja.com/
Ok, so a lookUp table is the solution for rigth now. Thanks! > No. Not at this time. But depending on the kind of problem you're ^^^^^^^^^^^^^^^^^^ Do you know if Informix is planning to put such a feature in a next release? Regards, Juan Ignacio Saitua.