> Any idea what's wrong with this SQL? > > ALTER TABLE test > ADD COLUMN testNum BYTE NOT NULL DEFAULT 5 > > This is using Access 2000. It seems to be the DEFAULT > that's causing problems (works OK with just NOT NULL, > or without constraints altogether). > > Even statements copy-pasted from the MS Jet SQL > reference docs seem to not work with DEFAULT. Any > common pitfalls with this clause?
I'm surprised it lets you add the column with "NOT NULL", actually. If you have an existing table, with data in it, you usually can't add a column which can't have NULLs, since initially, it will have to have NULLs. My guess is that there's something similar going on with default values in Access. Typically, rather than changing the target table, you have to do a little dancing: 1. Create a new "temporary" table, identical to the old one in structure, with a different name. Note that it's typically not a true temp table, just one that you're only going to use for a while. 2. Copy data from the old table to the new one. 3. Drop constraints on the old table. 4. Drop the old table. 5. Recreate the old table with the new structure. 6. Recreate the constraints. 7. Copy the data from the "temporary" table to the recreated table. 8. Drop the "temporary" table. As you can guess, this can be quite a bit of work, which is where SQL schema management tools like ERwin and PowerDesigner come into their own - they automate the whole process. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 ______________________________________________________________________ Why Share? Dedicated Win 2000 Server � PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation � $99/Month � Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
