I had someone ask this question recently and had to double check the syntax myself, so I thought this would make a nice SQL New Blogger post.
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.
Defining a Foreign Key
Most people define a foreign key like this:
ALTER TABLE [dbo].[OrderLine] WITH CHECK ADD CONSTRAINT [FK_OrderLine_Order] FOREIGN KEY([OrderID]) REFERENCES [dbo].[Order] ([OrderID]) GO
This assumes I’ve added a table called dbo.Order with a PK of OrderID.
However, I can do this in the CREATE TABLE statement, like shown below. I add a new section after a column with the CONSTRAINT keyword. Then I name the constraint, which is always a good practice. I can then add the FK keyword, the column and the references that connects this child column to the parent column.
CREATE TABLE dbo.OrderLine ( OrderLineID INT NOT NULL CONSTRAINT OrderLinePK PRIMARY KEY , OrderID INT , Qty INT , Price NUMERIC(10,2) , CONSTRAINT FK_OrderLine_Order FOREIGN KEY (OrderID) REFERENCES dbo.[Order](OrderID) ) GO
Easy to do and this keeps my code clean.
Note that if I script this out in SSMS, I’ll get this:
CREATE TABLE [dbo].[OrderLine]( [OrderLineID] [int] NOT NULL, [OrderID] [int] NULL, [Qty] [int] NULL, [Price] [numeric](10, 2) NULL, CONSTRAINT [OrderLinePK] PRIMARY KEY CLUSTERED ( [OrderLineID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[OrderLine] WITH CHECK ADD CONSTRAINT [FK_OrderLine_Order] FOREIGN KEY([OrderID]) REFERENCES [dbo].[Order] ([OrderID]) GO
Nothing wrong with that, but knowing both syntaxes is a good idea. Plus, if you know this is a child column, define it right away.
SQL New Blogger
This is a post that took me about 15 minutes to write. I had to create and drop the tables a few times and verify I had the syntax correct, and then explain and format things.
This is a core skill for a DBA or developer. You ought to know how to define a FK and use them where appropriate. Write your own post to show how to build a FK for some scenario that you work with in your job, or in a project.
 on X_ _@ReviewMyDB @redgate @GFritchey Enjoy. Sorry [3].png "2023-10-19 16_17_28-way0utwest ️ (He_Him_His) on X_ _@ReviewMyDB @redgate @GFritchey Enjoy. Sorry")
