dropped uniqueness constraint with explicit primary keys

[Philonous]

Given the schema

Foo
    foo Int
    Primary foo
    bar Int
    UniqueBar bar

persistent creates the table as

Migrating: CREATE TABLE "foo"("foo" INTEGER NOT NULL,"bar" INTEGER NOT NULL, PRIMARY KEY ("foo"))

omitting the

CONSTRAINT "unique_bar" UNIQUE ("bar")

that is added if the explicit primary key directive is dropped.

However, the UniqueBar data type is still created, but trying to query on it results in an error:

{-# LANGUAGE TypeFamilies #-}
{-# LANGUAGE TemplateHaskell #-}
{-# LANGUAGE GADTs #-}
{-# LANGUAGE GeneralizedNewtypeDeriving #-}
{-# LANGUAGE MultiParamTypeClasses #-}


module Test where

import           Database.Persist
import           Database.Persist.TH
import           Control.Monad.Logger
import           Database.Persist.Sqlite as Sqlite

share [mkPersist sqlSettings, mkMigrate "migrateAll"] [persistLowerCase|
Foo
    foo Int
    Primary foo
    bar Int
    UniqueBar bar
|]

test = runStderrLoggingT . Sqlite.withSqliteConn ":memory:" $
       \backend -> do
    runSqlConn (runMigration migrateAll) backend

    flip runSqlConn backend $ do
        insert $ Foo 3 5
        getBy (UniqueBar 5)

ghci output:

λ> test
Migrating: CREATE TABLE "foo"("foo" INTEGER NOT NULL,"bar" INTEGER NOT NULL, PRIMARY KEY ("foo"))
[Debug#SQL] "CREATE TABLE \"foo\"(\"foo\" INTEGER NOT NULL,\"bar\" INTEGER NOT NULL, PRIMARY KEY (\"foo\"))" [] @(persistent-2.1.1:Database.Persist.Sql.Raw ./Database/Persist/Sql/Raw.hs:55:18)

[Debug#SQL] "INSERT INTO \"foo\"(\"foo\",\"bar\") VALUES(?,?)" [PersistInt64 3,PersistInt64 5] @(persistent-2.1.1:Database.Persist.Sql.Raw ./Database/Persist/Sql/Raw.hs:55:18)

[Debug#SQL] "SELECT \"foo\",\"bar\" FROM \"foo\" WHERE \"bar\"=?" [PersistInt64 5] @(persistent-2.1.1:Database.Persist.Sql.Raw ./Database/Persist/Sql/Raw.hs:42:26)

*** Exception: Foo: fromPersistValues failed on: [PersistInt64 5]

[Philonous]

The marshalling errors seems to be an independent problem and also occurs with postgres. I have created another issue: #342

[gregwebs]

#342 is fixed now, so this should just be an issue of a missing uniqueness constraint.

[tebello-thejane]

Any progress on getting uniqueness constraints to coexist with primary keys on sqlite? I can at least confirm that I do not get this issue on Postgres, but it makes my sqlite-based "unit" tests slightly less useful. :-)

[parsonsmatt]

Tagging this one as newcomer.

It should be relatively straightforward to look at the migration code in persistent-postgresql and ensure that the behavior for unique keys is the same in sqlite.

[SanchayanMaity]

@parsonsmatt Can I take this up?

[psibi]

@SanchayanMaity Sure, I have assigned this to you.

[kderme]

Is this still an issue after #922 and #342 fixed?

[parsonsmatt]

@SanchayanMaity Go for it 😄 Let me know if you have any questions.

@kderme I don't know. A good first step is for @SanchayanMaity to verify whether this is still happening, possibly by writing a test.

[parsonsmatt]

@SanchayanMaity #930 So it looks like @kderme did implement the fix for this and it was released. However, we don't appear to have any tests for it. If you'd like to write a test for it, that'd be great! If not, that's cool too 😄

I am going to close this issue out. You can continue to use this to track the test, or we can create a new one. I'll let you decide.