In short it doesn't really matter as the end result is effectively the same.

i.e. an index, just that the specifically named index rather than generated named index may be less confusing. There may also be a factor in that the name is easily determined/already named should it subsequently be utilised.

• the specifically named being when CREATE INDEX .... is used
• the generated named being an INDEX that is automatically created

Consider this extract from

3.6 UNIQUE constraints

A UNIQUE constraint is similar to a PRIMARY KEY constraint, except that a single table may have any number of UNIQUE constraints. For each UNIQUE constraint on the table, each row must contain a unique combination of values in the columns identified by the UNIQUE constraint. For the purposes of UNIQUE constraints, NULL values are considered distinct from all other values, including other NULLs. As with PRIMARY KEYs, a UNIQUE table-constraint clause must contain only column names — the use of expressions in an indexed-column of a UNIQUE table-constraint is not supported.

In most cases, UNIQUE and PRIMARY KEY constraints are implemented by creating a unique index in the database. (The exceptions are INTEGER PRIMARY KEY and PRIMARY KEYs on WITHOUT ROWID tables.) Hence, the following schemas are logically equivalent:

CREATE TABLE t1(a, b UNIQUE);
CREATE TABLE t1(a, b PRIMARY KEY);
CREATE TABLE t1(a, b);
CREATE UNIQUE INDEX t1b ON t1(b);

• https://www.sqlite.org/lang_createtable.html#unique_constraints

i.e. CREATE TABLE t1(a, b UNIQUE); and CREATE UNIQUE INDEX t1b ON t1(b); are functionally the same other than the name of the first index is system generated whilst the second is named according to the given name.

Demonstration

Consider the following that uses both ways and then outputs the schema:-

CREATE TABLE User1 (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    email TEXT NOT NULL,
    name TEXT
);
CREATE UNIQUE INDEX User_email_key ON User1(email);
CREATE TABLE User2 (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    email TEXT NOT NULL UNIQUE,
    name TEXT
);
SELECT * FROM sqlite_master;

Which results in :-

As you can see the generated index has been created and named according to SQLite naming conventions.

• unlikely to be of use, there is also no associated SQL as it has been generated automatically.

You probably want to add a field on your Course model that defines who created it so you can use the field as a unique constraint on the table itself.

model Course {
  id                Int              @id @default(autoincrement())
  name              String
  members           Enrollment[]
  creatorId         Int
  creator           User             @relation(fields: [creatorId], references: [id])
  @@unique([creatorId, name], name: "courseIdentifier")
}

I had the same issue.

Using Windows 10 and MariaDB (XAMPP). I added also the @unique directive - I already had some users in the table - and still could add users with the same email address without any error messages in the console. However when I opened up the Docker Desktop app, I saw a buch of error messages, regarding duplicate entries (email). Re-deploying prisma seemed to be working in the terminal, but it actually was not.

The answer from @realAlexBarge put me in the right direction:

"Also be careful that the new data model is actually deployed. If you have conflicts with the existing data it might be necessary to use the force flag to overwrite the existing schema anyways if so desired."

So what I did at the end:

1. stopped docker

docker-compose down -v --rmi all --remove-orphans

2. deleted all tables from the database - probably "users" and "migration" is enough. As it seems the "migration" table keeps track of your scheema
3. restarted docker

docker-compose up -d

4. re-deployed prisma

prisma deploy

Hope it helps.

Hey @jasonabullard 👋, welcome to the Prisma community! I'm glad you are enjoying it so far 😄.

the where option only allows parameters that were marked as @unique in the schema.

This is correct! findUnique can only be guaranteed to return a unique row if you are filtering by a field that is actually unique across the table. In the case of a user account, the email field is generally required to be unique, therefore annotating it with the @unique attribute in your Prisma schema makes sense and would enable the use case you outlined above.

If a combination of multiple fields is guaranteed to be unique, you could use a compound unique constraint like so: @@unique([email, password]).

Relevant docs page.

Let me know if I can clarify further!

In Prisma, you can model your relations like this:

model Product {
  id          String        @id @default(uuid())
  name        String
  description String
  sizes       ProductSize[]
}

model ProductSize {
  id          String  @id @default(uuid())
  name        String
  description String
  product     Product @relation(fields: [productId], references: [id])
  productId   String

  @@unique([productId, name])
}

This contains a unique index over a combination of two fields (productId and name). So for each product there can only be a unique named size. Example - (productId - A, Size - 1), (productId - A, Size - 2). Adding another record with (productId - A, Size - 1) would throw an error but (productId - B, Size - 1) will be allowed.