sql server programming: The cascaded delete is easier to manage and involves no code:

ALTER TABLE [dbo].[Books] ADD
CONSTRAINT [FK_Books_Authors] FOREIGN KEY
(
[AuthorID]
) REFERENCES [dbo].[Authors] (
[AuthorID]
ON DELETE CASCADE
)


--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
[quoted text, click to view]
Hi there,

for testing purposes I've created two tables, Authors and Books (table defs
at the end of the message). I have a one to many relationship between them
(1 Author can have n Books).

My question is: how do I delete an Author from the table? Do I have to start
a transaction, remove the books (of this author), then the author and
commit? Is there a better way? Shall I set it up altogether different (I've
seen the Cascading delete option, but I'm not really sure whether to use it
or not)?

Would be great if somebody could shed some light.

Thanks in advance.

/matthias

--Authors Table
CREATE TABLE [dbo].[Authors] (
[AuthorID] [int] IDENTITY (1, 1) NOT NULL ,
[FirstName] [varchar] (50) NOT NULL ,
[LastName] [varchar] (50) NOT NULL
) ON [PRIMARY]

ALTER TABLE [dbo].[Authors] WITH NOCHECK ADD
CONSTRAINT [PK_Authors] PRIMARY KEY CLUSTERED
(
[AuthorID]
) ON [PRIMARY]

--Books Table

CREATE TABLE [dbo].[Books] (
[BookID] [int] IDENTITY (1, 1) NOT NULL ,
[Title] [varchar] (50) NOT NULL ,
[AuthorID] [int] NOT NULL
) ON [PRIMARY]

ALTER TABLE [dbo].[Books] WITH NOCHECK ADD
CONSTRAINT [PK_Books] PRIMARY KEY CLUSTERED
(
[BookID]
) ON [PRIMARY]

-- Relationship
ALTER TABLE [dbo].[Books] ADD
CONSTRAINT [FK_Books_Authors] FOREIGN KEY
(
[AuthorID]
) REFERENCES [dbo].[Authors] (
[AuthorID]
)

Hi there,

for testing purposes I've created two tables, Authors and Books (table defs
at the end of the message). I have a one to many relationship between them
(1 Author can have n Books).

My question is: how do I delete an Author from the table? Do I have to start
a transaction, remove the books (of this author), then the author and
commit? Is there a better way? Shall I set it up altogether different (I've
seen the Cascading delete option, but I'm not really sure whether to use it
or not)?

Would be great if somebody could shed some light.

Thanks in advance.

/matthias

--Authors Table
CREATE TABLE [dbo].[Authors] (
[AuthorID] [int] IDENTITY (1, 1) NOT NULL ,
[FirstName] [varchar] (50) NOT NULL ,
[LastName] [varchar] (50) NOT NULL
) ON [PRIMARY]

ALTER TABLE [dbo].[Authors] WITH NOCHECK ADD
CONSTRAINT [PK_Authors] PRIMARY KEY CLUSTERED
(
[AuthorID]
) ON [PRIMARY]

--Books Table

CREATE TABLE [dbo].[Books] (
[BookID] [int] IDENTITY (1, 1) NOT NULL ,
[Title] [varchar] (50) NOT NULL ,
[AuthorID] [int] NOT NULL
) ON [PRIMARY]

ALTER TABLE [dbo].[Books] WITH NOCHECK ADD
CONSTRAINT [PK_Books] PRIMARY KEY CLUSTERED
(
[BookID]
) ON [PRIMARY]

-- Relationship
ALTER TABLE [dbo].[Books] ADD
CONSTRAINT [FK_Books_Authors] FOREIGN KEY
(
[AuthorID]
) REFERENCES [dbo].[Authors] (
[AuthorID]
)

I am a realf an of referential integrity, because you don´t have to code
something on your own and can just refer on the builtin functions.

Therefore you can use a cascading delete with defning a PK-FK Constraint on
the tables where as the authorID should be the PK referenced on the authorID
FK in the books table with a cascading delete.If you want to do that you
have to define the above mentioned constraints and enable the cascading
delete for the relationship.

If you want to code that on your own you can consider using a (UPdate)
trigger or implement the logic on your own in your frontend using a
transaction block where you execute the delete statement.

BEGIN TRANSACTION

--Delete statement

IF @@ERROR = 0
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION


HTH, Jens Suessmeyer.

---
http://ww.sqlserver2005.de
---


"matthias s." <postamt[theat]emvoid[thedot]de> schrieb im Newsbeitrag
news:u2$lv%23nfFHA.272@TK2MSFTNGP15.phx.gbl...
[quoted text, click to view]

The first thing you would do never use an IDENTITY column and you look
up industry standards.
Then you would think about the data types and size of columns -- How
many people with fity character first or last name do you know?

CREATE TABLE Authors
(author_id INTEGER NOT NULL PRIMARY KEY, -- my publisher uses ssn
last_name VARCHAR(20) NOT NULL,
first_name VARCHAR(20) NOT NULL);

CREATE TABLE Books
(isbn CHAR(10) NOT NULL PRIMARY KEY,
book_title VARCHAR(50) NOT NULL,
author_id INTEGER NOT NULL
REFERENCES Authors (author_id)
ON UPDATE CASCADE
ON DELETE CASCADE);

The extra clauses in the Books table are called DRi actions. A change
in the referenced table will cascade to all the referencing tables.
The tables will take care of all this for you.