Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.
One of the important things that a database developer can do is to define Foreign Keys (FK) at table creation. This is a good time to do this as the referential integrity gets setup before any data is added and this can prevent issues later.
This post shows the syntax for defining the FKs and adding them to your tables immediately.
Build the Reference
The first step is to ensure you have a table with a Primary Key (PK) that will be referenced. Let’s do that first. I’ve been looking to provide a database of SQL Server Builds, so let’s start with a table of versions.
CREATE TABLE SQLServerVersion
( SQLServerVersionKey INT IDENTITY(1,1)
, VersionName VARCHAR(200)
, CONSTRAINT SQLServerVersionPK PRIMARY KEY (SQLServerVersionKey)
)
GO
The PK in this table is what is referenced in the next table. This is required as a FK must refer to a PK in another table.
Add the Reference
When you build a child table, you may write the code like this:
CREATE TABLE [dbo].[SQLServerBuilds]
(
[BuildKey] [int] NOT NULL IDENTITY(1, 1),
[BuildNumber] [varchar] (30) NULL,
[BuildDescription] [varchar] (100) NULL,
[BuildKBArticleNumber] [varchar] (50) NULL,
[BuildKBArticleURL] [varchar] (1000) NULL,
[SQLServerVersionKey] [int] NULL
) ON [PRIMARY]
GO
However, in this case, you’ve ignored the FK that might link this table to the versions table. This means that a value could be entered in this table that doesn’t exist in the SQLServerVersion table.
You might think this won’t happen with your application, but thousands, maybe millions, of developers have felt the same way. And they have junk data in their databases because of this.
If there is a strong relationship, add the FK.
Here’s how we do that in the CREATE TABLE statement. I’ll add a comma at the end and include a CONSTRAINT clause. I add the name and then the FOREIGN KEY keywords. Next I include the column from this table that is the FK with the References and the other table and column.
CREATE TABLE dbo.SQLServerBuild
(
BuildKey INT NOT NULL IDENTITY(1, 1) ,
BuildNumber VARCHAR(30) NULL ,
BuildDescription VARCHAR(100) NULL ,
BuildKBArticleNumber VARCHAR(50) NULL ,
BuildKBArticleURL VARCHAR(1000) NULL ,
SQLServerVersionKey INT NULL ,
CONSTRAINT SQLServerBuild_Version_FK
FOREIGN KEY (SQLServerVersionKey)
REFERENCES dbo.SQLServerVersion (SQLServerVersionKey)
) ON [PRIMARY];
GO
This is the structure I tend to use, though sometimes I’ll move the CONSTRAINT clause directly below the actual column. This lets me see right away this is related to that column.
I also avoid using this inline in the column as I can’t specify the constraint name, which I always want to do.
SQLNewBlogger
This is a core skill that database developers needed. If you know the syntax, this post would take about 10 minutes to structure and write. If not, maybe it’s 10 more to learn a bit.. Write your own and show you understand the design concepts.
Reference
Creating Foreign Key Relationships – https://docs.microsoft.com/en-us/sql/relational-databases/tables/create-foreign-key-relationships?view=sql-server-2017
