Base: Index design without unique

[lonk]

create table "BookBorrowed1" ("id" bigint primary key, "StudentID" varchar(10), "BookNumber" varchar(10), constraint "StudentIDConstraint1" unique ("StudentID") using asc index "StudentIDConstraint1")

I need to record each student can borrow several books and I also need a fast query.
How can I design index to StudentID without adding unique ?

New Database.odb (2.8 KB)

LibreOffice:
Version: 7.3.4.2 / LibreOffice Community
Build ID: 30(Build:2)
CPU threads: 4; OS: Linux 5.15; UI render: default; VCL: gtk3
Locale: en-US (en_US.UTF-8); UI: en-US
Ubuntu package version: 1:7.3.4-0ubuntu0.22.04.1
Calc: threaded

Base: Embedded Firebird

OS: Ubuntu 22.04 LTS

[flywire]

First table for StudentID unique, second table for BookNumber unique, and third table with BookNumber unique and StudentID.

[Villeroy]

Does “BookNumber” refer to one individual book (as in a library) or does it refer to a publication (like an ISBN)?

[lonk]

Sorry for the inappropriate sample database, I just wanted to learn how to use index without unique.

Let me bring SQL example from over here:
create table "people"("id" int not null, "nickname" varchar(12) not null, "country" char(4), constraint "pk_people" primary key ("id"), constraint "uk_nickname" unique ("nickname") using index "ix_nick")

How can unique be removed by SQL?

And get index as shown below:

[Ratslinger]

Hello,
May not be just what you wanted. Created table:

CREATE TABLE people
(
  id         INT NOT NULL,
  nickname   VARCHAR(12) NOT NULL,
  country    CHAR(4),
  CONSTRAINT pk_people PRIMARY KEY (id)
)

Then added index:

CREATE DESCENDING INDEX IDX_CHANGE
ON people (nickname);

giving:

[lonk]

It’s no wonder that when I created index manually in design mode, Base forced me to save database first and apply index later.