Inserting NULL when foreign key gets deleted.

I have a couple of tables in my database (SQL server 2000)
1. Student -- Columns are as
"StudentID" -- INT
"StudentName" -- NVARCHAR
"DepartmentID" -- INT
2. Department -- Columns are as
"DepartmentID" -- INT
"DepartmentName" -- NVARCHAR
So in the table "Student", the field "DepartmentID" is a foreign key. Now
what I need to do is that when I delete a row in the "Department" table, then
the rows in "Student" table where the to be deleted "Department ID" occurs
must *not* get deleted. Instead NULL should be inserted there.
Can this be achieved through database schema? Or is it something that needs
to be done through triggers?
A.
--
Locate me in the blogosphere: http://spaces.msn.com/aayushpuriIn 2005, you can define SET DEFAULT and SET NULL for a foreign key reference (and of course CASCADE
and restrict). In 200, you need to write code for this(trigger, for example).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Aayush Puri" <aayushpuri @. h o t m a i l . c o m> wrote in message
news:0F995DF3-EFED-475B-9F22-E96302A58F0B@.microsoft.com...
>I have a couple of tables in my database (SQL server 2000)
> 1. Student -- Columns are as
> "StudentID" -- INT
> "StudentName" -- NVARCHAR
> "DepartmentID" -- INT
> 2. Department -- Columns are as
> "DepartmentID" -- INT
> "DepartmentName" -- NVARCHAR
> So in the table "Student", the field "DepartmentID" is a foreign key. Now
> what I need to do is that when I delete a row in the "Department" table, then
> the rows in "Student" table where the to be deleted "Department ID" occurs
> must *not* get deleted. Instead NULL should be inserted there.
> Can this be achieved through database schema? Or is it something that needs
> to be done through triggers?
> A.
> --
> Locate me in the blogosphere: http://spaces.msn.com/aayushpuri|||Thanks a lot Tibor for the quick reply. In the meanwhile I also searched a
bit more for the solution to my problem. I got to this article on MSDN -
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql_refintegrity.asp.
It explains as to how to implement the "ON DELETE INSERT NULL" functionality
as TRIGGER in SQL server 2000.
A.
--
Locate me in the blogosphere: http://spaces.msn.com/aayushpuri
"Tibor Karaszi" wrote:
> In 2005, you can define SET DEFAULT and SET NULL for a foreign key reference (and of course CASCADE
> and restrict). In 200, you need to write code for this(trigger, for example).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Aayush Puri" <aayushpuri @. h o t m a i l . c o m> wrote in message
> news:0F995DF3-EFED-475B-9F22-E96302A58F0B@.microsoft.com...
> >I have a couple of tables in my database (SQL server 2000)
> > 1. Student -- Columns are as
> > "StudentID" -- INT
> > "StudentName" -- NVARCHAR
> > "DepartmentID" -- INT
> > 2. Department -- Columns are as
> > "DepartmentID" -- INT
> > "DepartmentName" -- NVARCHAR
> >
> > So in the table "Student", the field "DepartmentID" is a foreign key. Now
> > what I need to do is that when I delete a row in the "Department" table, then
> > the rows in "Student" table where the to be deleted "Department ID" occurs
> > must *not* get deleted. Instead NULL should be inserted there.
> > Can this be achieved through database schema? Or is it something that needs
> > to be done through triggers?
> >
> > A.
> >
> > --
> > Locate me in the blogosphere: http://spaces.msn.com/aayushpuri
>
>