Matt Velic

T-SQL Tuesday

This month’s T-SQL Tuesday is hosted by Allen White. Allen has asked about T-SQL tips that make our work easier. I would like to share one around development.

Everyone knows that both Primary and Foreign Keys must share a few common attributes, such as sharing the same data type. But they do not need to share the same name, and this is something that I believe can hinder query development. Here’s an example:

CREATE TABLE dbo.Donors(
	Donor_ID	INT			IDENTITY(1,1)	PRIMARY KEY,
	First_Name	NVARCHAR(20)	NOT NULL,
	Last_Name	NVARCHAR(40)	NOT NULL
);

CREATE TABLE dbo.Gifts(
	Gift_Key	INT			IDENTITY(1,1)	PRIMARY KEY,
	Person_ID	INT			NOT NULL,
	Amount		MONEY		NOT NULL,
	Tender		VARCHAR(13)	NOT NULL,
	Gift_Date	DATE		NOT NULL,
	Campaign	VARCHAR(14)	NOT NULL
);

ALTER TABLE dbo.Gifts
ADD CONSTRAINT FK_Gift_Donor FOREIGN KEY (Person_ID)
	REFERENCES dbo.Donors (Donor_ID);

In this example, a foreign key relationship exists between Donor_ID and Person_ID. And in a simple, two table example, this isn’t likely to create any issues. But I’m certain that you don’t have to stretch your imagination to see how this may become a development issue when you are working against a whole database. And the problem is exacerbated once you begin to work on dozens of databases all at once.

What problem am I referencing? The problem of memory.

The Problem of Memory

Here is the previous code rewritten following my tip. You’ll notice that both columns referenced in the Foreign Key Constraint have the same name.

CREATE TABLE dbo.Donors(
	Donor_ID	INT			IDENTITY(1,1)	PRIMARY KEY,
	First_Name	NVARCHAR(20)	NOT NULL,
	Last_Name	NVARCHAR(40)	NOT NULL
);

CREATE TABLE dbo.Gifts(
	Gift_Key	INT			IDENTITY(1,1)	PRIMARY KEY,
	Donor_ID	INT			NOT NULL,
	Amount		MONEY		NOT NULL,
	Tender		VARCHAR(13)	NOT NULL,
	Gift_Date	DATE		NOT NULL,
	Campaign	VARCHAR(14)	NOT NULL
);

ALTER TABLE dbo.Gifts
ADD CONSTRAINT FK_Gift_Donor FOREIGN KEY (Donor_ID)
	REFERENCES dbo.Donors (Donor_ID);

And here’s a query written against both examples. They’ll each return the same results with the same performance… but which do you think was less taxing mentally to write?

SELECT d.Donor_ID, d.First_Name, g.Gift_Date, g.Gift_Amount
FROM dbo.Donors AS d
LEFT OUTER JOIN dbo.Gifts AS g
	ON d.Donor_ID = g.Person_ID
WHERE g.Gift_Amount >= 10

--versus

SELECT d.Donor_ID, d.First_Name, g.Gift_Date, g.Gift_Amount
FROM dbo.Donors AS d
LEFT OUTER JOIN dbo.Gifts AS g
	ON d.Donor_ID = g.Donor_ID
WHERE g.Gift_Amount >= 10

Personally, it’s the second. I don’t have to take a split second to think “Did I join those tables on the right columns?” I don’t have to take thirty seconds to look up that information if I’ve guessed wrong. Naming your constraints the same takes the guess work out of query writing. It may not be a game-changer, but this has helped me in a number of occasions, and I hope it helps you too.

• ← Never Not Busy
• Blog Cause I Wanna →