Relationships? Cascading deletion. 2

[gillockb]

I'm not really sure that a relationship is what I need. I have two tables. The first holds Employee core information. The second holds instructor information, course taught, date qualified, etc. Not all employees are instructors, so normalization rules as I understanding dictate keeping the information in two different tables. Logically I'm using employeeID in both tables and want to relate them so that when I delete a record from the core table the database removes the matching records from the instructor table.

I tried to make a relationship, assuming that if I relate primary keys from both tables aht will be all I need to do. It is giving me an error, so I guess I don't know what I'm doing. Can someone please assist me in this?

TYIA

 

[jbenson001]

what is the error?

 

[fredericofonseca]

cascade delete works with a relation of primary_key->foreign_key. Loot at your books online help

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[gillockb]

Okay. I was able to get at one to work. Not sure what I did. So now I have one table, my core table, related to about 20 other tables all using a UserID field. But there is still one table I can't get to link. I'm not sure breaking these into two tables was a good idea or not. I was thinking I would rather have two smaller tables than one large table. Just thought it'd be more efficient since I don't always need personal information on someone. I have a table with core or basic information, and I have another that has phone numbers and addresses and such. It is the only table I can't get to relate. This is the error I'm getting...

'PersonnelCoreInfo' table saved successfully
'PersonnelPersonalInfo' table
- Unable to create relationship 'FK_PersonnelPersonalInfo_PersonnelCoreInfo'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint 'FK_PersonnelPersonalInfo_PersonnelCoreInfo'. The conflict occurred in database '533_Development', table 'PersonnelCoreInfo', column 'UserID'.

I'm reading there is a problem with the core table, but as I said, it is linked to about 20 other tables, so I'm thinking that can't be it. One thing I find puzzling is I have set no Foreign Keys. I didn't think I had need for them. I'm currently looking for information refered to by Mr. Fonseca (thank you sir).

Thanks!

 

[TGandon]

This error occurs because you have your relationship the wrong way round.
i.e. Each record in PersonnelCoreInfo would be dependant on a related record in PersonnelPersonalInfo, but PersonnelPersonalInfo has no data and PersonnelCoreInfo has.

If you are creating the relationship using a diagram, click on the PersonnelCoreInfo UserId and drag it to PersonnelPersonalInfo UserId.
This defines PersonnelPersonalInfo records are dependant on related PersonnelCoreInfo records.

In database terminology, PersonnelPersonalInfo has a "Foreign Key" constraint (i.e. its relationship with PersonnelCoreInfo).

Hope this explains it

Trevor

 

[TGandon]

SQL Server does not have an equivalent to the MS-Access Cascade Delete.

You can explicitly
"DELETE FROM PersonnelPersonalInfo WHERE UserId = x"
followed by
"DELETE FROM PersonnelCoreInfo WHERE UserId = x"

Or create a stored procedure to which you pass the UserId and it performs the deletes

Trevor

 

[jbenson001]

SQL Server does not have an equivalent to the MS-Access Cascade Delete.

From Books On Line

ON DELETE CASCADE

Specifies that if an attempt is made to delete a row with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are also deleted. If cascading referential actions have also been defined on the target tables, the specified cascading actions are also taken for the rows deleted from those tables.

 

[TGandon]

SQL Server does not have an equivalent to the MS-Access Cascade Delete

Sorry about that.

I was working from home and only had access to SQL 7 books online.

Even so I would still advise caution in using it.
I find that having to explicitly delete "child" records prior to the "parent" ensures that I cannot inadvertently delete the "parent" if it has "children".

Trevor

 

[gillockb]

Thank you all for the great information. It's a slow, painful process to get information in this thick skull of mine. Your time and patience is greatly appreciated.