a field that can be null until it receive something in it?

Question

If you have a field(foreign key) in a table that can accept null until there is something in it, so after that the field would refuse null value.

how would you do this?

Answer 1

According to MSDN, triggers are useful for "Extending referential integrity between tables.", which is exactly what you are trying to do. So just use a FOR UPDATE trigger and raise an error if the old value was not null but the new value is.

Assuming that you have a nullable column of type int called SomeId and your table is called SomeTable the following trigger would do the job:

CREATE TRIGGER CheckTrigger ON SomeTable
FOR UPDATE
AS
DECLARE @OldId int, 
        @NewId int
BEGIN
    SELECT @OldId = SomeId FROM deleted
    SELECT @NewId = SomeId FROM inserted

    IF NOT @OldId IS NULL AND @NewId IS NULL
    BEGIN
        RAISERROR ('No!!!!', 10,1) WITH NOWAIT
        ROLLBACK TRANSACTION
    END
END;

Answer 2

A trigger would be your best bet in that case

Answer 3

Well, the first thing that comes to my mind is creating a trigger that only updates the column if it is null and the new value isn't.