SQL Server default constraints with fuctions ( newID())

General Discussion
1

Hi,

This is a a nag that has a workaround, but basically I cannot set the defaultValue attribute in the column element to a function. I can work around it by:

    
        
            alter table AssignLatch add constraint DF_AssignLatch_GUID
            DEFAULT newsequentialid() for GUID
        
        
            
                alter table AssignLatch drop constraint DF_AssignLatch_GUID
            
        
   

but it’d be nice to simply say:

    
        
            
                
            
        
   

BTW I tried it with parenthesis too: defaultValue="(newsequentialID())"

Ciao
Stefano

2

What if you use it with defaultValueNumeric=“newsequentialID()” ?

Nathan

3
Originally posted by: Nathan
What if you use it with defaultValueNumeric="newsequentialID()" ?

Nathan

Should I try even if newsequentialid() returns a uniqueidentifier (GUID) which maps to the String java type?

Ciao
Stefano

4

I think it will still work.  What the defaultValueNumeric will do is try to parse “newsequentialID()” as a number, and if it can’t it will just figure it is a computed value and return it as is without quotes around it (since it’s a number).

Logically, we should probably have a defaultValueComputed, but we do not currently.

Nathan

5

Hi Nathan,

numericDefaultValue did not work:

    
        
            
                
            
            
                
            
            
        
   

results into this exception

$ lqb --changeLogFile=pocone.xml updateSQL
java.lang.reflect.InvocationTargetException
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at liquibase.util.ObjectUtil.setProperty(ObjectUtil.java:29)
        at liquibase.parser.xml.XMLChangeLogHandler.setProperty(XMLChangeLogHandler.java:350)
        at liquibase.parser.xml.XMLChangeLogHandler.startElement(XMLChangeLogHandler.java:240)
        at com.sun.org.apache.xerces.internal.parsers.AbstractSAXParser.startElement(AbstractSAXParser.java:501)
        at com.sun.org.apache.xerces.internal.impl.xs.XMLSchemaValidator.startElement(XMLSchemaValidator.java:688)
        at com.sun.org.apache.xerces.internal.impl.XMLNSDocumentScannerImpl.scanStartElement(XMLNSDocumentScannerImpl.java:400)
        at com.sun.org.apache.xerces.internal.impl.XMLDocumentFragmentScannerImpl$FragmentContentDriver.next(XMLDocumentFragmentScannerImpl.java:2747)
        at com.sun.org.apache.xerces.internal.impl.XMLDocumentScannerImpl.next(XMLDocumentScannerImpl.java:648)
        at com.sun.org.apache.xerces.internal.impl.XMLNSDocumentScannerImpl.next(XMLNSDocumentScannerImpl.java:140)
        at com.sun.org.apache.xerces.internal.impl.XMLDocumentFragmentScannerImpl.scanDocument(XMLDocumentFragmentScannerImpl.java:510)
        at com.sun.org.apache.xerces.internal.parsers.XML11Configuration.parse(XML11Configuration.java:807)
        at com.sun.org.apache.xerces.internal.parsers.XML11Configuration.parse(XML11Configuration.java:737)
        at com.sun.org.apache.xerces.internal.parsers.XMLParser.parse(XMLParser.java:107)
        at com.sun.org.apache.xerces.internal.parsers.AbstractSAXParser.parse(AbstractSAXParser.java:1205)
        at com.sun.org.apache.xerces.internal.jaxp.SAXParserImpl$JAXPSAXParser.parse(SAXParserImpl.java:522)
        at liquibase.parser.xml.XMLChangeLogParser.parse(XMLChangeLogParser.java:70)
        at liquibase.parser.ChangeLogParser.parse(ChangeLogParser.java:28)
        at liquibase.Liquibase.update(Liquibase.java:105)
        at liquibase.Liquibase.update(Liquibase.java:136)
        at liquibase.commandline.Main.doMigration(Main.java:660)
        at liquibase.commandline.Main.main(Main.java:95)
Caused by: java.text.ParseException: Unparseable number: "newsequentialid("
        at java.text.NumberFormat.parse(NumberFormat.java:333)
        at liquibase.change.ColumnConfig.setDefaultValueNumeric(ColumnConfig.java:204)
        ... 25 more
Migration Failed: Unknown Reason

Ciao
Stefano

6

I created a couple issues for this:
http://liquibase.jira.com/browse/CORE-381
and
http://liquibase.jira.com/browse/CORE-382

You’ll have to stick with the work around for now.

Nathan

7

Nathan,

thank you for your wonderful and most appreciated work on liquibase!

Ciao
Stefano