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

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

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

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

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

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

What Prevents You From Securing Your Servers?

Database Fundamentals
Reading about how hackers are using SQL Server instances that are exposed on the internet AND have weak passwords to work into systems, I'm sitting here wondering why. I get it. A pretty hefty majority of these were simply set up by people who didn't know any better. Computers and databases are now so wonderfully easy, almost anyone can slap together a database server, and do. Simple ignorance leads them to misconfigure the systems and leave them exposed. So, while it's hard to forgive this ignorance, it's easy to understand. I sincerely doubt we'll ever be able to do much about it. I suppose if systems were designed to be secure from the moment of installation, that would help. Stuff like ElasticSearch having it's development servers have zero security out…
Read More

Database Fundamentals #23: Filtering Data

Database Fundamentals, SQL Server
If you've been reading these Database Fundamentals posts, you've already seen the WHERE clause because of your use of it when manipulating data with DELETE and UPDATE statements. It's also been used several times earlier in this series to limit the values returned from a SELECT statement. The primary places where people run into trouble with T-SQL is in the JOIN criteria and the WHERE clause criteria. This occurs because they don’t understand well enough what the filters and operators they’re using will do. They end up returning too much data because they didn’t us the WHERE clause or misapplied it. They also filter too much data out. Just remember, there are even more functions than we go over here in this series. While these basic operators answer most needs,…
Read More

Database Fundamentals #22: Using the Join Operator, CROSS JOIN

Database Fundamentals
While the CROSS JOIN is not used much, and, depending on the size of your data it can be dangerous, there are some uses for it. For example, you may want to write a query that will summarize all the sales for the companies and people in your system. You can do this using what is called an aggregate operation or a GROUP BY: SELECT c.CompanyName, p.LastName, SUM(ft.TransactionAmount) AS 'TransactionTotals' FROM Management.Company AS c JOIN Finance.FinancialTransaction AS ft ON c.CompanyID = ft.CompanyID JOIN Personnel.Person AS p ON p.PersonID = ft.PersonID GROUP BY c.CompanyName, p.LastName; This will add all the values up in the SUM operation for each company and each person that has values so that your data will look like this: The only problem with this is, you can’t…
Read More

Database Fundamentals #21: Using the JOIN Operator, OUTER JOIN

Database Fundamentals, SQL Server
The OUTER JOIN returns one complete set of data and then the matching values from the other set. The syntax is basically the same as INNER JOIN but you have to include whether or not you’re dealing with a RIGHT or a LEFT JOIN. The OUTER word, just like the INNER key word, is not required. OUTER JOIN Imagine a situation where you have a list of people. Some of those people have financial transactions, but some do not. If you want a query that lists all people in the system, including those with financial transactions, the query might look like this: SELECT p.LastName, ft.TransactionAmount, ft.TransactionDate, ft.TransactionTime FROM Personnel.Person AS p LEFT JOIN Finance.FinancialTransaction AS ft ON p.PersonID = ft.PersonID; Except for the addition of the LEFT key word, this…
Read More