April 23, 2015 at 11:28 am
Hello.
I have the Person table with the following fields:
Id_Person
Nm_Person
Id_AddressResidential
Id_AddressCommercial
Another table called Address with the following fields:
Id_Address
Ds_Street
I want to make the relationship between these tables so that when deleting a related field address in Person is set to null.
the SQL Server allows me to make this relationship in only one field.
A person can live and work in the same place. If I delete her address, I want the two Individual fields are set to null.
April 23, 2015 at 12:06 pm
wcarvalho (4/23/2015)
Hello.I have the Person table with the following fields:
Id_Person
Nm_Person
Id_AddressResidential
Id_AddressCommercial
Another table called Address with the following fields:
Id_Address
Ds_Street
I want to make the relationship between these tables so that when deleting a related field address in Person is set to null.
the SQL Server allows me to make this relationship in only one field.
A person can live and work in the same place. If I delete her address, I want the two Individual fields are set to null.
How are you currently blanking the value(s)?
April 23, 2015 at 12:23 pm
You have a bit of a normalization issue, which is complicating your issue. As DJJ pointed out, as of now with the structure in place you have to maually kill the relationship (i.e. null out the columns.)
The relationship between person and addresses is usually a "many to many" relation, meaning you'd use a separate "PersonAddress" table, with foreign keys to person and address. Assuming you did, then you can "cascade delete" the Entry in PersonAddress when the address is deleted.
With the stucture you have, you'd be restricted to using something like a trigger on delete to go remove those values from the preson table.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 23, 2015 at 4:31 pm
In that case I would need to have the table "type of address" to compose this table many to many
A business rule is that there will only be two addresses.
Introducing FOREIGN KEY constraint 'FK_Tb_Person_Tb_Address2' on table 'Tb_Person' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
April 23, 2015 at 4:34 pm
in the application
April 23, 2015 at 5:38 pm
Clearly - using the cascade on the person table will not be what you want (delete would take out the person itself). If the separate table isn't workable either, then your options become limited.
As of now - if you need this to be cleaned up synchronously, I think you're looking at a trigger (and not a particularly well performing one either). If you can let it sit for a few minutes you might consider a clean-up process that cleans up any "orphan ID's" every hour or so.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply