[rt-devel] primary keys on table

[Vivek Khera]

I was just looking into why some queries were really slow on my RT,
and I find that the current schema.Pg lists primary keys for nearly
every table, but my database (updated from earlier versions) has not
these primary keys!

it seems that the insertdata script doesn't add them, either (should
it?)

Anyhow, here's the alter table's for them:

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);


Then, we can add some referential constraints so that we can trivially
delete dead tickets with one blow:

begin;
delete from Tickets where Status='dead';
commit;

and the DB takes care of the rest.

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;