[rt-users] Delete tickets

[Vivek Khera]

>>>>> "FR" == Frances Russell <frussell at msi.com.au> writes:

>> If you use Postgres, you can add the foreign keys I posted a couple of
>> weeks ago and then just do a delete from tickets for dead tickets and
>> it will cascade down to the other tables.

FR> Can you point me to where I might find the postings you mention?


Here's the full set of indexes I had to add.  Somewhere along the line
the primary keys were not added to my DB during the various upgrades,
so if you already have them, you don't need to recreate them.  The
foreign keys, though, are useful for making ticket deletion easy.

My main wish is that a complete set of foreign keys be added to the
schema so that we can clearly see what all the relations are, and
ensure that they are valid at all times.


ALTER TABLE KeywordSelects ADD PRIMARY KEY (id);
ALTER TABLE Attachments ADD PRIMARY KEY (id);
ALTER TABLE Queues ADD PRIMARY KEY (id);
ALTER TABLE Links ADD PRIMARY KEY (id);
ALTER TABLE Groups ADD PRIMARY KEY (id);
ALTER TABLE Watchers ADD PRIMARY KEY (id);
ALTER TABLE ScripConditions ADD PRIMARY KEY (id);
ALTER TABLE Transactions ADD PRIMARY KEY (id);
ALTER TABLE Scrips ADD PRIMARY KEY (id);
ALTER TABLE ACL ADD PRIMARY KEY (id);
ALTER TABLE GroupMembers ADD PRIMARY KEY (id);
ALTER TABLE ObjectKeywords ADD PRIMARY KEY (id);
ALTER TABLE Keywords ADD PRIMARY KEY (id);
ALTER TABLE Users ADD PRIMARY KEY (id);
ALTER TABLE Tickets ADD PRIMARY KEY (id);
ALTER TABLE ScripActions ADD PRIMARY KEY (id);
ALTER TABLE Templates ADD PRIMARY KEY (id);


ALTER TABLE Transactions ADD CONSTRAINT transfk1 FOREIGN KEY (Ticket) REFERENCES Tickets(id) MATCH FULL ON DELETE CASCADE;
ALTER TABLE Attachments ADD CONSTRAINT attachfk1 FOREIGN KEY (TransactionID) REFERENCES Transactions(id) MATCH FULL ON DELETE CASCADE;
ALTER TABLE Watchers ADD CONSTRAINT watchfk1 FOREIGN KEY (Value) REFERENCES Tickets(id) MATCH FULL ON DELETE CASCADE;
ALTER TABLE ObjectKeywords ADD CONSTRAINT objectfk1 FOREIGN KEY (ObjectId) REFERENCES Tickets(id) MATCH FULL ON DELETE CASCADE;
ALTER TABLE Links ADD CONSTRAINT linksfk1 FOREIGN KEY (LocalTarget) REFERENCES Tickets(id) MATCH FULL ON DELETE CASCADE;
ALTER TABLE Links ADD CONSTRAINT linksfk2 FOREIGN KEY (LocalBase) REFERENCES Tickets(id) MATCH FULL ON DELETE CASCADE;