Allow `DEFERRABLE` or `modifyEnd/modifyStart` to constraints

[rubenferreira97]

Currently we can't set a constraint (Unique, FK, etc...) as [DEFERRABLE | NOT DEFERRABLE] [INITIALLY DEFERRED | INITIALLY IMMEDIATE] on ALTER TABLE nor CREATE TABLE.

Example:

ALTER TABLE tablename
   DROP CONSTRAINT constraintname,
   ADD  CONSTRAINT constraintname UNIQUE(columnname) DEFERRABLE;

db.schema.alterTable('tablename').dropConstraint('constraintname').execute();
db.schema.alterTable('tablename').addUniqueConstraint('constraintname', ['columnname']).execute(); // No DEFERRABLE nor .modifyEnd 

[koskimas]

We should add deferrable() and initiallyDeferred() methods. I don't really understand when initially immediate is useful since that's the default. But it can be added too I guess.

This applies to all constraints, including the primary key.

[rubenferreira97]

@koskimas I think initially immediate makes sense if a constraint can be changed (not all dbs allow this). An example would be a constraint created with initially deferred and now we want to change it to initially immediate with ALTER CONSTRAINT. As far as I know initially here means the default transaction "deferred mode". If we don't change it (in Postgres via SET CONSTRAINTS { ALL | name [, ...] } { DEFERRED | IMMEDIATE }) it will mantain the value declared after the initially keyword.

For the same reason I think if initially immediate is added to Kysely NOT DEFERRABLE/notDeferrable() should also be taken to consideration. We could change a constraint that was DEFERRABLE to NOT DEFERRABLE. Or maybe ignore that a constraint can be changed at all and don't implement those.

[naorpeled]

@igalklebanov @koskimas can you assign me to this issue?

Thanks 😎 🙏

[naorpeled]

Sorry guys, been super swamped with stuff to do, it's better if someone else will take this 🙏

[anirudh1713]

Hi @igalklebanov @koskimas I'd love to take this on if its okay. Let me know.

Thanks.

[igalklebanov]

It's yours. Have fun! 🙂

[anirudh1713]

Hey all! I apologize for the delay here 🙏, I'm unassigning this for now as I've been busy and will be busy till mid August due to some unexpected things (In good way).

[viraxslot]

Hi all, I'm on this issue.

[viraxslot]

@koskimas, Hi, according to the PostgreSQL documentation, DEFERRABLE is applicable to UNIQUE, PRIMARY KEY, EXCLUDE, and REFERENCES constraints only. Therefore, in kysely we cannot use it for the CheckConstraintNode. It seems that creating a separate DeferrableConstraintNode interface is the wrong way of thinking, right? So I just need to duplicate the logic in PrimaryKeyConstraintNode, UniqueConstraintNode and ForeignKeyConstraintNode to avoid future code limitations. Am I correct?

[JDvorak]

Is there a way to hack around this in the meanwhile using raw sql within a transaction?