db :: adding "missing" foreign key constraints (tsql)

I had the following tables

create table customer (
 id int identity primary key,
 ...
)
create table foo_orders (
 orderno int identity primary key,
 customerid int,
 ...
)

after various failed attempts at “alter column”, “add constraint”, etc. I finally found the correct incantation

alter table foo_orders with nocheck add foreign key(customerid)
references customer(id)
on delete no action
on update no action

In (other) words:

  • in the foo_orders table
  • create a new foreign key
    • from the customerid field
    • to the id field in the customer table
  • don’t check existing data in the table (with nocheck)
  • if the customer is deleted, keep the foo_orders record (on delete no action)
  • if the id field in the customer table changes, then do nothing (on update no action)

(at least that’s what I’m pretty sure it means 😉

Why the no action clauses? The foo_order table records info we send to external entities, and is part of the historical record.  Any changes should never happen automagically.

This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *