Constraints… helpful in making sure you get the data you want out of your database! Constraints allow you to define rules that the data must follow in order to be inserted into a record. You might want to define a primary key or foreign key in order to define the relationship of two related records in different tables. You may want a column to receive a unique value for each of its records, or you may just want to verify the data in a column fits a certain mold.

While I would normally say checking the data should go in your business or interface layer, you can define it in your database layer. Just be careful how much checking you do on your base layer, you could end up making your system far slower than you realize.

Since I’ve already covered the primary key and foreign key constraints in their own post, let’s jump straight into UNIQUE constraints.

UNIQUE Constraints

To specify that a column will require unique values, when creating it in SQL, use the UNIQUE keyword.

Here is an example:

USE Exercise;
GO
CREATE TABLE Students
(
    StudentNumber int UNIQUE,
    FirstName nvarchar(50),
    LastName nvarchar(50) NOT NULL
);
GO

When a column has been marked as unique, during data entry, the user must provide a unique value for each new record created. If an existing value is assigned to the column, this would produce an error:

USE Exercise;
GO
CREATE TABLE Students
(
    StudentNumber int UNIQUE,
    FirstName nvarchar(50),
    LastName nvarchar(50) NOT NULL
);
GO

INSERT INTO Students
VALUES(24880, N'John', N'Scheels'),
      (92846, N'Rénée', N'Almonds'),
      (47196, N'Peter', N'Sansen'),
      (92846, N'Daly', N'Camara'),
      (36904, N'Peter', N'Sansen');
GO

By the time the fourth record is entered, since it uses a student number that exists already, the database engine would produce an error:

Msg 2627, Level 14, State 1, Line 2
Violation of UNIQUE KEY constraint 'UQ__Students__DD81BF6C145C0A3F'.
Cannot insert duplicate key in object 'dbo.Students'.
The statement has been terminated.

CHECK Constraints

By default, your table will check your data types to make sure they are valid on entry (or at least check that they can be converted to the types defined in the table. You may want to go above and beyond this. Let’s say you want to verify that a price is positive, or you want to make sure you have a phone number OR an email address for a contact, but you are OK if one of the two is null. You can set these up by defining the check constraint at table definition time, or you can alter the column in your table to have the CHECK constraint.

First, check out the price must be positive CHECK constraint.

CREATE TABLE product
(
     productName VARCHAR(255)
   , price DECIMAL(9,2)
	CONSTRAINT CK_priceIsPositive CHECK (price > 0)
)
--or as an alter
ALTER TABLE ADD CONSTRAINT CK_priceIsPositive CHECK (price > 0)

And then consider the CHECK for phone number OR email is not null.

CREATE TABLE user
(
      userName VARCHAR(255) UNIQUE NOT NULL
    , FirstName VARCHAR(50) NOT NULL
    , LastName VARCHAR(50) NOT NULL
    , PhoneNumber nchar(16)
    , EmailAddress nvarchar(50),
    CONSTRAINT CK_user_hasPhoneOrEmail
	CHECK ((PhoneNumber IS NOT NULL) OR (EmailAddress IS NOT NULL))
)
--or via alter
ALTER TABLE user ADD CONSTRAINT CK_user_hasPhoneOrEmail
   CHECK ((PhoneNumber IS NOT NULL) OR (EmailAddress IS NOT NULL))

Constraints can be useful. But like I mentioned before, you can over use them and cause issues not only by slowing down your inserts, but you could also create more errors for your interface programmers, if they don’t fail gracefully when a user creates an error condition.

If you have any questions, please let me know!