>Come posso fare su sql?
Intendi su SQL Server?
Devi aggiungere un constraint FOREIGN KEY sulla tabella dalla parte molti della relazione, abilitando una cancellazione "a cascata".
Vediamo un esempio:
USE tempdb;
GO
/* Creo la tabella dbo.Students */
CREATE TABLE dbo.Students(
StudentID int NOT NULL IDENTITY PRIMARY KEY,
FirstName varchar(10) NOT NULL,
LastName varchar(10) NOT NULL
);
/* Creo la tabella dbo.Courses */
CREATE TABLE dbo.Courses(
CourseID int NOT NULL IDENTITY(100, 1) PRIMARY KEY,
StudentID int NOT NULL,
CourseName varchar(20) NOT NULL
);
/* Le popolo */
SET NOCOUNT ON
INSERT dbo.Students VALUES('Lorenzo', 'Benaglia')
INSERT dbo.Students VALUES('Luca', 'Bianchi')
INSERT dbo.Students VALUES('Andrea', 'Montanari')
INSERT dbo.Students VALUES('Gianluca', 'Hotz')
INSERT dbo.Courses VALUES(1, 'Design SQL Server')
INSERT dbo.Courses VALUES(3, 'Design SQL Server')
INSERT dbo.Courses VALUES(1, 'Admin SQL Server')
INSERT dbo.Courses VALUES(2, 'Admin SQL Server')
INSERT dbo.Courses VALUES(4, 'Inside XML') /* :-D */
SET NOCOUNT OFF
GO
/* Aggiungo un constraint FOREIGN KEY sulla tabella dbo.Courses */
ALTER TABLE dbo.Courses
ADD CONSTRAINT FK_Courses_Students FOREIGN KEY (StudentID)
REFERENCES dbo.Students(StudentID) ON DELETE CASCADE;
/* Elimino il mio account */
DELETE dbo.Students
WHERE StudentID = 1;
/* Vediamo se ha eliminato anche i corsi relativi */
SELECT *
FROM dbo.Courses;
/*
CourseID StudentID CourseName
----------- ----------- --------------------
101 3 Design SQL Server
103 2 Admin SQL Server
104 4 Inside XML
(3 row(s) affected)
*/
/* Pulizia */
DROP TABLE dbo.Courses, dbo.Students;
>Grazie a tutti
Prego.
Ciao!
--
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo/
http://italy.mvps.org