Managing Primary Key Constraints

Posted on May 14, 2012 by Zach Stagers

Primary Key Constraints keep your rows unique, and therefore help maintain your datas integrity.

They can be applied to a single column, or across multiple columns (A maximum of 16). Any column or combination of columns referenced by the Primary Key will be unique, and because of this, they’re usually chosen as the Identity column as well.

A Primary Key column cannot be nullable, due to the fact that it forces uniqueness in the table. Computed columns are allowed to be used as a Primary Key.

When a Primary Key is created, the columns it’s created on also have a clustered index created on them. If you’d like to override this functionality, you can do so by specifying NONCLUSTERED during the creation of the key.

Adding a Primary Key Constraint to an existing table

ALTER TABLE Products
ADD PRIMARY KEY (ProductId)

The above code will modify the Products table to add a Primary Key Constraint to the ProductId column.

ALTER TABLE Products
ADD PRIMARY KEY NONCLUSTERED (ProductId, ProductName)

The above code will modify the Products table to add a Primary Key Constraint across two columns, but also overrides the default action of creating a clustered composite index, and creates a nonclustered composite index instead. What do I mean by composite? This means that the index references multiple columns.

Adding a Primary Key Constraint during table creation

CREATE TABLE Products 
  (
   ProductId INT PRIMARY KEY IDENTITY,
   ProductName VARCHAR(50))

In the above example, we’re adding the Primary Key to the integer column ProductId. The same column is also being specified as the identity column for the table, as I mentioned earlier, this is fairly common.

Removing a Primary Key Constraint

A table can only ever have one Primary Key, and you may reassess your needs and have to drop the existing Primary Key.

In order to do this, you’ll first need to find the name of the primary key that you’d like to drop. You can do this by executing the below code (replace “Schema.Table” with your details, but keep the single quotes):

SELECT I.name [PrimaryKeyName]
 
FROM sys.tables T 
     JOIN sys.indexes I ON I.OBJECT_ID = T.OBJECT_ID
 
WHERE T.OBJECT_ID = OBJECT_ID('Schema.Table') 
      AND I.is_primary_key = 1

Once you’ve run the above code, and the name has been returned, you can run the below code to drop the Primary Key:

ALTER TABLE table_name
DROP CONSTRAINT primary_key_constraint_name

Managing Check Constraints

Posted on May 10, 2012 by Zach Stagers

Check Constraints are an important part of data validation, and should be used at every opportunity.

A lot of DBAs seem to trust the checking done against data before it reaches their database is sufficient enough, but this is a very bad assumption to make. Sometimes people miss things, human error – it happens, unfortunately!

A website may be missing one small detail in its validation, which could cause a major head ache later down the line, and this is why it’s always important to do your own validation.

How they work

A check constraint is essentially a WHERE clause added to a table to confirm that the data being entered into the table is valid.

The boolean check of the constraint decides whether or not the data is allowed to be inserted.

Adding a Check Constraint to an existing table

ALTER TABLE Products
ADD CONSTRAINT ProductsPriceCheck CHECK (Price > 0)

The above code will add a check constraint, named ProductsPriceCheck, to the Products table which checks that the column Price is greater than zero. If someone attempts to enter a value which breaks this rule, an error will be raised.

Please remember that even though NULL isn’t greater than zero, it will get through this check! If you don’t want NULL values to be entered, specify “AND Price IS NOT NULL” in the check – or better yet, add “NOT NULL” to the column.

Adding a Check Constraint during table creation

CREATE TABLE dbo.Products 
   (
    ProductId INT IDENTITY PRIMARY KEY,
    ProductName VARCHAR(45) NOT NULL,
    Price MONEY NOT NULL,
    CONSTRAINT ProductsPriceCheck CHECK (Price > 0)
   )

The syntax here is very similar, but there is now no longer any need for the keyword ADD, as you arn’t adding the constaint to an existing table, it’s being created along with the table.

A check constraint can span multiple columns within the table, but cannot reference anything outside of the table it’s being created against. In order to use multiple columns, reference them exactly as you would when writing a WHERE clause against the table.

