Results 1 to 10 of 10

Thread: Alter table

  1. #1
    Join Date
    Jan 2013
    Posts
    7

    Exclamation Unanswered: Alter table

    Hi,
    I have already created a table and forgot to add in the first row.
    This row should auto-increment, be unique and of type int.

    I have tried using the following statements but i keep getting a syntax error.


    Code:
    alter table 'comp_table'
    add column 'comp_id' int not null auto_increment unique first;
    Code:
    ALTER TABLE 'comp_table' ADD COLUMN 'comp_id' int NOT null
    AUTO_INCREMENT FIRST


    Can anyone tell me what i am doing wrong?

    Thanks.

  2. #2
    Join Date
    Dec 2012
    Posts
    25
    you can try this

    alter table comp_table add comp_id int auto_increment unique key not null first;

    regards,

    --rush

  3. #3
    Join Date
    Jan 2013
    Posts
    7
    i copied this exact statement and i am still getting a syntax error.

  4. #4
    Join Date
    Dec 2012
    Posts
    43
    Hello CM670,

    your first SQL command will work fine if you use ` instead of ' .

    My example:
    Code:
    mysql> alter table `comp_table` add column `comp_id` int not null auto_increment unique first;
    Query OK, 0 rows affected (0.01 sec)
    Records: 0  Duplicates: 0  Warnings: 0

  5. #5
    Join Date
    Jan 2013
    Posts
    7
    Got it now!

    Thanks for all the help

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Enclosing an identifier (e.g. a table name, a column name) in ` or " makes it a quoted identifier which you should avoid in the first place.

    Unless you want to use reserved words or names containing special characters (which is a really bad idea), neither the ` nor the ANSI standard double quotes: " are required at all.

    So the following is just as good:

    Code:
    alter table comp_table  
           add column comp_id int not null auto_increment unique first;
    The single quotes that you were using are required for character literals (aka strings).
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  7. #7
    Join Date
    Dec 2012
    Posts
    43
    Quote Originally Posted by shammat View Post
    Enclosing an identifier (e.g. a table name, a column name) in ` or " makes it a quoted identifier which you should avoid in the first place.

    Unless you want to use reserved words or names containing special characters (which is a really bad idea), neither the ` nor the ANSI standard double quotes: " are required at all.

    So the following is just as good:

    Code:
    alter table comp_table  
           add column comp_id int not null auto_increment unique first;
    The single quotes that you were using are required for character literals (aka strings).
    I agrree with you. But your description is incomplete:

    In MySQL capitalized and not capitalized names of identifiers ( e.g. tablename, column) are two different objects if no enclosing characters are used.

    An example:
    Code:
    mysql> create table Example_Table (xKey integer, xVal integer);
    Query OK, 0 rows affected (0.05 sec)
    select * from example_table will fail , because "example_table" is not the same as "Example_Table" (In my example "thomas" is the name of the database):
    Code:
    mysql> select * from example_table;
    ERROR 1146 (42S02): Table 'thomas.example_table' doesn't exist
    But select * from Example_Table works:
    Code:
    mysql> select * from Example_Table;
    Empty set (0.00 sec)
    Using an export or a database dump mysql use always ` to enclose all identifiers (e.g. tablenames or columns etc.)

    Code:
    mysql> show create table Example_Table;
    -- bibjet's note: unimportant characters deleted
    CREATE TABLE `Example_Table` (
      `xKey` int(11) DEFAULT NULL,
      `xVal` int(11) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    Enclosing an identifier with >>"<< in MySQL results in an error:
    Code:
    mysql> select * from "Example_Table";
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"Example_Table"' at line 1

    Other database system have an other behavior when not enclosing an identifier:

    ORACLE stores the name of an identifier completely in capitalized letters:
    Example_Table --> EXAMPLE_TABLE

    PostgreSQL stores the name of an identifier completely in not capitalized letters:
    Example_Table --> example_table

    For both ORACLE and PostgreSQL database system the enclosing character is the SQL-standard >>"<<.

  8. #8
    Join Date
    Dec 2012
    Posts
    43
    Sorry:

    In my last post I wrote that on MySQL identifier (e.g. table "example_table" and "Example_Table") without encosed in >>`<< are not identical.

    This is only true if the configuration parameter
    Code:
    lower_case_table_name
    of MySQL server is FALSE and effects only on operating systems / filesystems where both capitalized and not capitalized letters in filenames are not identical (e.g. linux).

    MySQL is sometimes very confusing ...

  9. #9
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by bibjet View Post
    MySQL is sometimes very confusing ...
    I agree.

    Everything you said only applies to MyISAM if I'm not mistaken.

    I don't know if the rules for InnoDB tables are just equally chaotic.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  10. #10
    Join Date
    Dec 2012
    Posts
    43
    Quote Originally Posted by shammat View Post
    I agree.

    Everything you said only applies to MyISAM if I'm not mistaken.

    I don't know if the rules for InnoDB tables are just equally chaotic.
    Yes, it's also very surprising for tables using the InnoDB engine, depending on the operating system.
    In my example above I've created the table with the default engine (In MySQL 5.5 it's InnoDB). As you can see the error messages it's different for InnoDB engines too due to tablename is stored as file in the filesystem with the same name like the tablename.

    From the original documentation:
    MySQL :: MySQL 5.5 Reference Manual :: 5.1.4 Server System Variables

    You should not set this variable to 0 if you are running MySQL on a system that has case-insensitive file names (such as Windows or Mac OS X). If you set this variable to 0 on such a system and access MyISAM tablenames using different lettercases, index corruption may result. On Windows the default value is 1. On Mac OS X, the default value is 2.
    So you should not set this variable to 0 if your operating system is Windows and at least one database has tables with a MyISAM engine.

    This confusion is one of many reasons why I prefer PostgreSQL.
    But sometimes you must use an existing database system ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •