Create Database Scripts in T-SQL

After this lesson you can

• Write a script that creates a database and its tables, with types, NOT NULL, IDENTITY and DEFAULT
• Add primary key, foreign key, UNIQUE and CHECK constraints

Key takeaways

• A create script builds the database from nothing, and DROP DATABASE IF EXISTS at the top lets you run it again to start fresh.
• Give every column a type that fits its data, and make it NOT NULL unless a value may be missing.
• An IDENTITY(1, 1) column is numbered by SQL Server, and a DEFAULT gives a value when an INSERT leaves the column out.
• Named PRIMARY KEY, FOREIGN KEY, UNIQUE and CHECK constraints make the database itself refuse bad data, whatever app sends it.
• Create tables parents first, such as Owners before Pets, and drop them children first.

A design becomes real through a script: a .sql file that builds the database from nothing. Keeping it as a script, not clicks in SSMS, means you can run it again on another computer, or start fresh whenever you want. (That's what Northwind.sql does.)

This lesson builds PetClinic, a small database for a vet's office: owners, their pets, the vets, and each visit.

Commands that build things

These are DDL, the data definition language: they change the structure, where INSERT, UPDATE and DELETE change the data.

The database

USE master;
GO

DROP DATABASE IF EXISTS PetClinic;
GO

CREATE DATABASE PetClinic;
GO

USE PetClinic;
GO

USE master comes first, because SQL Server can't drop the database your window is using. DROP DATABASE IF EXISTS deletes any old copy, so the script can run again and start fresh. USE switches to the new database, so the tables are made there and not wherever SSMS happened to be pointing. Each GO ends a batch; CREATE DATABASE must finish before the database can be used.

Tables

CREATE TABLE lists each column with its type, and whether it may be empty:

CREATE TABLE Owners
(
    OwnerId int IDENTITY(1, 1) NOT NULL,
    FirstName nvarchar(30) NOT NULL,
    LastName nvarchar(30) NOT NULL,
    Phone nvarchar(20) NULL,
    Email nvarchar(100) NULL,
    CONSTRAINT PK_Owners PRIMARY KEY (OwnerId)
);

CREATE TABLE Pets
(
    PetId int IDENTITY(1, 1) NOT NULL,
    OwnerId int NOT NULL,
    Name nvarchar(30) NOT NULL,
    Species nvarchar(20) NOT NULL,
    BirthDate date NULL,
    CONSTRAINT PK_Pets PRIMARY KEY (PetId),
    CONSTRAINT FK_Pets_Owners FOREIGN KEY (OwnerId) REFERENCES Owners (OwnerId)
);

CREATE TABLE Vets
(
    VetId int IDENTITY(1, 1) NOT NULL,
    Name nvarchar(60) NOT NULL,
    CONSTRAINT PK_Vets PRIMARY KEY (VetId)
);

CREATE TABLE Visits
(
    VisitId int IDENTITY(1, 1) NOT NULL,
    PetId int NOT NULL,
    VetId int NOT NULL,
    VisitDate datetime2 NOT NULL CONSTRAINT DF_Visits_VisitDate DEFAULT SYSDATETIME(),
    Reason nvarchar(200) NOT NULL,
    Fee decimal(8, 2) NOT NULL,
    CONSTRAINT PK_Visits PRIMARY KEY (VisitId),
    CONSTRAINT FK_Visits_Pets FOREIGN KEY (PetId) REFERENCES Pets (PetId),
    CONSTRAINT FK_Visits_Vets FOREIGN KEY (VetId) REFERENCES Vets (VetId),
    CONSTRAINT CK_Visits_Fee CHECK (Fee >= 0)
);

Types

Choose the type that fits the data: a date column can't hold "next Tuesday", and a decimal column can't hold "about ten dollars".

NOT NULL, IDENTITY and DEFAULT

• NOT NULL means every row must have a value. NULL allows a missing one: a pet's birth date may be unknown.
• IDENTITY(1, 1) makes SQL Server number the rows itself, starting at 1 and going up by 1. You never insert a value into an identity column.
• DEFAULT gives a value when an INSERT leaves the column out: a visit is dated now, by SYSDATETIME(), unless another date is given.

Constraints

Constraints are rules the database enforces on every insert and update. Each has a name, so its error messages say which rule was broken.

Constraints can also be added after the tables exist, with ALTER TABLE:

ALTER TABLE Owners
ADD CONSTRAINT UQ_Owners_Email UNIQUE (Email);

Now the database itself refuses bad data, whatever app sends it. A pet for an owner who doesn't exist:

INSERT INTO Pets (OwnerId, Name, Species)
VALUES (99, 'Ghost', 'Cat');

Msg 547, Level 16, State 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Pets_Owners". The conflict occurred in database "PetClinic", table "dbo.Owners", column 'OwnerId'.

A negative fee breaks CK_Visits_Fee, and a second owner with the same email breaks UQ_Owners_Email, each with its own message.

Order matters

Tables must be created parents first: Owners before Pets, because Pets refers to it. They must be dropped children first: SQL Server won't drop Owners while Pets still refers to it.

Msg 3726, Level 16, State 1
Could not drop object 'Owners' because it is referenced by a FOREIGN KEY constraint.

Try it

Put the database, the tables and a few INSERTs in one file, PetClinic.sql, and run it in SSMS. Then join the tables to see every visit with its owner, pet and vet. Run it again: DROP DATABASE IF EXISTS starts it fresh each time.

Check yourself

2 questions to try on the lesson page.

So what

A create script is the design, written so a computer can build it: tables with the right types, keys that link them, and constraints that keep bad data out for every app that ever connects.

Key terms

• Data Definition Language (DDL)
• CREATE TABLE
• IDENTITY
• Constraint
• UNIQUE Constraint
• CHECK Constraint

Cheat sheets

• Create Database Scripts in T-SQL