Adding Foreign Keys after a table is created in SQL Server

Well, you knew exactly what you needed when you created a table. Only now when you created a child table, the child table does not update with the information from your main table. Or worse you deleted something from your primary table and it still lives in the child table. You need a foreign key, my friend. This is how you define one after you’ve created your table.

ALTER TABLE My_Child_Table ADD CONSTRAINT FK_User_ID
FOREIGN KEY ( User_ID_In_Child_Table )
REFERENCES My_Primary_Table ( User_ID_In_Primary_Table ) 
ON DELETE CASCADE
ON UPDATE CASCADE

The last two lines are important. In this example, if the user id in the primary table is modified or deleted, the change will cascade to the child table. This ensures consistency between the two tables. However, this may not be the intended behavior for your application. Consider your cascades carefully before bringing them into production.

As always, this works on SQL Server. Check your documentation for mySql, Oracle, etc.

Rudy

Rudy Rodarte is a SQL Server Database professional based in Austin, TX. Over his career, Rudy has worked with SSRS, SSIS, performance tuning troubleshooting. When away from Keyboard, Rudy goes to Spurs and Baylor Bear sporting events.

More Posts - Website - Twitter