Newbie question about relational databse

[pcapelo]

Hi gang,

I have a newbie question about access. I have been played wit some basic database, but nothing really fancy.

What I would like to know what to do it is:

tableA
-taid
-taname
-tainfo
-tacolor

tableB
-tbid
-tbname
-tbinfo = taname

I dont know how to make that happen and I also would like to: if I delete tableA registry that the record on tableB (taname) also be deleted from tableB.

is that possible??? could anyone point me to the right direction???
I am using classic asp to interface with the database, and using access 2007



thank you in advance

PC

[Rawb]

Well, the tbinfo = taname link itself is fairly simple in Access, all you have to do is add each Table to the Relationships screen and then drag tableA.taname on top of tableB.tbinfo.

When you do that, you'll get a popup with a couple of options including "Cascade Delete Related Records." If you put a check in that, it'll handle deleting the Records from tableB automatically when you delete something from tableA.

I'm not sure if using ASP for the interface will override the "Cascade Delete Related Records" option or not (I've never modified an Access DB using an outside program, only read data from one), but I'm pretty sure things should still work automatically.

[pcapelo]

Hey Rawb,

I believe I did what you suggest but when I delete the registry from tableA it will also delete the whole registry on tableB. What I am looking for is to be able to delete registry from tableA and delete only the tbinfo = taname information, I want to keep the rest of the registry on tableb.

Is that possible??? what am I doing wrong here:

- I drag the registry from tablea (taname) to tableb (tbinfo);
- relationship is set to one to many;
- join type is set like: "Only include rows where the joined fields from both tables are equal";
- "Enforce Referential Integrity" is set as well "Cascade Delete Related Records"


thank you all in advance
PC

PS: my interface is ASP classic and, apparently the delete is working fine (since its delete all registry on tableb). lol

[Rawb]

I'm not sure I understand...

Do you mean that, when you delete the Record from tableA, you want to delete ONLY the contents of the tbinfo Field and leave the data in tbid and tbname there?

If that's what you want to do, there's no way to do that "automatically." The best thing to do is make a Function similar to the following (pseudocode):

function ClearData(taname)
  Run SQL "DELETE FROM tableA WHERE taname=" & taname
  Run SQL "UPDATE tableB SET tbinfo='' WHERE tbinfo=" & taname

[pcapelo]

Imagine that I have a relational database where I have this:

tableA
taid|taname|tainfo|tacolor
1|teste1|info1|black
2|teste2|info2|blue
3|teste3|info3|green

tableB
tbid|tbname|tbinfo
1|name1|teste1
2|name2|teste2
3|name3|teste3
4|name4|teste3


If i use foreign key and delete one registry from tableA this is what happen with tableB:

tableA
taid|taname|tainfo|tacolor
1|teste1|info1|black
2|teste2|info2|blue

tableB
tbid|tbname|tbinfo
1|name1|teste1
2|name2|teste2

I deleted one registry from tableA and access delete all registries on tableB that contained that foreign key.

but what I am trying to do is this:


tableA
taid|taname|tainfo|tacolor
1|teste1|info1|black
2|teste2|info2|blue


tableB
tbid|tbname|tbinfo
1|name1|teste1
2|name2|teste2
3|name3|
4|name4|

you see, I deleted one registry from tableA and in tableB only that information were deleted, I still have the rest of the information on the registry.


is that possible, and if it is how should I set the DB???

thank you again

[Rawb]

Gotcha!

Unfortunately, you can't set up something like that in the DB itself. You will need to write your own function to delete the data yourself. See my previous post for pesudocode on how to do this.

Generally speaking though, you don't want to do this, since it damages the integrity of the database by creating "orphan Records" (Records that don't relate to any other data in the database). Usually in situations like this, you either delete the entire Record or you add an additional Field that you can use to flag it as active/inactive:

tableA
taid|taname|tainfo|tacolor|isactive
1|teste1|info1|black|true
2|teste2|info2|blue|true
3|teste3|info3|green|true

Then, instead of deleting the Record from tableA, you just mark it as false in the isactive column:

tableA
taid|taname|tainfo|tacolor|isactive
1|teste1|info1|black|true
2|teste2|info2|blue|true
3|teste3|info3|green|false

This allows you to hide data from the user (just add "isactive=true" to your WHERE Clause) while keeping it available in case you need to look it up later (or add it back in).