Setting References, Adding constraints
We need to set up some items so that eventually this project will actually run. First off, we need to set up a reference to an existing server where the project will be exported when we're finished.
Under the Solution's name, there is a entry for References. Right-click on that, then select Database References. Change the database reference type to System Database, and select the master database.
Just to be fair, I had trouble with adding the default as below. I kept getting an error SLQ17501, meaning the editor was unable to find the referred to item.
There is an equivalent of SSMS' Crtl+Shift+R, which in the Project menu then Reanalyze Project. And that didn't work. It's supposed to take a little bit like the refresh of the table data into IntelliSense does in SSMS, so I waited. Hours.
Then I deleted the offending item and tried to add it again. Still there.
Then deleted the entire project and started over. At that point, in the error listing, I noticed the project was set for "Build + Intellisense". Clicking the other options confirmed that the problem was in IntelliSense, not the Build.
Next day, I started off with a new and smaller build and planned on using VS to report the issue to Microsoft. And it never came back. Anywhere. Computer Science is in the next classroom, this is working with software, which is a whole different beast. End interruption.
The source table has the one constraint already, which is the clustered primary key. "Clustered" means that the data will be put in the specified order onto the drive. Primary keys have to be non-null and unique, and are often what is used as a foreign key in another table.
This is a table of dates, and there's already date functions and tools built in to SQL. Why add more? Mostly so that we have a simple table to join on for long calculations. You can figure out if a given day is a weekend by looking at the DatePart() function with the first parameter set to DW. But is it a holiday? That is a bit harder to work out. This table does the calculations so they don't have to be repeated endlessly.
First off, let's set the FederalHolidayFlag to default to the character 'N'. We do the right-click on the solution's name, then Add, then New Item. Under Tables and Views we can select a Default Constraint. And it wants a name again.
And VS is helping where it can.
There's a related Check Constraint. Everybody is going to have opinions about where each of the files should be one single step, or if all the related steps should stay together. Make a choice that appeals to you, and realize unless you're the project manager it doesn't matter. I want the practice using the different templates.
The blue squiggle is error SQL70588 - since there's already a data enforcement check on that column, the WITH CHECK part of the statement will be ignored. I'm making a note that the Test Suite needs to look at that.
Document it. Always. Or you will forget.
Why use 'Y' and 'N'? One byte is the smallest column size. The boolean columns do only take up one bit in some byte, but unless you have a lot of booleans (8 or more), it's not much of a savings in space and a tad more work in CPU - probably breaks even in the long run. We could have used 0 and 1 in a tinyint column. It was an aesthetic choice on the part of the Data Architect team.
There are a bunch more constraints we could put in. Which ones matter to you?