Voice of the DBA

Tag: SQLNewBlogger

Adding a Foreign Key in the CREATE TABLE statement–#SQLNewBlogger
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.
October 30, 2023
Deleting a Git Branch–#SQLNewBlogger
I had someone ask me recently about deleting branches. While I had known how to delete a local branch, I had to look up how to delete a remote one. Documenting these both will hopefully help me remember this.

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

Deleting Branches

Most parameters have something do to with the action, and most people might guess a –d is used to delete a branch in Git. The actual syntax for a branch named “Feature123” is:

git branch –d Feature123

You can also use a –D, though be aware that –D is the same as –d with the –force option. The –d actually aliases to –delete, so you have three options:
- -d
- –delete
- -D (this will run –-force)
To delete a remote branch, you can use (with v1.7+)

git push origin –-delete Feature123

where origin is the remote name and Feature123 is the branch. This is better than the old syntax, and you ought to keep your git up to date.

You can see this working for one of my branches below:

Git docs for branch have more details.

Use this to clean up branches if your changes are merged and you don’t need to send in any more PRs for this branch.

SQL New Blogger

This is a simple thing, but one that I don’t do often, so I wrote this as much to document it for myself as to put this out there as a piece of knowledge. If someone reads this post and asks the question in an interview, it’s likely an easy one for me to give.

You can do this, help showcase your career knowledge and control the interview. This piece took me about 8 minutes to write. You could do your own version of this topic.
October 23, 2023
Quick Filtering in SSMS–#SQLNewBlogger

I saw someone limit the databases they see in SSMS, which isn’t something I often do, but I thought this was great.

A Long List

This is the list of databases on a demo instance I have. While it’s a lot since I do a lot of testing with customers and colleagues, I see plenty of people will lists of databases longer than this.

I also watch them scroll like I do when trying to find an object in a database.

However, for demos, this is a lot, so I like to slim things down. I used to have a script to detach all the databases and then attach the ones I need, but that’s time consuming and once in awhile, I need a different database, so that’s an issue.

I saw the someone filter their list by clicking the filter button in the Object Explorer. This is the funnel button shown here:

Once you click this, a dialog appears that let’s you enter your filter criteria. I’ll use a simple filter of “zero” to limit to those databases in my zerodowntime demo.

Once I click “OK”, I see only those databases listed.

If I want to get everything back, I can click filter again and then delete my criteria, or click “Clear” filter. That gets me the entire list back.

Easy.

SQLNewBlogger

This post was something I jotted a note about when I saw someone do this. The note was literally “write about filtering in SSMS object explorer”. I took that and wrote this post in 10 minutes, including time to grab screen shots.

You could do this as well, showcasing the knowledge that you’re learning to use tools better, which make you more productive. Employers love that.

Write your own post with more advanced filtering.

September 27, 2023
Creating an Empty Repo in GitHub–#SQLNewBlogger

I saw someone struggling with getting started with a Visual Studio project and Azure DevOps. They got a conflict, which I’ll show and then get you started with an empty repo.

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

The Problem with Initialized Repos

I wrote about this recently with Azure DevOps and empty repos.. If I initialize a repo in GitHub, or any online provider, and try to push up a local repo, it returns an error that I have conflicts.

I’ve learned that when I want to start a new project, either I create the repo locally and then make an empty one online, or I create it online, clone it, and then start working.

I try to do the second (create online first), but I don’t always remember.

Creating an Empty Repo

In GitHub, if you go to your repositories page, you can see a “New” button on the right.

Click this and enter a name, as I’ve done below. BTW, I love the suggested name.

If you look at the image above, near the bottom, you can see my mouse pointer.

DO NOT check this box if you want an empty repo. Don’t add a .gitignore either. You’ll be adding code from your own project, which likely has a gitignore file and might have a repo. If not, just create a readme.md file in your local git repo.

Don’t add a license file either.

Pushing My Project

Now when you create this, you get the details of how to push your repository. If you have never used git, just open a command prompt and paste these lines in one by one and run them.

That’s it and now you can work and commit in either repo and deal with merges.

SQLNewBlogger

This post was easy for me, but I’ve been in the situation where I’ve made this mistake. In this case, I’d written the other post, so I knew what to do here. I spent another 10 minutes getting GitHub set, taking images, and writing this.

Easy to do, 2 posts from one idea, and showcasing knowledge on another platform. I could do another one with BitBucket or somewhere else, and maybe I will. I have customers that use other platforms.

Maybe you should do this and showcase your versatility to the next person that wants to interview you. If you want to work with software and Git, show them you know the basics. Or even more.

September 20, 2023