none
How to change NOT NULL column to a NULL column using SQL

    Question

  • I tried those SQL command:

    Alter table PIEZOCONE Alter column PROF_DEPART SINGLE

    and

    Alter table PIEZOCONE Alter column PROF_DEPART SINGLE NULL

    via ODBC, it return no error but it doesn't modified the NOT NULL attribute

    Is someone can help me

    Friday, September 28, 2012 6:01 PM

Answers

  • Hi Lupien1

    "lupien1" schrieb im Newsbeitrag news:5feef52c-85a8-4041-9c88-36aef2afe4cf@communitybridge.codeplex.com...

    Alter table PIEZOCONE Alter column PROF_DEPART SINGLE NULL
    via ODBC, it return no error but it doesn't modified the NOT NULL attribute

    Jet doesn't change from NULL to NOT NULL even the ALTER command is accepted. It's also in Access not possible to remove the Not Null constraint of a column by an ALTER command.

    There are different ways to switch to NULL allowed.
    Method 1
    use the DAO objects to set the required property of the tabledef's field object

    Method 2
    - create a new field as null with the same datatype
    - update the new temporary field to the existing NOT NULL field
    - drop the old NOT NULL field
    - create the droped column with the same datatype again without NOT NULL
    - update the existing field to the temporary field
    - if there have been indices on the existing field, recreate these
    - drop the temporary field

    To find the indexes and unique field constraints you may run this code in Access. You may use part of it for your code in C to change the field's required property if you want to go with Method 1

    Public Function listIndexes(ForTable As String)
     Dim db As Database
     Dim tdf As TableDef
     Dim idx As Index
     Dim fld As Field
     Set db = CurrentDb
     Set tdf = db.TableDefs(ForTable)
     For Each idx In tdf.Indexes
       Debug.Print idx.Name
     Next
     For Each fld In tdf.Fields
       If fld.Required = True Then
         Debug.Print fld.Name & " is NOT NULL"
       End If
     Next
     Set db = Nothing
    End Function

    HTH
    Henry


    Tuesday, October 02, 2012 4:26 AM

All replies

  • To be honest, I don't believe it's possible using DDL (Data Definition Language).

    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)

    Friday, September 28, 2012 11:09 PM
  • I am not too sure about your question since you have "via ODBC"...

    What is the database engine that hosts your database and Table PIEZOCONE? Is it ACE/JET or Microsoft SQL Server or something else?

    What is the client from which you are trying to modify the Table?

    If the database engine is JET/ACE, i.e. Access, then you should be able to modify by code the Required Property via the Fields Collection of the TableDef Object.


    Van Dinh

    Saturday, September 29, 2012 2:42 AM
  • Hi Van and Douglas,

    The field that i'm trying  to modify is in an Access database. We develop software in C language and we use the ODBC DNS to make the connexion. We are using the ACE or the JET depending of the available driver. I'm trying the SQL syntax using the SQLExecDirect function. We don't use Fields Collection or TableDef Object.

    Using DNS connexion, the SQLExecDirect and the SQL syntax is working properly on SQL Server.

    Thank you Douglas, I think I won't have choice to create a temporary field, copy the content, droping the field, recreating it with the correct attribute, copy the data back to the field and droping the temporary field.

    If you have someting else to propose, i'm taking.

    Please excuse my english.

    Thank you

    Monday, October 01, 2012 12:03 PM
  • Yes, I think that if you're not willing to use DAO methods (or ADOX methods), then adding a new field and populating it will be the best. Note that any field you add to a populated table will automatically be created as NULL.


    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)

    Monday, October 01, 2012 9:36 PM
  • Hi Lupien1

    "lupien1" schrieb im Newsbeitrag news:5feef52c-85a8-4041-9c88-36aef2afe4cf@communitybridge.codeplex.com...

    Alter table PIEZOCONE Alter column PROF_DEPART SINGLE NULL
    via ODBC, it return no error but it doesn't modified the NOT NULL attribute

    Jet doesn't change from NULL to NOT NULL even the ALTER command is accepted. It's also in Access not possible to remove the Not Null constraint of a column by an ALTER command.

    There are different ways to switch to NULL allowed.
    Method 1
    use the DAO objects to set the required property of the tabledef's field object

    Method 2
    - create a new field as null with the same datatype
    - update the new temporary field to the existing NOT NULL field
    - drop the old NOT NULL field
    - create the droped column with the same datatype again without NOT NULL
    - update the existing field to the temporary field
    - if there have been indices on the existing field, recreate these
    - drop the temporary field

    To find the indexes and unique field constraints you may run this code in Access. You may use part of it for your code in C to change the field's required property if you want to go with Method 1

    Public Function listIndexes(ForTable As String)
     Dim db As Database
     Dim tdf As TableDef
     Dim idx As Index
     Dim fld As Field
     Set db = CurrentDb
     Set tdf = db.TableDefs(ForTable)
     For Each idx In tdf.Indexes
       Debug.Print idx.Name
     Next
     For Each fld In tdf.Fields
       If fld.Required = True Then
         Debug.Print fld.Name & " is NOT NULL"
       End If
     Next
     Set db = Nothing
    End Function

    HTH
    Henry


    Tuesday, October 02, 2012 4:26 AM