Tag: SQLNewBlogger

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

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

I had logic in a CS curriculum many years ago and I’ve worked with AND and OR statements for years. I’ve sometimes confused myself, but I usually ensure I have parenthesis included to clarify the code. Not just for me, but for anyone that might glance at the code later.

As a side note, I also try to format code so a quick glance can reveal what happens.

However, I learned something new this week. I saw a question about the order of logical operations in this form: a or b and c.

I had somewhat assumed, like math, we’d use a left to right evaluation. However, that’s not correct. Look at this snippet:

If we went left to write, we’d have two rows from the OR (n=1, n=2) and then an AND that produces no rows. So no results?

That’s not correct. According to BOL for OR,  the AND operations occur first. So n=2 AND n > 3 occurs, with 0 rows. Then the OR with n=1 is evaluated to return 1 row.

Fascinating.

At least to me. I’ve never thought because I’d write

WHERE (n = 1 OR n = 2) AND n > 3

or

WHERE n = 1 OR (n = 2 AND n > 3)

and be sure that what I wanted to occur would occur.

A quick lesson. While it’s good to know what the order or evaluation is for your platform, don’t count on this. If there is a chance for confusion or unintended consequences, use parenthesis. It’s simpler and easier, and I might argue, more elegant.