I had the following tables
[sourcecode language=”sql”]
create table customer (
id int identity primary key,
…
)
create table foo_orders (
orderno int identity primary key,
customerid int,
…
)
[/sourcecode]
after various failed attempts at “alter column”, “add constraint”, etc. I finally found the correct incantation
[sourcecode language=”sql”]
alter table foo_orders with nocheck add foreign key(customerid)
references customer(id)
on delete no action
on update no action
[/sourcecode]
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.