Create table column named foreign 1

[machinetype]

Hi,

I'm using MySQL 4.1.7 on FreeBSD 5.3. I'm trying to add a column named foreign and it keeps kicking back an error. I realize foreign is a function call of sorts...but is there a workaround? I've tried adding it using the add table syntax and alter table...both are unsuccessful.

Thanks.

 

[ericbrunson]

'foreign' is a reserved word in sql. You can name it foreign if you quote it properly, but you'd have to specially quote it every time you referenced it. Why not just think of a different name.

 

[machinetype]

Foreign" as in foreign movies. I'm running php which fetches the column names, returns them with checkboxes, allows the user to select multiple check boxes, then returns movies in the specified genres.

Renaming it may be confusing for the user, unless there's some way to alias it, but that would most likely muck up my script....

I've tried quoting it with double, single and back ticks but nothing seemed to work...am I not quoting properly?....

Thanks for the suggestions.

 

[Deleted]

To create a table column with reserved words use backticks in your sql statement.

EX:

INSERT INTO table (`foreign`,`desc`) VALUES ('blah blah','more blah');

http://unixjunky.com

 

[sleipnir214]

Although it can be done, it is a very bad idea.


Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[machinetype]

I'm actually trying to *create* a table with a column named foreign.

eg.

create table movies (
-> title varchar(20),
-> director varchar(20),
-> independent tinyint(1) NOT NULL DEFAULT '0',
-> foreign tinyint(1) NOT NULL DEFAULT '0'
);

I have also tried to first create the table without foreign and adding later with alter table to no avail:

alter table movies add foreign tinyint(1) NOT NULL DEFAULT '0' after independent;

Thanks.

 

[Deleted]

Did you try back ticking the column name?

http://unixjunky.com

 

[machinetype]

D'oh...thought I tried that as my first step...apparently not because it just worked. Thanks.

Someone stated it was a very bad idea...is this because it's breaking standards?... or can it cause other damage?....

 

[Deleted]

http://dev.mysql.com/doc/mysql/en/Reserved_words.html

Someone stated it was a very bad idea...is this because it's breaking standards?

No it is not.. It could get messy though if all your columns are reserved words though.

But in your case I wouldn't worry.

http://unixjunky.com

 

[ericbrunson]

create table "insert" 
( "references"  varchar(11),
  "timestamp"  date,
  "varchar"    timestamp default now(),
  "datetime"   integer,
  "distinct"   decimal(2,1),
  key "order by" ( "references" ),
  constraint "distinct key" 
    foreign key ( "distinct" )
    references "on delete cascade" ( "limit" )
);

mod: +1; ludicrous

 

[sleipnir214]

machinetype:
It's a bad idea because "foreign" is a MySQL reserved word. Using reserved words for identifiers is simply a bad practice.


Want the best answers? Ask the best questions!

TANSTAAFL!!