27Jun 2024 by Grant Fritchey No Comments

Data Technology Learning Resources

AWS, Azure, Database Fundamentals, PostgreSQL, SQL Server
Hey all! Quick one here. Tracy Boggiano, awesome person that she is, has put together an interesting collection of data over on GitHub. It's titled DBA Resources, but it goes beyond DBAs. It's much more about the different data platforms on display. And, it's in Github. Create a pull request and you can add to it. Get your own blog on there, whatever. Anyhoo, worth a look. I'm going to do some contributions. Oh, AND, it's multi-platform, cause, isn't everything these days.
Read More
05Sep 2022 by Grant Fritchey No Comments

Database Fundamentals #33: Check Constraints from the GUI

Database Fundamentals, SQL Server
The other types of constraints are referred to as check constraints. They limit the data by defining a logical operation that checks the state of the data prior to allowing an insert or update to the table. The logical operation is not against other tables. The logic can be against multiple columns in the same table. Let’s start with a simple example. The business has determined that the transactions it’s recording will never be less than $10. As part of the business definition, they would like a constraint put in place that limits the TransactionAmount values in the Finance.FinancialTransaction table to only accept values that are greater than $10. Let’s create this constraint using the GUI and again, in the next post, using T-SQL. Check Constraint with the GUI We’ll…
Read More
25Jul 2022 by Grant Fritchey No Comments

Database Fundamentals #32: Create Unique Constraints with T-SQL

Database Fundamentals, SQL Server
In the last Database Fundamentals post, I explained what a unique constraint was and how you can create them using the GUI. Using TSQL to create a constraint is very similar to the primary key and foreign key constraints that you created in this post. You can use either the ALTER TABLE command or create the constraint when you make the table with the CREATE TABLE command. Using CREATE TABLE to Make a Unique Constraint There aren’t any new tables we need for this post, so we’ll create a table that we can drop immediately as soon as we’re done experimenting. You should already be familiar with the CREATE TABLE statement (or follow the link above): CREATE TABLE dbo.ConstraintTest ( TestName VARCHAR(50), CONSTRAINT UniqueName UNIQUE (TestName) ); This script creates…
Read More
28Feb 2022 by Grant Fritchey No Comments

Database Fundamentals #31: Unique Constraints from the GUI

Database Fundamentals, SQL Server
In the last few Fundamentals posts you were introduced to a couple of ways to limit and control the data stored in the tables in your database. A primary key won’t allow a duplicate value. A foreign key won’t allow a value to be added that doesn’t already exist in the parent table and it will prevent data from being deleted. These are types of constraints on data in your database. There are a bunch of other ways to constrain the data in an effort to ensure that the data stored is exactly what the business needs. The next few Fundamentals posts will cover several methods of limiting data. Unique Constraints to Stop Duplicates When the concept of the primary key was introduced earlier in the series, two different types…
Read More
24May 2021 by Grant Fritchey 2 Comments

Database Fundamentals #30: Create Foreign Keys With T-SQL

Database Fundamentals
You can create foreign keys using TSQL roughly the same way as you created primary keys. You can either use the ALTER TABLE statement to add the foreign key, or, if you already have the parent table created along with it’s primary key, you can use the CREATE TABLE statement to include foreign key constraints. The restrictions for creating foreign keys are still the same when using TSQL. Adding a Foreign Key Using the ALTER TABLE statement is very straight forward as before. This script will create a foreign key relationship between the Personnel.Person table and the Personnel.PersonAddress table: ALTER TABLE Personnel.PersonAddress ADD CONSTRAINT PersonAddress_FK_Person FOREIGN KEY (PersonID) REFERENCES Personnel.Person (PersonID); The ALTER TABLE and ADD CONSTRAINT statements are the same as what you saw before. They respectively refer to…
Read More
28Jul 2020 by Grant Fritchey No Comments

Database Fundamentals #28: Creating a Primary Key Using T-SQL

Database Fundamentals
There are actually a couple of ways to create a primary key with T-SQL. You can use the ALTER TABLE script to add a primary key to an existing table, or you can create a primary key as part of the table definition directly. There’s very little difference in the outcome. I’ll show you both methods and you can decided for yourself which one works better for your style of coding. ALTER TABLE for a Primary Key This T-SQL statement will alter the table Management.Address to create a primary key. Notice that I’m supplying most things. There are slightly fewer defaults for you to take advantage of when compared to the GUI. ALTER TABLE Management.Address ADD CONSTRAINT PK_Address PRIMARY KEY (AddressID); You have to tell it which table you’re altering,…
Read More
14Jul 2020 by Grant Fritchey 2 Comments

Database Fundamentals #27: Creating a Primary Key in the Table Designer

Database Fundamentals
Defining primary keys is the hardest part of the operation. You will need to work very closely with the business in order to define exactly what column or columns make a row unique. Often, this will be difficult for a business to define, but you will need to persist in order to be sure that you can properly maintain the integrity of the data being stored. It’s hard for a business to define partly because people just don’t think in terms of “unique values.” They tend to think in terms of pointing at a thing and saying “that’s the one I want.” But invariably there’s a way to uniquely identify almost any concept that business can come up with. You just have to work with the business people to find…
Read More
22Jun 2020 by Grant Fritchey 7 Comments

Database Fundamentals #26: The Primary Key War

Database Fundamentals
There is a war about primary keys in the database world. There are two camps. The first camp believes that primary keys should only ever be created on meaningful information. For example, there is an ISO standard for the abbreviation of state names in the United States. You could create a table for looking up state names and make the primary key that abbreviation because it is guaranteed to be unique. The other camp believes that primary keys should never be created on meaningful information because, meaningful information is subject to change and you don’t want your primary keys to be changing. A changing primary key means changing all the tables that are related to that value. This camp believes that all primary keys should be artificial. As far as…
Read More
26May 2020 by Grant Fritchey No Comments

Database Fundamentals #25: Referential Integrity

Database Fundamentals, SQL Server
If you have been reading through all my fundamentals posts and following along, you have built a small sample database, loaded it with data, and learned how to retrieve the data from it. You’ve also learned how to relate one table to another through T-SQL JOIN statements. But that relationship is very temporary. It will last only as long as it takes for that query to run. To create a database that enforces the relationships between the tables, you need to work with declarative referential integrity (DRI), frequently shortened to referential integrity(RI). DRI is the foundation on which the relational part of the relational storage engine is built. It’s not just a nice thing to do for your database. It’s actually a fundamental piece of how SQL Server works. DRI…
Read More
04May 2020 by Grant Fritchey No Comments

Database Fundamentals #24: More Filtering Data

Database Fundamentals, SQL Server
In this Database Fundamentals post we continue discussing the functionality of the WHERE clause. We started with the basics of the logic using things like AND, OR and LIKE or '='. Now, we'll expand into some other areas. Functions in the WHERE clause SQL Server provides you with all sorts of functions that can be used to manipulate strings, modify dates or times or perform arcane mathematical equations. The problem with these is that if you do them on columns in tables it can lead to performance issues. The trick then, is to not perform functions on the columns in the tables. We’ll cover this in more detail when we get to indexing, variables, and parameters. Just don’t get into the habit of putting functions on the columns in your…
Read More