Tag: SQLNewBlogger

I had written about a FK in a CREATE TABLE statement recently, but the second half of this was that after the original question, the person asked if this would also work for a self-referencing FK. It does, and I wrote this to show that.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

Creating the FK

The last post showed how to create the FK, but this works within a table as well. Let’s say I want to have an Employee table that links back one employee to another, who is their manager. That type of structure looks like this:

CREATE TABLE [dbo].[Employee](
     [EmpID] [INT] NOT NULL,
     [EmpName] [VARCHAR](20) NULL,
     [MgrID] [INT] NULL,
  CONSTRAINT [EmployeePK] PRIMARY KEY CLUSTERED 
(
     [EmpID] ASC
)
) ON [PRIMARY]
GO

I can add a link that makes MgrID a FK reference by altering the code like this:

CREATE TABLE [dbo].[Employee](
     [EmpID] [INT] NOT NULL,
     [EmpName] [VARCHAR](20) NULL,
     [MgrID] [INT] NULL,
  CONSTRAINT [EmployeePK] PRIMARY KEY CLUSTERED 
(
     [EmpID] ASC
),
CONSTRAINT FK_MgrID_EmpID FOREIGN KEY (MgrID) REFERENCES dbo.Employee (EmpID)
) 
GO

Easy.

SQL New Blogger

This is a post that took me less than 10 minutes to write. I changed the code from the previous post and wrote this right after the other one. The search and replace was the longest code part, and then the writing was quick, 5 minutes.

This is a core skill for a DBA or developer. Write your own post to show how and why to build a self referencing FK for some scenario that you work with in your job, or in a project.

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.