Thread: syntax error foreign key
db=# ALTER TABLE ONLY table ADD CONSTRAINT "$1" FOREIGN KEY (user) REFERENCES users(user_id); ERROR: syntax error at or near "user" at character 56 I'm either blind or something besides syntax can cause this error. no?
On Aug 16, 2005, at 9:37 PM, Matthew Terenzio wrote: > db=# ALTER TABLE ONLY table ADD CONSTRAINT "$1" FOREIGN KEY (user) > REFERENCES users(user_id); > ERROR: syntax error at or near "user" at character 56 > > I'm either blind or something besides syntax can cause this error. no? > > changed the field name to userid and it;s fine. Can't user the word user for field that references?
On Tue, Aug 16, 2005 at 10:09:14PM -0400, Matthew Terenzio wrote: > >db=# ALTER TABLE ONLY table ADD CONSTRAINT "$1" FOREIGN KEY (user) > >REFERENCES users(user_id); > >ERROR: syntax error at or near "user" at character 56 > > > >I'm either blind or something besides syntax can cause this error. no? > > changed the field name to userid and it;s fine. Can't user the word > user for field that references? See "Identifiers and Key Words" in the "SQL Syntax" chapter of the documentation, in particular the part that talks about quoted identifiers. Here's a link for the latest version: http://www.postgresql.org/docs/8.0/static/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS -- Michael Fuhr
Matthew Terenzio <matt@jobsforge.com> writes: > On Aug 16, 2005, at 9:37 PM, Matthew Terenzio wrote: > >> db=# ALTER TABLE ONLY table ADD CONSTRAINT "$1" FOREIGN KEY (user) >> REFERENCES users(user_id); >> ERROR: syntax error at or near "user" at character 56 >> >> I'm either blind or something besides syntax can cause this error. no? >> >> > > changed the field name to userid and it;s fine. Can't user the word > user for field that references? USER is an SQL keyword; you have to put it in double quotes to use it as an identifier. -Doug
Matthew Terenzio wrote: > > On Aug 16, 2005, at 9:37 PM, Matthew Terenzio wrote: > >> db=# ALTER TABLE ONLY table ADD CONSTRAINT "$1" FOREIGN KEY (user) >> REFERENCES users(user_id); >> ERROR: syntax error at or near "user" at character 56 >> >> I'm either blind or something besides syntax can cause this error. no? >> >> > > changed the field name to userid and it;s fine. Can't user the word > user for field that references? probably if you quote it. Sincerely, Joshua D. Drake > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster