Microsoft SQL Server connector

Database schema names

SQL Server does not have an equivalent to the PostgreSQL SET search_path command familiar from PostgreSQL. This means that when you create migrations, you must define the same schema name in the connection URL that is used by the production database. For most of the users this is dbo (the default value). However, if the production database uses another schema name, all the migration SQL must be either edited by hand to reflect the production or the connection URL must be changed before creating migrations (for example: schema=name).

Cascading rules

SQL Server follows the same cascading rules as other database providers - for most relations (with certain restrictions), Prisma runs ON DELETE CASCADE ON UPDATE CASCADE (delete all children from a parent automatically).

However, SQL Server cascading has certain restrictions that do not allow us to create certain kinds of relations. Consider the following schema:

schema.prisma
1model Album {
2  id        Int    @id @default(autoincrement())
3  artist_id Int
4  artist    Artist @relation(fields: [artist_id], references: [id])
5}
6
7model Artist {
8  id     Int      @id @default(autoincrement())
9  albums Album[]
10}

Next, add a favorite album to every artist:

schema.prisma
1model Artist {
2  id             Int      @id @default(autoincrement())
3  album_id       Int
4  favorite_album Album    @relation(fields: [album_id], references: [id], name: "FK_fav_album")
5  albums Album[]
6}

This results in an error:

Introducing FOREIGN KEY constraint `FK__Artist__album_id__2D3D91F9` on table `Artist` may cause cycles or multiple cascade paths. Specify `ON DELETE NO ACTION` or `ON UPDATE NO ACTION`, or modify other FOREIGN KEY constraints.

SQL Server does not allow cyclic cascading, and in our example our relations go from Album to Artist and through another relation from Artist back to Album. In Microsoft's words, the cause for this is:

You receive this error message because in SQL Server, a table cannot appear more than one time in a list of all the cascading referential actions that are started by either a DELETE or an UPDATE statement. For example, the tree of cascading referential actions must only have one path to a particular table on the cascading referential actions tree.

Another, simpler example that is not possible in the first version of Prisma Migrate for SQL Server is a self-reference:

schema.prisma
1model Artist {
2  id      Int @id @default(autoincrement())
3  muse_id Int?
4  muse    Artist @relation(fields: [muse_id], references: [id])
5}

If you want to use relations as described here, you must:

• Create the migration using the --create-only flag
• Modify the cascading rules manually before writing to the database

Destructive changes

Certain migrations will cause more changes than you might expect. For example:

• Adding or removing autoincrement(). This cannot be achieved by modifying the column, but requires recreating the table (including all constraints, indices, and foreign keys) and moving all data between the tables.
• Additionally, it is not possible to delete all the columns from a table (possible with PostgreSQL or MySQL). If a migration needs to recreate all table columns, it will also re-create the table.

Shared default values are not supported

In some cases, user might want to define default values as shared objects:

default_objects.sql
1CREATE DEFAULT catcat AS 'musti';
2
3CREATE TABLE cats (
4    id INT IDENTITY PRIMARY KEY,
5    name NVARCHAR(1000)
6);
7
8sp_bindefault 'catcat', 'dbo.cats.name';

Using the stored procedure sp_bindefault, the default value catcat can be used in more than one table. The way Prisma manages default values is per table:

default_per_table.sql
1CREATE TABLE cats (
2    id INT IDENTITY PRIMARY KEY,
3    name NVARCHAR(1000) CONSTRAINT DF_cat_name DEFAULT 'musti'
4);

The last example, when introspected, leads to the following model:

schema.prisma
1model cats {
2  id   Int     @id @default(autoincrement())
3  name String? @default("musti")
4}

And the first doesn't get the default value introspected:

schema.prisma
1model cats {
2  id   Int     @id @default(autoincrement())
3  name String?
4}

If using Prisma Migrate together with shared default objects, changes to them must be done manually to the SQL.

Cannot use column with UNIQUE constraint and filtered index as foreign key

Microsoft SQL Server only allows one NULL value in a column that has a UNIQUE constraint. For example:

• A table of users has a column named license_number
• The license_number field has a UNIQUE constraint
• The license_number field only allows one NULL value

The standard way to get around this issue is to create a filtered unique index that excludes NULL values. This allows you to insert multiple NULL values. If you do not create an index in the database, you will get an error if you try to insert more than one null value into a column with Prisma Client.

However, creating an index makes it impossible to use license_number as a foreign key in the database (or a relation scalar field in corresponding Prisma Schema)