php-db | 2000121

[PHP-DB] <PK><FK> From: Anthony Martin (Anthony <email protected>)
Date: 12/11/00

By the way, this is on an Interbase database, but the logic should work for
any properly written referential database...

I'm having trouble figuring out this referential logic. I'm writing a
rudimentary directory structure for dividing HTML sections from one another.
Right now, my tables have no referential integrity, except for ones
pertaining outside the directory structure (those being profiles and
groups). My problem is that I cannot find a way to declare primary keys and
hence foreign keys yet maintain the functionality I'm looking for.
 
Here are my tables:
 
CREATE TABLE "DIRECTORY" (
  "DEPTH" INTEGER NOT NULL,
  "NAME" VARCHAR(32) NOT NULL,
  "PROFILE_ID" VARCHAR(16),
  "GROUP_ID" VARCHAR(16),
  "PERMISSION" CHAR(9),
  "CREATE_DT" TIMESTAMP,
  "MODIFY_DT" TIMESTAMP,
  CONSTRAINT "DIRECTORY_PROFILE_FK" FOREIGN KEY ( "PROFILE_ID" ) REFERENCES
"PROFILE" ( "ID" ),
  CONSTRAINT "DIRECTORY_GROUP_FK" FOREIGN KEY ( "GROUP_ID" ) REFERENCES
"GROUP" ( "ID" )
) ;

GRANT SELECT, INSERT, UPDATE, DELETE ON "DIRECTORY" TO "PHP" ; 

---
CREATE TABLE "PARENT_DIRECTORY" (
  "PARENT_DIR_DEPTH" INTEGER NOT NULL,
  "PARENT_DIR_NAME" VARCHAR(32) NOT NULL,
  "CHILD_DIR_DEPTH" INTEGER NOT NULL,
  "CHILD_DIR_NAME" VARCHAR(32) NOT NULL
) ;

GRANT SELECT, INSERT, UPDATE, DELETE ON "PARENT_DIRECTORY" TO "PHP" ;

---
The records that get inserted would be as follows:
 
INSERT INTO "DIRECTORY" (
   "DEPTH", "NAME", "PROFILE_ID", "GROUP_ID", "CREATE_DT", "MODIFY_DT"
) VALUES (
  0, '%ROOT%', 'SYSTEM', 'SYSTEM', 'NOW', 'NOW'
) ;

INSERT INTO "DIRECTORY" (
  "DEPTH", "NAME", "PROFILE_ID", "GROUP_ID", "CREATE_DT", "MODIFY_DT"
) VALUES (
  1, 'ANTHONY', 'SYSTEM', 'SYSTEM', 'NOW', 'NOW'
) ;

INSERT INTO "PARENT_DIRECTORY" (
  "PARENT_DIR_DEPTH", "PARENT_DIR_NAME", "CHILD_DIR_DEPTH", "CHILD_DIR_NAME"
) VALUES (
  0, '%ROOT%', 1, 'ANTHONY'
) ;

---
The answer is staring me in the face: use artificial primary keys.  But I've
gotten away with avoiding them for the whole project.  As you can see, I
avoid artificial primary keys by using the depth and name combination.  But
in order to have multiple directory entries in the same depth, I have to
allow non-unique entries. To allow non-unique entries, I can't have primary
keys. If I can't have primary keys, I can't have foreign keys pointing to
them.

Any thoughts?
 
 
Anthony