I want to have my tables all contain a unique number for my tableID column.
Insert sequential number in MySQL
This is pretty much what I'm trying to accomplish but from my C# app.
EDIT: Adding the ID column with Primary Key and Auto Increment was all I needed to do. Thank you deterministicFail
From your error log:
ERROR 1067: Invalid default value for 'Status'
SQL Statement:
ALTER TABLE `lianowar_woodlandlumberbook`.`book`
CHANGE COLUMN `Customer_Ph` `Customer_Ph` VARCHAR(16) NOT NULL ,
CHANGE COLUMN `Status` `Status` VARCHAR(10) NOT NULL DEFAULT NULL ,
DROP PRIMARY KEY,
ADD PRIMARY KEY (`Customer_Name`, `Status`)
ERROR: Error when running failback script. Details follow.
ERROR 1050: Table 'book' already exists
SQL Statement:
CREATE TABLE `book` (
`Customer_Name` varchar(20) NOT NULL,
`Customer_Ph` varchar(16) DEFAULT NULL,
`Customer_Ph2` varchar(30) NOT NULL,
`Info_Taken_By` varchar(12) NOT NULL,
`Project_Type` varchar(20) NOT NULL,
`Project_Size` varchar(20) NOT NULL,
`Date_Taken` varchar(5) NOT NULL,
`Date_Needed` varchar(5) NOT NULL,
`Sales_Order` varchar(5) NOT NULL,
`Information` text NOT NULL,
`Status` varchar(10) DEFAULT NULL,
`tableID` varchar(5) DEFAULT NULL,
PRIMARY KEY (`Customer_Name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
You try to define a NOT NULL column and give it the default NULL. You should consider your datatypes, tableID should be a number datatype (btw the name isn't good, just id or bookId would be better).
To your question: You should define the table like this
CREATE TABLE `book` (
`ID` INT NOT NULL AUTO_INCREMENT,
`Customer_Name` varchar(20) NOT NULL,
`Customer_Ph` varchar(16) DEFAULT NULL,
`Customer_Ph2` varchar(30) NOT NULL,
`Info_Taken_By` varchar(12) NOT NULL,
`Project_Type` varchar(20) NOT NULL,
`Project_Size` varchar(20) NOT NULL,
`Date_Taken` varchar(5) NOT NULL,
`Date_Needed` varchar(5) NOT NULL,
`Sales_Order` varchar(5) NOT NULL,
`Information` text NOT NULL,
`Status` varchar(10) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
I dont know which datatypes you really need, because i dont know the data you going to store. But to use the Primary Key and Auto_increment feature this will do the trick.
Don't do this from application code. Ever. Application code is poorly positioned to guarantee uniqueness, because you have a potential race condition between multiple clients trying to insert at about the same time. It will also be slower, because application code must first request a current value from the database before incrementing it, resulting in two separate database transactions.
The database, on the other hand, already has features to ensure atomicity and uniqueness, and can respond to requests in order, thus positioning it to do this job much faster and better. Indeed, pretty much every database out there, including MySql, already has this feature built in.
