Postgres和Clojure JDBC中的外键和字符限制

Question

我把自己做成一个facebook/twitter的组合，只是为了好玩，但我对JDBC的一些语义并不是百分之百确定。我不确定如何将字段设置为外键，或者如何限制varchar字段中的字符数。

我的代码(带有注释以显示我需要帮助的位置)如下

(ns twitface.models.migration
  (:require [clojure.java.jdbc :as sql]))

(defn create-tables []
  (sql/with-connection (System/getenv "DATABASE_URL")
    (do 
      (sql/create-table :users
                        [:id :serial "PRIMARY KEY"]
                        [:email :varchar "NOT NULL"]
                        [:password :varchar "NOT_NULL"]
                        [:full-name :varchar "NOT NULL"]
                        [:pref-name :varchar]
                        [:created_at :timestamp "NOT NULL" "DEFAULT CURRENT_TIMESTAMP"])
      (sql/create-table :relations
                        [:id :serial "PRIMARY KEY"]
                        [:relating-user :numeric] ; foreign key
                        [:related-user :numeric]  ; foreign key
                        [:status :varchar])
      (sql/create-table :posts
                        [:id :serial "PRIMARY KEY"]
                        [:user :numeric "NOT NULL"]    ; foreign key
                        [:destination-user :numeric "NOT NULL"] ; foreign key
                        [:message :varchar "NOT NULL"] ; 180 char limit
                        [:created_at :timestamp "NOT NULL" "DEFAULT CURRENT_TIMESTAMP"])
      (sql/create-table :comments
                        [:id :serial "PRIMARY KEY"]
                        [:user :numeric "NOT NULL"]    ; foreign key
                        [:message :varchar "NOT NULL"] ; 180 char limit
                        [:created_at :timestamp "NOT NULL" "DEFAULT CURRENT_TIMESTAMP"]))))

任何帮助都将不胜感激！提前感谢！

Answer 1

对于外键，使用references作为sql：

(defn create-tables []
  (sql/with-connection (System/getenv "DATABASE_URL")
    (do
      (sql/create-table :users
                        [:id :serial "PRIMARY KEY"]
                        [:email "varchar(50)" "NOT NULL"]
                        [:password "varchar(30)" "NOT NULL"])
      (sql/create-table :relations
                        [:id :serial "PRIMARY KEY"]
                        [:related_user :serial "references users (id)"]
                        [:status "varchar(10)"]))))

Answer 2

如果找不到文档，可以查看项目中包含的测试，如果这不能帮助您查看源代码--这是使用开源最好的地方。我尝试查看测试，发现您可以使用VARCHAR(180)而不是:varchar来指定列的限制，同样，您也可以使用普通的SQL结构来指定外键