Open heroin-moose opened 2 years ago
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.
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.
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.
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")
);
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.
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:
In this case interface record is uniquely identified by
(name, device_name)
tuple, making it easy to search and delete. Having a single uniqueid
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.