MySQL

seanmayhew

All I want to do is change the primary key from an int with auto increment to a varchar(50) and then drop the name column all together and apparently this is impossible I cant even get rid of the damned primary key could someone help me please.

mysql> drop field manufactureid;
ERROR 1064: You have an error in your SQL syntax near 'field manufactureid' at line 1
mysql> alter table drop manufactureid;
ERROR 1064: You have an error in your SQL syntax near 'drop manufactureid' at line 1
mysql> alter manufacture drop manufactureid;
ERROR 1064: You have an error in your SQL syntax near 'manufacture drop manufactureid' at line 1
mysql> alter table manufacture drop primary key;
ERROR 1075: Incorrect table definition; There can only be one auto column and it must be defined as a key
mysql> alter table manufacture drop PRIMARY KEY;
ERROR 1075: Incorrect table definition; There can only be one auto column and it must be defined as a key
mysql> alter table manufacture
-> change manufacture id varchar(100) primary key not null;
ERROR 1054: Unknown column 'manufacture' in 'manufacture'
mysql> alter table manufacture
-> change manufactureid varchar(100) primary key not null;
ERROR 1064: You have an error in your SQL syntax near 'varchar(100) primary key not null' at line 2
mysql> alter table manufacture
-> change manufactureid varchar(100);

ERROR 1064: You have an error in your SQL syntax near 'varchar(100)' at line 2
mysql> alter table manufacture
-> change manufactureid varchar(50);

ERROR 1064: You have an error in your SQL syntax near 'varchar(50)' at line 2
mysql> alter table manufacture drop primary key;
ERROR 1075: Incorrect table definition; There can only be one auto column and it must be defined as a key
mysql> alter table manufacture drop primary key;
ERROR 1075: Incorrect table definition; There can only be one auto column and it must be defined as a key
mysql>

kellog

alter table manufacture change manufactureid manufactureid varchar(50) not null primary key;

alter table manufacture drop name;

Note, manufactureid will no longer auto-increment since it is not a numeric coulmn anymore. Also, specifying auto_increment in the alter statement for manufactureid will cause for it to error.

Originally posted by seanmayhew
All I want to do is change the primary key from an int with auto increment to a varchar(50) and then drop the name column all together and apparently this is impossible I cant even get rid of the damned primary key could someone help me please.

mysql> describe manufacture;
+------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+------------------+------+-----+---------+----------------+
| manufactureid | int(11) unsigned | | PRI | NULL | auto_increment |
| name | varchar(20) | | | | |
| address | varchar(50) | | | | |
| city | varchar(20) | | | | |
| state | char(2) | | | | |
| zip | varchar(20) | | | | |
| phone | varchar(20) | | | | |
| contactfirstname | varchar(20) | YES | | | |
| contactlastname | varchar(20) | YES | | | |
| contactnumber | varchar(20) | YES | | | |
| webaddress | varchar(20) | YES | | | |
+------------------+------------------+------+-----+---------+----------------+
11 rows in set (0.00 sec)

mysql> drop field manufactureid;
ERROR 1064: You have an error in your SQL syntax near 'field manufactureid' at line 1
mysql> alter table drop manufactureid;
ERROR 1064: You have an error in your SQL syntax near 'drop manufactureid' at line 1
mysql> alter manufacture drop manufactureid;
ERROR 1064: You have an error in your SQL syntax near 'manufacture drop manufactureid' at line 1
mysql> alter table manufacture drop primary key;
ERROR 1075: Incorrect table definition; There can only be one auto column and it must be defined as a key
mysql> alter table manufacture drop PRIMARY KEY;
ERROR 1075: Incorrect table definition; There can only be one auto column and it must be defined as a key
mysql> alter table manufacture
-> change manufacture id varchar(100) primary key not null;
ERROR 1054: Unknown column 'manufacture' in 'manufacture'
mysql> alter table manufacture
-> change manufactureid varchar(100) primary key not null;
ERROR 1064: You have an error in your SQL syntax near 'varchar(100) primary key not null' at line 2
mysql> alter table manufacture
-> change manufactureid varchar(100);

ERROR 1064: You have an error in your SQL syntax near 'varchar(100)' at line 2
mysql> alter table manufacture
-> change manufactureid varchar(50);

ERROR 1064: You have an error in your SQL syntax near 'varchar(50)' at line 2
mysql> alter table manufacture drop primary key;
ERROR 1075: Incorrect table definition; There can only be one auto column and it must be defined as a key
mysql> alter table manufacture drop primary key;
ERROR 1075: Incorrect table definition; There can only be one auto column and it must be defined as a key
mysql>

seanmayhew

that seems like exactly what i need with one problem:

alter table manufacture change manufactureid manufactureid varchar(50) not null primary key;
ERROR 1068: Multiple primary key defined

I think I need to drop the primary key first but cant with the auto_increment. Is there a way to drop the auto_increment?

kellog

Unless you want to change the primary key to another column, you won't need to drop it or modify it. That statement worked on my system, but you can try this:

alter table manufacture change manufactureid manufactureid varchar(50) not null;

The primary key should stay on manufactureid.

Originally posted by seanmayhew
that seems like exactly what i need with one problem:

alter table manufacture change manufactureid manufactureid varchar(50) not null primary key;
ERROR 1068: Multiple primary key defined

I think I need to drop the primary key first but cant with the auto_increment. Is there a way to drop the auto_increment?

seanmayhew

beautiful worked perfect