DAN212 DAN212 - 1 year ago 77
MySQL Question

SQL - 'char' auto_increment problems

I'm currently trying to use an SQL file to add a table to my database in myphpadmin and then populate it with data. I have successfully done this on many occasions but I have run into a serious issue which I can't find a solution too regardless of what I try. Previously I have used 'int' as my primary key type but I must now use 'char' as my primary key type. This is where the problem seems to have come from. I have looked online and tried several solutions all with different outcomes. All outcomes equal failure.

I have shown my original code below which failed and caused me too look for solutions online but too no avail.

-- Table structure for table `practice`

CREATE TABLE `practice` (
`practice_Id` char(2) NOT NULL auto_increment,
`Practice_Name` varchar(20) NOT NULL,
`Practice_Address` varchar(50) NOT NULL,
PRIMARY KEY (`practice_id`)

-- Dumping data for table `practice`

INSERT INTO `practice` VALUES(P1, 'Practice Head Office', '27, The Hill, Glasgow');
INSERT INTO `practice` VALUES(P2, 'Practice Unit 1', 'Unit 1, Houston Estate, Glasgow');

When I try and import this file to my database I get the following response:

#1063 - Incorrect column specifier for column 'practice_Id'

Now I found a solution to that on this very site. Simply add unsigned to the auto_increment and it should work as auto_increment works with ints and floats. I added this to the file and then tried to import it once more. Again this failed giving me even more errors which are shown below:

#1064 - 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 'unsigned auto_increment,
`Practice_Name` varchar(20) NOT NULL,
`Practice_Add' at line 6

I tried unsigned alone leaving out auto_increment but I got the same results as above.

I really need help as this seems impossible to pass. There are no syntax errors so it makes no sense. Any questions just ask but I've put my attempts above and all solutions I've found. I have other sql files with this issue so really this is a massive hurdle for me and my last work.

Answer Source

It is impossible to pass. Autoincrement only works with numeric fields. Semantically it makes no sense to "increment" a character string. Yes, you could say

'x' + 1 = 'y'

for a single character, but how do you increment a character string? How would you evaluate

"Bob" + 3

for example?

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download