INSERT statement conflicted with COLUMN FOREIGN KEY constraint...

Hi there,

I have a stored procedure which i pass a number of parameters into. One of these parameters is staffNo (only passed this in because i couldn't execute the query without it). The thing is this field can be Null, but when trying to pass null into it it comes up with an Foreign Key conflict. staffNo is a foreign key within the table i'm inserting the data into.

This is the error i get:

"INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'PropStaffFK'. The conflict occurred in database 'DewMountain', table 'TblStaff', column 'staffNo'. The statement has been terminated. The 'PropertyAdvert' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead. "

Does anyone know of away around this? how to pass a null value to the stored procedure without it causing this error.

Thank you

Melanie

There is a conflict, you say that staffNo can be null, however, PropStaffFK says that it can not, since you have no staff member with an id of null.|||

Thanks for your reply Motley.

So does that mean if you have a foreign key referencing a primary key from another table that it can't be null?

That makes sence that it would be true, however, in one of my tables TblProperty i have the column staffNo and in this column i want to allow null values to indicate that the property hasn't been approved by a memeber of staff (if it has been approved then the member of staff that approved the property, thier staffNo will be inserted in there) . Do you know of any way to do this?

Thanks

Melanie

|||

Here's the problem. In order to set a FK contraint, you need to reference a primary key table (That has a primary key set). You can't set a primary key index on a column that is nullable. Since the Primary key table doesn't have a NULL-value as one of it's values, then the FK table can not have null as one of it's values either. (Actually, it wouldn't really make all that much sense anyhow, because even if there was a null vaue in the PK table, since null represents UNKNOWN, and UNKNOWN=UNKNOWN is always false, it still shouldn't work even if null was allowed in the PK table).

A workaround is to create an entry into the PK table for unassigned values, like "0" or "-1". Then make the column in the FK table, not-nullable, with a default constraint of "0" or "-1".

|||Another option is to remove the primary key on the primary key table, enter a null value in the old PK column, then apply a unique constraint on that column, then reestablish your FK relationship. Of course, if the old PK table column as of type identity, you'll have to remove that since identity columns can not contain null either.|||

Yeah thats the thing, i have the primary key column set as type Identity.

I like the first idea you mentioned, by putting a 0 or -1 in there replacing the null entry.

Could use the identity type below ( seed 0 and increment 1)

IDENTITY (0, 1)

then the null entry will be replaced by '0' by inserting a fake entry at the start.

Thanks for your help Motley i'll go and try this out.

Melanie

|||You can just remove the indentity from the column, insert your record, and add the identity back on the column too, that works too.|||

How would i carry that out?

I mean to add the identity back to the column after inserting a row of data, wouldn't this require me dropping the table which in turn removes the data entered previouly entered?

Could you tell me the way of doing this without dropping the table?

Thank you

Melanie

|||

I use management studio, so while technically you are correct, it hides it all in the background for me (Copying all the data), and if the table isn't too big, it's quick. But as far as I know, you are correct, you must create a new table to reinstate the identity.

|||

Thanks Motley

What do you suggest for me then?

Guess i'll have to go with '0' to replace the null entry.

Melanie

|||

You might try doing the insert by doing a SET IDENTITY_INSERT {Your table} ON then insert the record, then SET IDENTITY_INSERT {Your table} OFF