Primary keys on table

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;

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!

I seem to recall that these got added somewhere in the 2.0.x series

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

I’d be curious to know if this is still the case.

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;

Slick. I can’t wait until mysql actually supports this :wink:

»|« http://www.bestpractical.com/rt – Trouble Ticketing. Free.

“JV” == Jesse Vincent jesse@bestpractical.com writes:

ALTER TABLE ObjectKeywords ADD CONSTRAINT objectfk1 FOREIGN KEY (ObjectId) REFERENCES Tickets(id) MATCH FULL ON DELETE CASCADE;

JV> Slick. I can’t wait until mysql actually supports this :wink:

Someone here (or on rt-users) swore up and down that MySQL supports
referential integrity via foreign keys now. I have no clue.

I’m sure there are other relationships that could have foriegn keys
added, but I don’t need them…

I checked the rt 2.0.14 insertdata script and it doesn’t appear to
have any alter tables for primary keys.

Someone here (or on rt-users) swore up and down that MySQL supports
referential integrity via foreign keys now. I have no clue.

It supports defining the relationships, but doesn’t actually do anything
with them.

I checked the rt 2.0.14 insertdata script and it doesn’t appear to
have any alter tables for primary keys.

Right. but they were added to the schema file. A new install should
insert them from that.

»|« http://www.bestpractical.com/rt – Trouble Ticketing. Free.

At 15:24 18/10/2002 -0400, Jesse Vincent wrote:>On Fri, Oct 18, 2002 at 03:13:38PM -0400, Vivek Khera wrote:

Someone here (or on rt-users) swore up and down that MySQL supports
referential integrity via foreign keys now. I have no clue.

It supports defining the relationships, but doesn’t actually do anything
with them.

The manual implies that this now works for InnoDB’s:

Source: http://www.mysql.com/doc/en/SEC447.html

Starting from version 3.23.50 you can also associate the ON DELETE CASCADE
or ON DELETE SET NULL clause with the foreign key constraint.

If ON DELETE CASCADE is specified, and a row in the parent table is
deleted, then InnoDB automatically deletes also all those rows in the child
table whose foreign key values are equal to the referenced key value in the
parent row. If ON DELETE SET NULL is specified, the child rows are
automatically updated so that the columns in the foreign key are set to the
SQL NULL value.

Must admit I haven’t tried though…

Olly