This question specifically deals with PostrgreSQL and other
SQL-compliant databases. I say this, because the question deals with
foreign keys and constraints, which I'm pretty sure MySQL doesn't deal
with properly, if at all.
I have the following table, which most other tables reference:
hosts=# \d machines
Table "public.machines"
Column | Type | Modifiers
--------------+--------------------------+---------------------------------
id | integer | not null default nextval('ids'::text)
itemtag | text | not null
model | integer | not null
location | integer | not null
monitor_temp | boolean | not null
serial_number | character varying(64) |
Indexes:
"machines_pkey" PRIMARY KEY, btree (id)
"machines_itemtag_key" UNIQUE, btree (itemtag)
Foreign-key constraints:
"machines_model_fkey" FOREIGN KEY (model) REFERENCES machine_models(id)
"machines_location_fkey" FOREIGN KEY ("location") REFERENCES locations(id)
And this table:
hosts=# \d classes
Table "public.classes"
Column | Type | Modifiers
--------------+------------------------+---------------------------------
id | integer | not null
class | text | not null
Indexes:
"classes_pkey" PRIMARY KEY, btree (id)
I want to create a table which has the following:
hosts=# \d class_members
Table "public.class_members"
Column | Type | Modifiers
--------------+------------------------+---------------------------------
id | integer | not null
member | text | not null
Indexes:
"classes_members_id_pkey" PRIMARY KEY, btree (id)
However, I want to restrict the member column by restricting the data
in it to also exist *either* in machines.id *OR* in classes.id. The
reason for this is that a class member can either be a machine or
another class (think netgroups here). Does anyone know how to do
this, or if it's even possible?
I suppose one solution is to just not have nested classes and
explicitly list each machine that's a member of any given class as
such, but, well, that's not overly elegant :)
Thanks for any insight.
--
Seeya,
Paul
_______________________________________________
gnhlug-discuss mailing list
gnhlug-discuss@mail.gnhlug.org
http://mail.gnhlug.org/mailman/listinfo/gnhlug-discuss
