pears.Collection

Links individual objects within a Collection.

• CollectionID

• No indexes found.

-- IQX database structure split by table
-- Source: IQXDatabaseStructure - with comments.sql
-- Table: "pears"."Collection"
-- Table comment: Links individual objects within a Collection.
-- Statement count: 9
 
CREATE TABLE "pears"."Collection" (
    "CollectionID"                   CHAR(20) NOT NULL
   ,"Parent"                         CHAR(20) NULL
   ,"Name"                           CHAR(60) NOT NULL
   ,"staffid"                        CHAR(20) NULL
   ,"IDType"                         CHAR(2) NULL
   ,"ID"                             CHAR(20) NULL
   ,"CollectionTypeID"               CHAR(20) NULL
   ,"WhenCreated"                    TIMESTAMP NULL DEFAULT CURRENT TIMESTAMP
   ,"WhoCreated"                     CHAR(20) NULL
   ,"WhenArchived"                   TIMESTAMP NULL
   ,"WhoArchived"                    CHAR(20) NULL
   ,"DateFrom"                       DATE NULL
   ,"DateTo"                         DATE NULL
   ,"Notes"                          long VARCHAR NULL
   ,"OtherNotes"                     long VARCHAR NULL
   ,"sortorder"                      INTEGER NULL
   ,"IsTemplate"                     SMALLINT NOT NULL DEFAULT 0
   ,PRIMARY KEY ("CollectionID" ASC) 
)
GO
 
 
COMMENT ON COLUMN "pears"."Collection"."IDType" IS 
	'H=header P=person C=company E=employment V=vacancy L=placement T=timesheet CE=contact Event R=progress I=invoice TQ=timesheet query'
GO
 
 
COMMENT ON COLUMN "pears"."Collection"."IsTemplate" IS 
	'Only applies to top level'
GO
 
 
COMMENT ON TABLE "pears"."Collection" IS 
	'Links individual objects within a Collection.'
GO
 
 
ALTER TABLE "pears"."Collection"
    ADD FOREIGN KEY "staff" ("staffid" ASC)
    REFERENCES "pears"."staff" ("staffid")
GO
 
 
ALTER TABLE "pears"."Collection"
    ADD FOREIGN KEY "CollectionType" ("CollectionTypeID" ASC)
    REFERENCES "pears"."CollectionType" ("CollectionTypeID")
    ON DELETE CASCADE
GO
 
 
ALTER TABLE "pears"."Collection"
    ADD FOREIGN KEY "CollectionParent" ("Parent" ASC)
    REFERENCES "pears"."Collection" ("CollectionID")
    ON DELETE SET NULL
GO
 
 
CREATE TRIGGER "CollectionDelete" BEFORE DELETE ORDER 1 ON
"pears"."Collection"
REFERENCING OLD AS "old_name"
FOR each ROW
BEGIN
  DELETE FROM "collection" WHERE "parent" = "old_name"."collectionid"
END
GO
 
 
COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."Collection"."CollectionDelete" IS 
{CREATE TRIGGER CollectionDelete 
 BEFORE DELETE ORDER 1 ON
pears.Collection
REFERENCING OLD AS old_name
FOR each ROW
BEGIN
  DELETE FROM collection WHERE parent = old_name.collectionid
END
}
GO