Producing PostgreSQL's ON DISTINCT CASCADE DDL for foreign keys

[sheluchin]

I am looking to create a foreign key with referential integrity as so:

CREATE TABLE order_items (
    product_no integer REFERENCES products ON DELETE RESTRICT,
    order_id integer REFERENCES orders ON DELETE CASCADE,  /* <-- this line */
    quantity integer,
    PRIMARY KEY (product_no, order_id)
);

Documentation.

The closest I've been able to come up with:

(-> (hh/create-table :bar)
    (hh/with-columns
      [[:did :uuid [:default [:gen_random_uuid]]]
       [:foo-id :varchar [:not nil]]
       [[:primary-key :did :foo-id]]
       [[:foreign-key :foo-id]
        [:references :foo]
        [:on-delete :cascade]]]))

but this ends up wrapping the cascade part in parens like ON DELETE(CASCADE), which is not the desired result. Is there any way to achieve the Postgres syntax with HoneySQL at the moment?

Thanks for your work on HoneySQL!

[seancorfield]

I'm going to close this in favor of #439 where I want to track all column specification work and find a generic solution to this sort of thing.

[seancorfield]

The fix I have for #439 would work for your example as follows:

(deftest create-table-issue-437
  (is (= ["CREATE TABLE bar (did UUID DEFAULT GEN_RANDOM_UUID(), foo_id VARCHAR NOT NULL, PRIMARY KEY(did, foo_id), FOREIGN KEY(foo_id) REFERENCES foo(id) ON DELETE CASCADE)"]
         (-> (create-table :bar)
             (with-columns
               [[:did :uuid [:default [:gen_random_uuid]]]
                [:foo-id :varchar [:not nil]]
                [[:primary-key :did :foo-id]]
                [[:foreign-key :foo-id]
                 [:references :foo :id]
                 :on-delete :cascade]])
             (sql/format)))))

If you want just REFERENCES foo, omit :id like this: [:references :foo]