Referential Integrity Problem

[surovi99]

Hi all,
I have a problem with referential integrity in ms sql server. If I delete a primary key record of one table, the foreign key records are also deleted automatically provided I enable 'Csscade Delete Related Fields' option in the Relationshops Property Page in ms sql server. But if I dont enable this option, there comes the error 'Violation of Foreign Key constraints'. What I want is that if i delete a primary key record, the foreign key record will still retain. For example, i have a room table: Room(roomNo(pk),userid(fk), hardwareid(pk fk), telephoneid(pk fk)) and another table Users(userid(pk), name). On deleting a record in users info, the corresponding foreign key record in Room will still exist but foreign key user id will become null in Room table. Can anyone help me out with this problem? As soon as possible response will be very much appreciated.

Many thanks in adavance...

 

[hmckillop]

Cant you mark the User as deleted (a smallint or similiar )and ensure that any queries check for valid users also include this field.
This way you dont actually delete the users, but and ensure referential integrity?


"I'm living so far beyond my income that we may almost be said to be living apart

 

[r937]

you could do it easily if sql server supported more of the options for foreign keys

you should be able to say

ON DELETE [ CASCADE | NO ACTION | SET NULL | SET DEFAULT ]

but unfortunately you can't do the last two


the easiest way to do this is to UPDATE the Room table first (using a join to the appropriate user row) to set the userid FK to NULL (or whatever you want)

then in a second query, DELETE the user

yes, it's two steps, but it's very straightforward

this isn't really bypassing relational integrity, it is working within the boundaries of relational integrity as implemented in sql server

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[surovi99]

Thank you r937. But I have a question on how to use a join to the appropriate user row. Should it be like this if I use php with an interface to mssql server:

$query1= Update Room Set Room.userId=NULL where Room.userId=Users.userId AND Users.userId='$userId';

$query2= Delete from Users where userId='$userId';


Many thanks in advance....

 

[r937]

sorry, you probably don't need the join

if you are doing this for only one user, you already know the userid, so your first query is just --

Update Room
Set Room.userId=NULL
where Room.userId= $userId

(note: if userid is numeric, don't put quotes around the values)


rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[surovi99]

Thank you very much r937. It worked out fine!!