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…
Most of the time when I talk about or demo Extended Events, I spend more time talking about query tuning (I have a problem). However, there are tons of things that you can do with Extended Events. Here's a little one that came up, auditing unique constraint violations. Unique Constraint Violations Whether we're talking a primary key or just a constraint, the error you get is number 2627 when you attempt to add a non-unique value. So, the code for a simple way to track this in Extended Events would look like this: CREATE EVENT SESSION [UniqueConstraintViolation] ON SERVER ADD EVENT sqlserver.error_reported (WHERE ([error_number] = (2627))); That's it. That's all you need. Probably, it'd be a good idea to output this to a file (that's usually what I do). However,…
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…
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…
The purpose of a foreign key is to ensure data integrity by making sure that data added to a child table actually exists in the parent table and preventing data from being removed in the parent table if it’s in the child table. The rules for these relationships are not terribly complex: The columns in the two tables must be the same data type, although, if SQL Server can automatically, and correctly, convert the data you can get away with different data types. But don’t do that. It’s begging for an issue. Keep them the same and you won’t have any problems.The child values can be nullable, which means that any child data is unknown.The child data can also be required, meaning that you have to have the relationship, no…
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,…
When I first saw this question I thought to myself, "Self. Don't you think that's comparing apples to hammers? Yes, Self, I'm pretty sure it is. Good, I thought so too, self. Yeah, me too." After rebooting because of the runaway iterations on that thought, I had another, "Well... hold on there self. Both types of objects, while pretty different, are taken into account by the query optimizer." I then had to admit to myself that I had a point. So the question remains, are foreign keys better than indexes? As my first self said, these are different objects and they fulfill different purposes within SQL Server. My second self wants to point out that when you're dealing with functional objects within SQL Server, it's a bad habit to start…
Most people are very aware of the fact that having a foreign key constraint in place on your tables adds overhead to inserts and deletes. And many people believe that there is no benefit to foreign keys beyond referential integrity (which, the application can handle perfectly well, right?). But is that an accurate statement? Here's the basis for our investigation, a query in AdventureWorks2008R2: SELECT p.LastName + ', ' + p.FirstName AS 'PersonName' FROM Person.Address AS a JOIN Person.BusinessEntityAddress AS bea ON a.AddressID = bea.AddressID JOIN Person.BusinessEntity AS be ON bea.BusinessEntityID = be.BusinessEntityID JOIN Person.Person AS p ON be.BusinessEntityID = p.BusinessEntityID; This query results in the following execution plan: I know that is an ugly query and an ugly query plan, but bear with me for a moment. Do you…
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…
As part of my job, I've been tasked with doing some of my work in Oracle, so I'm learning Oracle. Allow me to share a little of my pain as I explore a space I've only ever dabbled in. Getting Started in Oracle Back in the day, in order to get started with Oracle, you begin at the Oracle web site. There, you download an installation, after getting a license (or while, whatever). In our modern era, this is the hard way to get stuff done. The first place I went was Azure. There is excellent Oracle support on the Azure platform and, best of all for me, it's really easy to fire up an Oracle VM. I had a server up and running in no time. Win! Another way…