Foreign Keys & character limits in Postgres and Clojure JDBC

clojure, jdbc, postgresql

Solution

for foreign key, use `references` as 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)"]))))

Problem

I'm making myself a facebook/twitter combination thing just for fun, but I am not 100% sure about some semantics with JDBC stuff. I am unsure on how to set a field to be a foreign key or how to limit the amount of characters in a varchar field. My code (with comments to show where I need help) is as follows ``` (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"])))) ``` Any help would be appreciated! Thanks in advance!

Original source