delete stored procedure for relationship m-m

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

delete stored procedure for relationship m-m

Author

Topic

dg78
Starting Member

5 Posts

Posted - 2011-01-08 : 05:07:18

Hi,

I have the tables :
- Customer (CustomerId, Name, ..)
- CustomerAddress (CustomerId, AddressId, TypeAddress)
- Address (AddressId, AddressLine1, AddressLine2, ZipCode, City, ..)

TypeAddress refers to a TypeAddress table.

One customer can have several addresses which differ by TypeAddress.
One address is only for one customer.

I use a relationship many to many because I have also the tables (with the same Address table) :
- Supplier (Supplier Id, Name, ..)
- Supplier Address (Supplier Id, AddressId, TypeAddress)
- Address (AddressId, AddressLine1, AddressLine2, ZipCode, City, ..)

When I delete a customer, I need to delete all Addresses of this customer.
It is to said : delete in tables Address, CustomerAddress and Customer.
I wish to do that in a stored procedure.

Thanks for your help.

Dominique

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-08 : 13:15:50

wouldnt it be better to implement it in a trigger? if not you might have to implement logic to call the procedure along with delete and make sure they all are part of transaction

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dg78
Starting Member

5 Posts

Posted - 2011-01-08 : 13:32:24

Perhaps a trigger is better. I prefer implement logic in a procedure.
How do people do to solve that ?
Have you some codes or some links ?
Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-08 : 13:43:10

you mean trigger or proc?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dg78
Starting Member

5 Posts

Posted - 2011-01-08 : 17:23:55

Thanks for your answer.

I prefer a proc, so if you have some piece of code for a proc it will be good.
But if you have also a piece of code for triggers, it is the best so I can see how to do with trigger and I can choose.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-08 : 17:56:12

http://msdn.microsoft.com/en-us/library/ms189799.aspx

BOL for Create Trigger.

Poor planning on your part does not constitute an emergency on my part.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-01-10 : 17:31:27

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

CREATE TABLE Customers
(customer_id CHAR(10) NOT NULL PRIMARY KEY,
customer_name VARCHAR(35) NOT NULL,
..);

CREATE TABLE Addresses
(address_id CHAR(10) NOT NULL PRIMARY KEY,
address_line1 VARCHAR(35) NOT NULL,
address_line2 VARCHAR(35),
zip_code CHAR(5) NOT NULL
CHECK(zip_code LIKE '[0-9][0-9][0-9][0-9][0-9]),
city_name VARCHAR(20) NOT NULL, ..)

CREATE TABLE CustomerAddresses
(customer_id CHAR(10) NOT NULL
REFERENCES Customers (customer_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
address_id CHAR(10) NOT NULL
REFERENCES Addresses (address_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
UNIQUE (customer_id, address_id),
UNIQUE (customer_id, address_type),
address_type CHAR(4) NOT NULL
CHECK(address_type IN (..)));

>> address_type refers to a address_type table. <<

Bad design; use a CHECK() instead. There are not that many values and they are not going to change.

>> One customer can have several addresses which differ by address_type.
One address [of each type?] is only for one customer. <<

Here is where I have to guess about the uniqueness. The specifications are vague, so I went with the second sentence, which says this is 1-to-1 which is not want you said in the title of this posting.

>> When I delete a customer, I need to delete all Addresses of this customer. .. I wish to do that in a stored procedure. <<

No, you want to use DRI actions and not procedural code.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

dg78
Starting Member

5 Posts

Posted - 2011-01-11 : 17:59:39

Hello Joe,

Thanks for your answer.

I tried your code. It is fine about tables, FKs and the diagram is good.

Then I insert some data :


INSERT INTO dbo.Addresses ( [address_id],[address_line1],[address_line2],[zip_code],[city_name]) VALUES  (1,'l11','l21',12345,'c1')
INSERT INTO dbo.Addresses ( [address_id],[address_line1],[address_line2],[zip_code],[city_name]) VALUES  (2,'l12','l22',12345,'c2')
INSERT INTO dbo.Addresses ( [address_id],[address_line1],[address_line2],[zip_code],[city_name]) VALUES  (3,'l13','l23',12345,'c3')

INSERT INTO dbo.[Customers] ([customer_id],[customer_name]) VALUES (1,'Bill')
INSERT INTO dbo.[Customers] ([customer_id],[customer_name]) VALUES (2,'Steve')

INSERT INTO dbo.[CustomerAddresses] ([customer_id],[address_id],[address_type]) VALUES (1,1,1)
INSERT INTO dbo.[CustomerAddresses] ([customer_id],[address_id],[address_type]) VALUES (1,3,2)
INSERT INTO dbo.[CustomerAddresses] ([customer_id],[address_id],[address_type]) VALUES (2,2,1)

Then I delete the customer having customer_id=1 :

DELETE Customers
WHERE customer_id = 1

And .. it is not good :
- the customer 1 is deleted : good
- the two records in CustomerAddresses with customer_id=1 are deleted : good
- but none record in Addresses are deleted : not good

Test it and you will agree with me.

I think the DRI is not good in this case.

I created this stored procedure by search in google :


CREATE PROCEDURE [usp_Customer_Delete]
(
	@Id smallint
)
AS
	SET NOCOUNT ON
	BEGIN TRANSACTION
	
		delete a
		from Customers c
		inner join CustomerAddresses j on j.customer_id = c.customer_id
		inner join Addresses a on a.address_id = j.address_Id 
		WHERE c.customer_id = @Id

		DELETE j
		from Customers c
		inner join CustomerAddresses j on j.customer_id = c.customer_id
		WHERE c.customer_id = @Id

		delete c
		from Customers c
		WHERE c.customer_id = @Id
	
	COMMIT
	
	RETURN @@Error
GO

I execute this stored procedure and now it is good in the three tables.

I think that it is also possible to have a good solution with triggers.

Best regards

Dominique

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-01-11 : 18:49:53

>> And .. it is not good :
- the customer 1 is deleted : good
- the two records [sic: rows] in CustomerAddresses with customer_id=1 are deleted : good
- but no record [row] in Addresses are deleted : not good <<

I assumed that more than one person can be at an address. If I die, do you wish to stop doing business with my wife? :)

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

dg78
Starting Member

5 Posts

Posted - 2011-01-12 : 04:55:35

>>I assumed that more than one person can be at an address.<<

In my sample :
- the customer Bill has two addresses
- an address is only for one customer

Even if an address is for several customers, the situation is the same : DRI is not good in this case.

Send my regards to your wife ;))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources