|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Beginner Q: Relationship and Updates/Deletesfor 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 --- Show quote "matthias s." <postamt[theat]emvoid[thedot]de> schrieb im Newsbeitrag news:u2$lv%23nfFHA.272@TK2MSFTNGP15.phx.gbl... > 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] > ) > > 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 .. "matthias s." <postamt[theat]emvoid[thedot]de> wrote in message for testing purposes I've created two tables, Authors and Books (table defsnews:u2$lv%23nfFHA.272@TK2MSFTNGP15.phx.gbl... Hi there, 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] ) 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.  
Other interesting topics
|
|||||||||||||||||||||||
