RE: Migration

[John Daisley]

Hi Lenz,

You cannot use UUID() or indeed any other function (with the exception
of current_timestamp) as the default value for a column

As per the manual at
http://dev.mysql.com/doc/refman/5.1/en/create-table.html

        The DEFAULT clause specifies a default value for a column. With
        one exception, the default value must be a constant; it cannot
        be a function or an expression. This means, for example, that
        you cannot set the default for a date column to be the value of
        a function such as NOW() or CURRENT_DATE. The exception is that
        you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP
        column. 

The way around this is to have a trigger set the column value to UUID()

Regards

John Daisley

--------------------------------------

John Daisley

Certified MySQL 5 Database Administrator
Certified MySQL 5 Developer
Cognos BI Developer
MS SQL Server 2005 Database Administrator

Telephone: [phone]
Email: john.daisley@stripped

On Fri, 2010-08-13 at 16:35 +0530, Kranthi wrote:
> Hi Lenz,
>         I used UUID() function, I am getting error "Check the manual that
> corresponds to your mysql server version for the right syntax to use near
> UUID()". I am using mysql version 5.0.45.
> 
> 
> DROP TABLE IF EXISTS `AdventureWorks_HumanResources`.`Employee`;
> CREATE TABLE `AdventureWorks_HumanResources`.`Employee` (
>   `EmployeeID` INT(10) NOT NULL AUTO_INCREMENT,
>   `NationalIDNumber` VARCHAR(15) CHARACTER SET utf8 COLLATE utf8_general_ci
> NOT NULL,
>   `ContactID` INT(10) NOT NULL,
>   `LoginID` VARCHAR(256) CHARACTER SET utf8 COLLATE utf8_general_ci NOT
> NULL,
>   `ManagerID` INT(10) NULL,
>   `Title` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
>   `BirthDate` DATETIME NOT NULL,
>   `MaritalStatus` CHAR(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT
> NULL,
>   `Gender` CHAR(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
>   `HireDate` DATETIME NOT NULL,
>   `SalariedFlag` TINYINT NOT NULL DEFAULT 1,
>   `VacationHours` SMALLINT(5) NOT NULL DEFAULT 0,
>   `SickLeaveHours` SMALLINT(5) NOT NULL DEFAULT 0,
>   `CurrentFlag` TINYINT NOT NULL DEFAULT 0,
>   `rowguid` VARCHAR(64) NOT NULL DEFAULT uuid(), -- Here I am using UUID()
> instead of newid()
>   PRIMARY KEY (`EmployeeID`)
> )
> ENGINE = INNODB;
> 
> 
> 
> Thanks & Regards, 
>  Kranthi 
> 
> 
> 
> -----Original Message-----
> From: Lenz Grimmer [mailto:lenz@stripped] 
> Sent: Friday, August 13, 2010 3:56 PM
> To: mysql@stripped
> Subject: Re: Reg: Migration
> 
> Hi,
> 
> On 08/13/10 09:34, Kranthi wrote:
> 
> >          I am migrating the database from mssql server to Mysql.i am
> getting
> > problem with newid(),I don't know about newid(),i am using mysql migration
> > tool kit.
> > 
> > if anybody knows please help me. 
> 
> Would using the UUID() MySQL function work for you in this case?
> 
> http://dev.mysql.com/doc/refman/5.1/en/miscellaneous-functions.html#function
> _uuid
> 
> Bye,
> 
>     Lenz
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=1
> 
> 
> 
> -- 
> 
>