A very practical example of a Check Constraint doing what it does best would be against a column that holds an email address:

CREATE TABLE dbo.Person 
   (
    PersonId INT IDENTITY PRIMARY KEY,
    FirstName VARCHAR(45) NOT NULL,
    Surame VARCHAR(45) NOT NULL,
    Email VARCHAR(100) NOT NULL,
    CONSTRAINT PersonEmailCheck CHECK (Email LIKE '%@%' AND Email LIKE '%.%')
   )

This example makes sure that the column Email has an @ symbol and a period – two of the things that absolutely every email address will have.

Removing a Check Constraint

Should you decide that a Check Constraint is no longer needed, then you’ll be able to delete it from the Object Explorer by browsing to the Server > Database > Tables > Expand the table you’re deleting the check constraint from > Constraints > Right click on the constraint and select Delete.

You can also remove the constraint by executing the below SQL:

ALTER TABLE table_name
DROP CONSTRAINT constraint_name

Managing Foreign Key Constraints

Posted on May 7, 2012 by Zach Stagers

Foreign Key Constraints identify links between the tables and columns within your database. The values within a column with a Foreign Key Constraint on it must also appear in the table they reference – creating a link between the columns in the two tables. These columns are commonly used to join tables together in the FROM clause.

There are several different options to choose from and consider when setting up a Foreign Key Constraint, and they are:

No Action: This is the default option, so if none of the others are specified, this one will come into play. Under this option, if a record referenced by a Foreign Key is deleted, then an error is raised stating that an attempt to break the Foreign Key Constraint has been made.
Set Null: When data in a column referenced by a Foreign Key is modified, then all of the data in the column of the referencing record is set to NULL
Set Default: When data in a column referenced by a Foreign Key is modified, then all of the data in the column of the referencing record is set to its default value.
Cascade: When data in a column referenced by a Foreign Key is modified, then all of the data in the column of the referencing record is set to the same value set in the referenced table. Note that if you delete the referenced record, the referencing record will also be deleted!

It’s important to know that these values can be set per DML statement type (Update, Delete). For example, you may choose No Action when an item in the referenced table is deleted, but Cascade for when an item is updated.

Adding a Foreign Key Constraint to an existing table

ALTER TABLE dbo.Products
 
ADD CONSTRAINT FK_Products_ProductType FOREIGN KEY (ProductTypeId)
 
REFERENCES dbo.ProductType (ProductTypeId)
ON DELETE NO ACTION
ON UPDATE CASCADE

In the above example, we are adding a Foreign Key to the Products table, and calling it “FK_Products_ProductType”. We are saying that the column ProductTypeId in the Products table will reference the ProductTypeId in the ProductType table.

The ON DELETE NO ACTION means that should anyone try to delete a row from the parent table (dbo.ProductType) whilst referencing records exist in the child table (dbo.Products), then an error will be raised.

ON UPDATE CASCADE means that if someone updates a Foreign Key column in the ProductType table (dbo.ProductType.ProductTypeId), then this will be replicated to the child columns (dbo.Products.ProductTypeId).

Adding a Foreign Key Constraint during table creation

CREATE TABLE dbo.Products 
 (
  ProductId INT PRIMARY KEY IDENTITY,
  ProductTypeId INT NOT NULL,
  CONSTRAINT FK_Products_ProductType FOREIGN KEY (ProductTypeId) REFERENCES ProductType(ProductTypeId)
 )

Not much changes between adding a Foreign Key Constraint to an existing table or during table creation, other than the removal of the keyword “ADD”. Notice that I haven’t included any “On Delete Cascade” or any other variation of this option. This means that the default (No Action) is set in all instances.

Removing a Foreign Key Constraint

In an instance where you would like to remove a Foreign Key Constraint, you can do so by executing the below code:

ALTER TABLE Products
DROP FOREIGN KEY FK_Products_ProductType

Scratchbox | Zach Stagers

SQL Server Developer and Administrator

Tag Archives: Constraints

Managing Primary Key Constraints

Managing Check Constraints

Managing Foreign Key Constraints