Subject | Re: Help with a DELETE command |
---|---|
Author | robert_difalco |
Post date | 2004-12-05T23:16:11Z |
Let's talk about this database design a litte. If I am correct I think
that ON DELETE CASCADE is not available to me.
I'm simplifying this a big but I have a table of key, value pairs.
These records are collected into attribute sets. The Set Id is not
unique so it could not be the primary key, instead I created a
composite primary key of the SET_ID and the KEY_ID.
CREATE TABLE ATTRIBUTES (
F_SET_ID NUMERIC(18,0) NOT NULL,
F_KEY_ID NUMERIC(18,0) NOT NULL,
F_VALUE BLOB ...,
PRIMARY KEY( F_SET_ID, F_KEY_ID ) )
Now I have several tables that have records that would like to
reference SETS of attributes. For these I cannot make them a foreign
key so CASCADE is not available to me. These records merely reference
the F_SET_ID, however, since F_SET_ID is the first column in the
primary key, it also acts as an index.
So, near as I can tell, I only have three ways to delete these:
1. The EXISTS query Helen suggested or some sub-select
2. A Trigger
3. A quick query with the JOIN (the fastest for the query part) and
then iterate through each record in java and delete them one at a time.
There could easily be 9 million records in the ATTRIBUTES table. I
can't tell a huge different in speed between my original sub-select
and Helen's EXISTS query.
R.
that ON DELETE CASCADE is not available to me.
I'm simplifying this a big but I have a table of key, value pairs.
These records are collected into attribute sets. The Set Id is not
unique so it could not be the primary key, instead I created a
composite primary key of the SET_ID and the KEY_ID.
CREATE TABLE ATTRIBUTES (
F_SET_ID NUMERIC(18,0) NOT NULL,
F_KEY_ID NUMERIC(18,0) NOT NULL,
F_VALUE BLOB ...,
PRIMARY KEY( F_SET_ID, F_KEY_ID ) )
Now I have several tables that have records that would like to
reference SETS of attributes. For these I cannot make them a foreign
key so CASCADE is not available to me. These records merely reference
the F_SET_ID, however, since F_SET_ID is the first column in the
primary key, it also acts as an index.
So, near as I can tell, I only have three ways to delete these:
1. The EXISTS query Helen suggested or some sub-select
2. A Trigger
3. A quick query with the JOIN (the fastest for the query part) and
then iterate through each record in java and delete them one at a time.
There could easily be 9 million records in the ATTRIBUTES table. I
can't tell a huge different in speed between my original sub-select
and Helen's EXISTS query.
R.