TSQL alter table adding constraint for both cascade on delete and update

Question

I'm trying to create a constraint with both on update and delete in tsql. I've tried a couple of different methods, and now I'm a little stuck & frustrated - seems so simple. I know you can't alter an existing constraint so I'm not sure about how to do this;

alter table AllowedCars 
   add constraint FK_AllowedCars_CarID foreign key (CarID) 
      references Cars(LocusID) on delete cascade, 
constraint FK_AllowedCars_CarID foreign key (CarID) 
   references Cars(CarID) on update cascade

or this;

alter table AllowedCars add constraint FK_AllowedCars_CarID foreign key (CarID) 
   references Cars(CarID) on delete cascade and on update cascade

Answer 1

You need to drop constraint first, and then recreate it. Your second attempt was right, but you needed to remove and.

alter table AllowedCars 
  drop constraint FK_AllowedCars_CarID 

alter table AllowedCars 
  add constraint FK_AllowedCars_CarID 
      foreign key (CarID) 
      references Cars(CarID) 
      on delete cascade 
      on update cascade