Foreign key DDL support

[philoskim]

I want to translate the following sql statement to honeysql.

CREATE TABLE user (
  id VARCHAR(255) NOT NULL PRIMARY KEY,
  company_id INT NOT NULL,      # 협력사_id, 유통사_id
  name VARCHAR(255) NOT NULL,
  password VARCHAR(255) NOT NULL,
  created_time DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (company_id) REFERENCES company(id)
) CHARSET=utf8;

So I tried it as follows.

(def user*
  {:create-table [:user :if-not-exists]
   :with-columns
   [[:id [:varchar 255] [:not nil] [:primary-key]]
    [:company-id :int [:not nil]]
    [:name [:varchar 255] [:not nil]]
    [:password [:varchar 255] [:not nil]]
    [:created-time :datetime [:default :CURRENT_TIMESTAMP]]
    [:updated-time :datetime [:default :CURRENT_TIMESTAMP]
     :on :update :CURRENT_TIMESTAMP]
    [:foreign-key :company-id :references [:company :id]] ]})

(honey.sql/format user*)
; ["CREATE TABLE IF NOT EXISTS user (
;   id VARCHAR(255) NOT NULL PRIMARY KEY,
;   company_id INT NOT NULL,
;   name VARCHAR(255) NOT NULL,
;   password VARCHAR(255) NOT NULL,
;   created_time DATETIME DEFAULT CURRENT_TIMESTAMP,
;   updated_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
;   foreign_key COMPANY_ID REFERENCES COMPANY(ID)
; )"]

What I want is FOREIGN KEY company-id REFERENCES company(id)), not foreign_key COMPANY_ID REFERENCES COMPANY(ID)).

How can I achieve what I want?

[seancorfield]

I think the closest you'll get right now is [:foreign :key ...] but that won't format case as you'd expect. I'll have to look at the syntax that supports primary key etc and add something equivalent for foreign key.

[seancorfield]

Ah, it looks like [[:foreign-key :company-id] [:references :company :id]] will get you very close:

FOREIGN KEY(company_id) REFERENCES COMPANY(ID)

(I may be able to tweak the casing of company(id) in the future)

[seancorfield]

:foreign-key is supported and documented but is currently expected to be an annotation on the column definition itself (see https://cljdoc.org/d/com.github.seancorfield/honeysql/2.2.861/doc/getting-started/sql-special-syntax-#column-descriptors which could certainly have better examples).

[philoskim]

Thanks for your quick reply!

Given your explanation that :foreign-key is supported and documented but is currently expected to be an annotation on the column definition itself, I tried it as follows.

(def user*
  {:create-table [:user :if-not-exists]
   :with-columns
   [[:id [:varchar 255] [:not nil] [:primary-key]]
    [:company-id :int [:not nil] [:foreign-key] [:references :company :id]] ]})

(honey.sql/format user*)
; ["CREATE TABLE IF NOT EXISTS user (
;    id VARCHAR(255) NOT NULL PRIMARY KEY,
;    company_id INT NOT NULL FOREIGN KEY REFERENCES COMPANY(ID)
;  )"]

The result is not what I expected, which is REFERENCES company(id). Is this a bug?

• honeysql version: 2.2.861

[seancorfield]

Isn't that exactly what I said above about the casing of company(id)?

[philoskim]

The casing of the example here (https://cljdoc.org/d/com.github.seancorfield/honeysql/2.2.861/doc/getting-started/sql-special-syntax-#constraint-default-references) is properly handled. So I asked about it.

[seancorfield]

Looks like a doc bug. What I showed above matches what you experienced and is how the code currently works.

When/if I can fix the way :references works -- per my comment above -- the docs will magically become correct there 😄

[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]

With the fix I'm working on for #439 your example becomes:

(deftest references-issue-386
  (is (= ["CREATE TABLE IF NOT EXISTS user (id VARCHAR(255) NOT NULL PRIMARY KEY, company_id INT NOT NULL, name VARCHAR(255) NOT NULL, password VARCHAR(255) NOT NULL, created_time DATETIME DEFAULT CURRENT_TIMESTAMP, updated_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY(company_id) REFERENCES company(id))"]
         (-> {:create-table [:user :if-not-exists]
              :with-columns
              [[:id [:varchar 255] [:not nil] [:primary-key]]
               [:company-id :int [:not nil]]
               [:name [:varchar 255] [:not nil]]
               [:password [:varchar 255] [:not nil]]
               [:created-time :datetime [:default :CURRENT_TIMESTAMP]]
               [:updated-time :datetime [:default :CURRENT_TIMESTAMP]
                :on :update :CURRENT_TIMESTAMP]
               [[:foreign-key :company-id] [:references :company :id]]]}
             (sql/format)))))

Note that :foreign-key and :references are "function-like" expressions.