Hi all. I have a question that is similar, but slightly different, than the last question posted about foreign keys.
I have a table cms_areas which will hold area labels, replicating the folders which make up our website. Here's the structure:
CREATE TABLE "cms_areas" (
"area_id" SERIAL PRIMARY KEY,
"parent" integer REFERENCES cms_areas ON UPDATE CASCADE ON DELETE CASCADE,
"name" character varying(255) NOT NULL,
"location" character varying(1000) NOT NULL,
"depth" integer DEFAULT 0 NOT NULL,
"max_history" integer DEFAULT 3 NOT NULL
I want to set up parent as a foreign key to area_id, such that if I have a record with area_id = 1 and parent is null, and another record with area_id 2 and parent = 1, then if the record with area_id = 1 for some reason has its area_id updated, the parent would also update.
While myself I cannot see any situations in which this would arise, if someone can, I would be grateful for some enlightenment here.
What I'm wondering, is since I've not set parent to NOT NULL (since the primary area which will represent the root of our site will not have a parent), will this have any effect on setting parent as a foreign key to the primary key in the same table?
I think in writing out this question I've actually answered my own question, as it seems logical that it wouldn't, but I'm still relatively green in the world of db design and administration, so I value more experienced members' input.
Thanks in advance,