Let me start by saying I understand that postgresql does not support the following: composite data types with individual components acting as foreign keys, arrays of composite data types, and arrays with elements acting as foreign keys. I will layout my example using them for clarity even though they don't exist Ok let me layout a rough example of my problem: CREATE TABLE target_node1 ( id int4, value text ); CREATE TABLE target_node2 ( id int4, value text ); CREATE TYPE composite1 ( idx int4, reference int4 ); CREATE TYPE composite2 ( idx int4, reference1 int4 reference2 int4 ); CREATE TABLE example_table ( id int4, value test, type1 composite1[]; type2 composite2[]; ); ALTER TABLE example_table ADD FOREIGN KEY ((composite1).reference) REFERENCES target_node1 (id); ALTER TABLE example_table ADD FOREIGN KEY ((composite2).reference1) REFERENCES target_node1 (id); ALTER TABLE example_table ADD FOREIGN KEY ((composite2).reference2) REFERENCES target_node2 (id); In addition I want a UNIQUE check constraint on INSERT and UPDATE that first checks that the text in the value column is unique. If that fails then I need to check that the type1 and type2 arrays are unique and act accordingly. Yes I understand all of that is completely wrong, but hopefully it clarifies my intentions. Essentially, I want a list(s) of a custom type(hopefully composite type) where individual data members of the type are foreign keys to other tables. Also I need to use those custom types for uniqueness in combination with other information in the table. The solutions as I see it: 1. Parallel arrays for the data members of the types. In other words an array for composite1.idx, an array for composite1.reference and so on. Then I could take the array of interest and create triggers that would allow the array to act as a set of foreign keys. However, this is slow when updating or deleting from a table like target_node1. Also it just seems accident prone given that the values are all separate from each other, and hardly human understandable. 2. Normalize this and move the composite type into its own table that references example_table. It is a many-to-one relationship after all, and I am sure this is the route that will probably be suggested by you guys. However... if I do that how the hell do I do maintain my uniqueness check on insert into example table given that half of the data I am checking on is in another table, and hasn't been inserted yet for the new row? I am totally stuck in regards to this... 3. Something with truly custom data types(aka C functions loaded into the back end). To be honest I don't even know how possible that is or how much work that would take. I have defined my own custom types before, but not any indexing functions for them. What would I have to define beyond the type itself? How would the foreign keys work in this situation, if at all? Help! This is seriously important to my project... but naturally the less complicated the solution the better. Thanks in advance, Morgan
