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, the
n
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, t
hen
> 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 need
s
> 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/d...fintegrity.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 referen
ce (and of course CASCADE
> and restrict). In 200, you need to write code for this(trigger, for exampl
e).
> --
> 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...
>
>

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
>
>

inserting NULL into Datetime and int

Hello,

I have an asp.net page with different textboxes that put text into an sql database.
When I try to leave the textboxes blank that correspond with datatypes of int and datetime
in sql server it gives an error : I need to fill in these textboxes.

How can I solve this problem so I can leave the boxes blanc? What is the best sollution for this? I know I can set the types to String but then the user could fill in what he wants :)
Could you give me some advice on this please? All ideas are welcome.
Thanks !if you strictly want to enforce the user to enter the proper data and not mess up the system, use customvalidators for the textboxes.

for date, you can prbly have three textboxes for month, yr and day and validate it to numeric and when you add the values to db, concatenate them with a "/" in between.

or if the user leaves it blank, check if the txtDate.text = "" then use system.dbnull to enter null into the db.

HTH.|||Hey thanks ndinakar :)

Ok I've looked in the .net classes for this dbnull. There's no good example of how to do that.
Do I use this dbnull on the page script or in the database (for example in a stored procedure?)
Could you give an example please?(I'm very noob)

Thanks again for replying !