I'm using mySQL. I've been told that declaring a field as FOREIGN KEY defaults so that if all records that are dependent on that foreign key are deleted then the original record (i.e. the one with the field as a primary key) will also be deleted.

(Ammend: just to note obviously the original record is in another table, and dependent records can be in several different tables)

Is this correct and if not shall I just use CHECK(myfield = original.myfield) or can I change the setting of the actions of FOREIGN KEY to something that will leave the original record in tact while still checking that the foreign key exists??

I'm really unsure about your definition of FOREIGN KEY. In my mind, foreign keys are used to enforce a relation between a field and the fields in another meaning that the first field is bound to the values of the fields in the other table.

If you are talking about "on delete cascade", I'm not really sure mySQL defaults to that. SQL Server, the one that I currently use at work, doesn't default to cascaded delete. Oracle wasn't either. Apparently, it's not a feature RDBMSes like to enforce.

You should probably carefully read mySQL docs about that. They are very useful.

Cascade I know is another option, but that would delete all records if I deleted any 1 of the dependent records. What I'm talking about is if there are no more dependent records around then the original will then be deleted.

(bit like how COM/COM+ components are pooled)

I was just reading an article about SQL commands it may have been irrelevent to mySQL ^^ I just want to double check so none of my data goes missing ^^

Ah Just checked, the documentation I was reading was for XDB not mySQL... My bad. But still wanna double check this ^^ and also to check if mySQL has this built in too since it does sound like a handy thing to have (the delete if there are no more dependents ability)

One more thing, What is TPU using as its database?

Oh! :O I see what you mean now and that's really not what foreign keys are about

TPU is using PostgresSQL.

I always thought foreign keys only linked records in different tables. Till I read that thing where I found it can do more stuff ^^