# Create Database Scripts in T-SQL

Turn a design into a script that builds it - CREATE DATABASE, CREATE TABLE with types, NOT NULL, IDENTITY and DEFAULT, and constraints for primary keys, foreign keys, UNIQUE and CHECK.

Journeyman · T-SQL · SQL · Updated November 3, 2026

## 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

| Command | Does |
|---|---|
| `CREATE` | Makes a new database, table, view... |
| `ALTER` | Changes one that exists: adds a column, adds a constraint |
| `DROP` | Deletes it, with everything in it |

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

## The database

```sql
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:

```sql
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

| Type | Holds | Example |
|---|---|---|
| `int` | Whole numbers | `PetId`, quantities |
| `nvarchar(n)` | Text of up to `n` characters, in any language | Names, emails |
| `decimal(p, s)` | Exact decimals: `p` digits in all, `s` after the point | `Fee decimal(8, 2)`, up to 999999.99 |
| `money` | Amounts of money, as Northwind uses | Prices |
| `bit` | 1 or 0, true or false | `Discontinued` |
| `date` | A date | `BirthDate` |
| `datetime2` | A date and time | `VisitDate` |

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.

| Constraint | Rule | In PetClinic |
|---|---|---|
| `PRIMARY KEY` | The column (or columns) is unique and never `NULL` | `PK_Pets` on `PetId` |
| `FOREIGN KEY ... REFERENCES` | The value must exist in the other table's key | `FK_Pets_Owners`: a pet's `OwnerId` must be a real owner |
| `UNIQUE` | No two rows have the same value | Two owners can't share an email |
| `CHECK` | A condition every row must meet | `CK_Visits_Fee`: no negative fees |

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

```sql
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:

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

```text
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.

```text
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 `INSERT`s 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)](https://www.ask-codey.com/glossary/data-definition-language-ddl)
- [CREATE TABLE](https://www.ask-codey.com/glossary/create-table)
- IDENTITY
- [Constraint](https://www.ask-codey.com/glossary/constraint)
- [UNIQUE Constraint](https://www.ask-codey.com/glossary/unique-constraint)
- [CHECK Constraint](https://www.ask-codey.com/glossary/check-constraint)

## Cheat sheets

- [Create Database Scripts in T-SQL](https://www.ask-codey.com/cheat-sheets/foundations-of-database-design-create-database-scripts-in-t-sql-cheat-sheet)

Source: https://www.ask-codey.com/lessons/foundations-of-database-design-create-database-scripts-in-t-sql