CODEWITHSUNDEEP
sql-serverforeign-keys
Leron
Leron

Reputation: 9866

SQL Server : setting foreign key to a specific value

I'm not sure what is the best way to structure my question.

I have a table with foreign key column in it. By default the foreign key is set to NOT NULL and I want to keep it that way for now, because maybe this will be the final result. But for now there could be records that don't need (and have) foreign keys values and I want to distinct them somehow so it is as clear as possible that these records are something different from the other.

I tried but as it seems I can not use negative numbers for bigint which is the value of the foreign key in my SQL Server table. I guess this is pretty standard stuff so what is the best thing to do in this situation besides making the foreign key to NULL?

Upvotes: 4

Views: 1392

Answers (2)

jazzytomato
jazzytomato

Reputation: 7214

Foreign key constraints enforces you to refer an existing PK of the other table.

One way not mentioned yet is to drop the constraint for now :

ALTER TABLE YourTable
  DROP CONSTRAINT fk_something

Upvotes: 1

Mike C.
Mike C.

Reputation: 3114

Not sure why HABO didn't make that the answer, because that's pretty much your only option.

  1. Make it NULL
  2. Create a row in the referenced table and set it to TBD or whatever moniker you prefer and use the ID from that instead of NULL

If you have records that do not need an FK and never will, then you should set the column to NULL, else use a temp value.

You cannot use a negative value because you MUST reference something in the foreign table if you have a foreign key constraint.

Upvotes: 3

Related Questions