Provide support for composite keys

[heroin-moose]

It's common to have the table that is natural to describe as two columns (or more) columns instead of just one. For example, consider a Device that has multiple child Interface. Say, for servers ns[1-4].example.com there are interfaces eth0, eth1 and eth2. So, it can be described as such:

CREATE TABLE devices (
	name TEXT PRIMARY KEY
);

CREATE TABLE interfaces (
	name        TEXT,
	device_name TEXT,

	PRIMARY KEY (name, device_name),

	FOREIGN KEY (device_name)
	 REFERENCES devices(name)
	  ON DELETE CASCADE
	  ON UPDATE CASCADE
);

In this case interface record is uniquely identified by (name, device_name) tuple, making it easy to search and delete. Having a single unique id column does not bring any value because removing an interface still happens by (name, device_name) query. It would be good to have composite keys in the table like sea-orm for example.

[kurtbuilds]

Can you elaborate on why you need the composite primary key instead of adding an id column and having a unique constraint on (name, device_name)?

It's possible in SQL, so it certainly makes sense to be able to model this, but my understanding is this requirement is rather uncommon. Many articles online argue against ever using composite primary keys. (Other articles argue for it, so it's certainly not settled.)

Right now, I think it makes sense to prioritize other features before this.

[heroin-moose]

It saves the trouble of doing additional SELECT to get the results. If a pair of (name, device_name) already describes the entity, having a distinct Id that API users must query first by the very same (name, device_name) pair is more complex for no real benefit.

[AndrewRademacher]

I think the core use case here for a composite primary key is when you are defining a join table. Say for example you have two tables.

CREATE TABLE user(
    id    int    PRIMARY KEY,
    ...
);

CREATE TABLE role(
    id    int   PRIMARY KEY,
    ...
);

And you want to have a many-to-many association between the two, you would create the following table:

CREATE TABLE user_role(
    user_id    int,
    role_id    int,
    PRIMARY KEY (user_id, role_id)
);

In this case you need to either support the composit key in the Model macro, thus getting a version of get_one that can take a tuple, or have a way to define a Model with no primary key as far as ormlite is concerned that the user can then call the select filter to get ahold of the value they are looking for.

[b2vn]

IMO, the main reason for needing composite keys is, as mentioned in the original post "It's common to have..."

We have a concrete situation where we are migrating one component to rust, and it needs to handle the existing data format. I have been playing around with ormlite and it is by far my favourite orm so far (simple to use, super lean). Only huge problem is that we have composite keys in the database.

The problem looks something like this, where multiple blockdata can have the same metadata:

CREATE TABLE "metadata" (
  "metadata_id" INTEGER PRIMARY KEY AUTOINCREMENT,
  "timestamp" VARCHAR(30) NOT NULL,
  "metadata" TEXT NOT NULL
);

CREATE TABLE "blockdata" (
  "metadata_id" INTEGER NOT NULL REFERENCES "meta" ("metadata_id") ON DELETE CASCADE,
  "name" VARCHAR(64) NOT NULL,
  "extension" TEXT NOT NULL,
  "data" BLOB,
  PRIMARY KEY ("metadata_id", "name")
);

[kurtbuilds]

What actually happens when you create a ormlite::Model for the blockdata table? If you put primary_key on the metadata_id column, I expect the fetch_one method will return a random row rather than the correct one, but other things should mostly work?

Not to say this shouldn't be fixed. I recognize the use case you're talking about and it does make sense to support it. Just trying to understand to what degree it's broken currently.