0

I've got two tables with the following structure:

Person

  • Id
  • Name

Class

  • Id
  • PersonId
  • Sequence

and some data:

Person

1   name1
2   name2
3   name3

Class

1   1   3
2   1   1
3   1   2
4   2   1

Class table can contain multiple rows related to one row from Person. PersonId from Class table is foreign key to Id in Person table. I'd like to delete data related to Person with Id 1, so remove the 3 rows from Class, and 1 row from Person using one query.

How can I do that in SQL?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Steve Macculan
  • 2,292
  • 5
  • 22
  • 32
  • 1
    Under "foreign-key relationships" change "Insert and Update specification" to `Delete Rule: Cascade`. http://msdn.microsoft.com/en-us/library/ms175493.aspx Then delete from `Person` which also deletes from `Class`. – Tim Schmelter May 26 '14 at 14:15

1 Answers1

0

Try This:

CREATE TABLE [dbo].[Class](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [PersonId] [int] NULL,
    [Sequence] [int] NULL,
 CONSTRAINT [PK_Class] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Person]    Script Date: 26-05-2014 07:54:26 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Person](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Person] [nchar](10) NULL,
 CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[Class]  WITH CHECK ADD  CONSTRAINT [FK_Class_Person] FOREIGN KEY([PersonId])
REFERENCES [dbo].[Person] ([Id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Class] CHECK CONSTRAINT [FK_Class_Person]
GO
Devansh
  • 1,277
  • 1
  • 13
  • 19