3

Is it way to apply unique by two columns constraint to model using peewee. There are raw way (using SQL()) but I'm finding another.

Updated:

I'm using postgres so I tried to do like @booshong says below then in raw SQL I gave the following (for simplicity transaction related BEGIN and COMMIT omitted and output was intended):

CREATE TABLE IF NOT EXISTS "foo" (
    "id" SERIAL NOT NULL PRIMARY KEY,
    "field_1" VARCHAR(255) NOT NULL,
    "field_2" VARCHAR(255) NOT NULL);

CREATE UNIQUE INDEX IF NOT EXISTS "foo_field_1_field_2" 
    ON "foo" ("field_1", "field_2");

CREATE TABLE IF NOT EXISTS "foo2" (
    "id" SERIAL NOT NULL PRIMARY KEY,
    "field_1" VARCHAR(255) NOT NULL,
    "field_2" VARCHAR(255) NOT NULL, 
    UNIQUE (field_1, field_2));

And as we can see it's different things as I say earlier.

Glech
  • 731
  • 3
  • 14
  • did you read the [documentation](http://docs.peewee-orm.com/en/latest/peewee/models.html#multi-column-indexes)? – booshong Dec 04 '20 at 17:53
  • @booshong , of course. Unless I didn't write here. In peewee you can only create multi index using pretty python syntax. For unique together there aren't something beautiful. So I was forced to use `constraints = [SQL('UNIQUE (field_1, field_2)']`in meta class. – Glech Dec 04 '20 at 22:44
  • maybe i'm not understanding the question. Why can't you just do `indexes = ((('field_1', 'field_2'), True),)` (under `class Meta:`)? – booshong Dec 04 '20 at 23:08
  • Index and constraint are two different things. Do not сonfuse it. – Glech Dec 04 '20 at 23:59
  • what database are you using? Generally (e.g. MySQL), these are the same thing under the hood. The unique constraint needs the index to function. Try both methods and look at the schema definition. On MySQL they will be the same. – booshong Dec 05 '20 at 00:08
  • I'm using postgres. See what attached in question under "updated" – Glech Dec 05 '20 at 22:10
  • 1
    I think there isn't so way yet. Anyway thank you, @booshong, for attention – Glech Dec 05 '20 at 22:14
  • 1
    From what I understand creating a constraint will create an index anyways. I'd suggest just using the Meta.indexes, but if you absolutely insist, you can just specify it in the `constraints=`. – coleifer Dec 05 '20 at 22:22
  • Regarding your edit: yes the SQL commands used by the two approaches are different, but if you can't find any difference between the resulting schema definitions (try `\d ` on those two tables and compare) then they are effectively the same. I don't have a lot of experience with postgres, so maybe there is a difference there, though based on these ([#1](https://stackoverflow.com/questions/23542794/postgres-unique-constraint-vs-index), [#2](https://stackoverflow.com/questions/23542794/postgres-unique-constraint-vs-index)) questions, it seems like it's small. Interesting discussion
     – booshong Dec 05 '20 at 22:34
  • 1
    You're right. Difference between unique index and unique constraint is very small. Difference is in fact that index have b-tree and rdbs forced to keep it updated. I think it unlikely can be performance issue, but fact is fact. – Glech Dec 05 '20 at 23:07

1 Answers1

3

class Foo(BaseModel):
    field_1 = CharField()
    field_2 = CharField()

    class Meta:
        indexes = (
            (('field_1', 'field_2'), True),
        )


class Foo2(BaseModel):
    field_1 = CharField()
    field_2 = CharField()

    class Meta:
        constraints = [SQL('UNIQUE (field_1, field_2)')]


Foo.create_table()
Foo2.create_table()


-- MySQL v8

show create table foo;
>> CREATE TABLE `foo` (
  `id` int NOT NULL AUTO_INCREMENT,
  `field_1` varchar(255) NOT NULL,
  `field_2` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `foo_field_1_field_2` (`field_1`,`field_2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci


show create table foo2;
>> CREATE TABLE `foo2` (
  `id` int NOT NULL AUTO_INCREMENT,
  `field_1` varchar(255) NOT NULL,
  `field_2` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `field_1` (`field_1`,`field_2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

The two tables are equivalent.

booshong
  • 782
  • 6
  • 21