Fork me on GitHub
#sql
<
2021-08-04
>
v3ga21:08:56

Can someone tell me why I’m getting an “architect_id” referenced in foreign key constraint does not exist when I attempt to make the cubes table. I literally copy/pasted the code that I used to create my ‘chamber’ table. ;; Create and Drop Databases (defn create-db-tables [] (do (db/execute! data-source ["create table if not exists architects(id SERIAL PRIMARY KEY, handle VARCHAR(100), password VARCHAR(100))"]) (db/execute! data-source ["create table if not exists books(id serial PRIMARY KEY, title VARCHAR(1000), author VARCHAR(100), genre VARCHAR(50), own BOOLEAN, reading VARCHAR(10), description TEXT, pages INTEGER)"]) (db/execute! data-source ["create table if not exists chamber(id serial PRIMARY KEY, architect_id INTEGER, name VARCHAR(100), created_at TIMESTAMP DEFAULT Now(), CONSTRAINT fk_architect FOREIGN KEY(architect_id) REFERENCES architects(architect_id))"]) (db/execute! data-source ["create table if not exists cubes(id serial PRIMARY KEY, architect_id INTEGER, chamber_id INTEGER, name VARCHAR(100), created_at TIMESTAMP DEFAULT Now(), CONSTRAINT fk_architect FOREIGN KEY(architect_id) REFERENCES architects(architect_id))"]) #_(db/execute! data-source ["create table if not exists cubes(id serial PRIMARY KEY, architect_id INTEGER, chamber_id INTEGER, name VARCHAR(150), created_at TIMESTAMP DEFAULT Now(), CONSTRAINT fk_chamber FOREIGN KEY(chamber_id) REFERENCES chamber(chamber_id))"]) #_(db/execute! data-source ["create table if not exists iotas(book_id, user_id)"])))

dpsutton21:08:22

references architects(architects_id) should probably be references architects(id) right? there is no column named that

dpsutton21:08:35

(but i'm surprised chamber worked in that case)

seancorfield21:08:00

That cubes/`chamber_id` code is commented out, that's why it doesn't fail.

emccue21:08:29

Also, i would name your tables in the singular

emccue21:08:42

so book instead of books

emccue21:08:51

and have explicitly named ids

emccue21:08:58

so book_id instead of id

emccue21:08:09

it feels counter intuitive, but you'll thank yourself later

emccue21:08:42

esp. since clojure has you writing or generating sql directly instead of through a magic layer

v3ga21:08:49

@dpsutton that was it… lol

dpsutton21:08:04

also you don't need the do form for a function body. you get it for free

v3ga21:08:44

@emccue yeah singular feels better. I’m coming from the world of ORM…. I’ll make that change. It did bug me.

dpsutton21:08:48

and in the future, to paste multiline, use triple-backticks <body> triple-backticks

and it looks
nicer on the eyes

dpsutton21:08:15

(and for something that large, use a snippet so it is collapsible and you get syntax highlighting as a bonus)

v3ga21:08:39

@dpsutton noted and I was wondering how that worked. I’ve been avoiding slack for the longest. Still learning the intricacies.

dpsutton21:08:08

no worries. any channel will be happy to help out with formatting and stuff. and it should make your life much easier in the future 🙂

👍 2
seancorfield21:08:24

There's also a #slack-help channel if you have Qs about Slack itself, rather than Clojure, and #community-development if you have Qs about this community. Folks will be happy to help!

👍 2