Delete across tables

hi,

I have two tables that are related, ie I created them with;

create table cm_message (
msgid varchar(40) not null primary key,
location varchar(240),
ts timestamp default 'now' not null,
lastsent timestamp
);

create table cm_data (
pkey integer not null primary key,
subdata varchar(255),
msgid varchar(40),
foreign key (msgid) references cm_message(msgid)
);

basically for each entry in cm_message there can be several cm_data entries and they're linked using the msgid fields.

I'm trying to write a purge script that will delete entries (in cm_message and cm_data) that have a cm_message.ts timestamp older than n hours. Can I do a 'delete from ... where cm_message.ts > n' which does some kind of union between the two tables and delete entries from both tables at one stroke?

At the moment I'm looking at selecting all old entries from cm_message and deleting all in cm_data for each msgid, but there must be a more efficient way of using the relational stuff...

thanks,
nikUse the ON DELETE CASCADE option:
...foreign key (msgid) references cm_message(msgid) ON DELETE CASCADE :rolleyes:|||which database system is this? because i don't know of any that will support this --timestamp default 'now'|||thanks LKBrwn_DBA.

rudy, the database is firebird - I think it also accepts TODAY, TOMORROW and YESTERDAY which is nice and handy...

nik|||wow, ya learn sumpin new every day ;)

thanks nik|||Hi,

Does it work on a MySQL database?

//M|||Does it work on a MySQL database?the ON DELETE CASCADE? only for InnoDB tables

default values for database fields yes or no??

Building the database I have come across different databases some that add a default value for every field and some that don't. I feel it is a hassle to add a default value, keep track if it is added.

I guess with a default value there would be no "NULL" values in the database but one could also make sure in the C# code that all the fields have a value when inputed and on the way out check for nulls.

What is the right way??

Pros and cons.......

Newbie

Like a table that holds car information. If you have a field that holds the number of wheels, I would make that a default of 4.

Sort of like, if you don't mention how many wheels the car has, I'm going to assume 4. Now if the car has more/less than 4, you can tell me about it, and I'll remember.

It's not really what is "right" and what is "wrong". You can also say there is no default, and you don't tell me the number of wheels of every car, I'm not going to accept it. It forces you to make a choice for every record. Or you can have each record allow nulls, in which case if you don't mention it, we'll still accept it, but we won't make any assumption on the number of wheels.

My rule of thumb is, if it's necessary field, then no default value, and does not accept nulls.

If you can assume a value if one isn't specified (It rarely isn't a particular value), then I'll make it does not accept nulls, with a default value.

If I really don't care about the field at all, and it's a fluff field that I won't use, then I'll accept nulls and no default.