This should work because adding the new column is a schema change only so it will be almost instant and take no extra space.Here were my thoughts on the first suggestion, adding a new column. Create a new table, populate it, index it, drop the old table, re-name the new table.Add a new column to the end of the table, populate it in batches, then remove the old column.While this was going on I checked on #sqlhelp to see if anyone knew any way to minimize the log useage so my command would finish. I then tried a 200 GB log and a 350 GB log. After about an hour I got back a log full error. My first attempt was just a straight ALTER TABLE ALTER COLUMN. I can get more disk space by requesting a new disk but I want to avoid the paperwork and complications of moving some of the files to the new location.This is a test environment so I can play a bit, but I’m planning for the production change.I only have to worry about the log size of the largest single transaction. The database is in SIMPLE recovery mode so no need to take log backups during the change to manage log size.The log when I started was 50 GB with the max size also set to 50 GB.The main drive the database is on is already 2 TB in size (I’m told this is the limit for our drives but I don’t know if that’s a political or technical limit).The database has multiple filegroups each with multiple files.You might not think that would be a big deal but it ended up being a big pain. I was asked to alter a column from CHAR(2) to CHAR(4). Feel free to give me your opinion in the comments below. ![]() I will not be giving any specific examples or code. I had a task, I went through several possibilities and I’m going to share each of them and the results of those I tried. Fair warning, this is a discussion piece. You can also chain withKeyName() to override default key name that is generated from table and column names (result is identical to specifying second parameter to function foreign()).ĭeferrable foreign constraint is supported on Postgres and Oracle and can be set by chaining. You can also chain onDelete() and/or onUpdate() to set the reference option (RESTRICT, CASCADE, SET NULL, NO ACTION) for the operation. Table.foreign(columns, )Īdds a foreign key constraint to a table for an existing column using table.foreign(column).references(column) or multiple columns using table.foreign(columns).references(columns).inTable(table).Ī default key name using the columns is used unless foreignKeyName is specified. If you want to chain unique() while creating new column you can use unique foreign # In PostgreSQL, SQLite and MSSQL a partial unique index can be specified by setting a 'where' predicate. In MSSQL and Postgres, you can set the useConstraint option to true to create a unique constraint instead of a unique index (defaults to false for MSSQL, true for Postgres without predicate, false for Postgres with predicate). Deferrable unique constraint are supported on Postgres and Oracle and can be set by passing deferrable option to options object. If you need to create a composite index, pass an array of column to columns. A default index name using the columns is used unless indexName is specified. In MySQL, the storage engine index type may be 'btree' or 'hash' index types, more info in Index Options section. Table.increments(name, options=)Īdds an unique index to a table over the given columns. ![]() Renames a column from one name to another. dropSchemaIfExists ( 'public', true ) Schema Building # dropColumn #ĭrops a column, specified by the column's name dropColumns #ĭrops multiple columns, taking a variable number of column names. dropSchemaIfExists ( 'public' ) //drop schema if exists 'public' cascade
0 Comments
Leave a Reply. |