SQL Function used to alter an existing table object.
0
votes
1answer
42 views
H2 SELECT NULLABLE or other equivalent to check a column has NOT NULL constraint or not
I would like to check if a column has a NOT NULL constraint in order to start an automated upgrade of the table definition.
However in H2 the SQL SELECT NULLABLE FROM TABLE_NAME doesn't return me the ...
2
votes
1answer
121 views
Can a view affect ALTER TABLE commands?
I provide a database system to a number of customers. The database is installed locally on Oracle.
We periodically upgrade the database structure (either adding new fields, renaming old fields, ...
1
vote
3answers
853 views
Avoiding “Waiting for table metadata lock” when `ALTER TABLE DROP PARTITION`?
I have some tables that many users need to access to:
mysql> show create table v3_cam_date\G
*************************** 1. row ***************************
Table: v3_cam_date
Create Table: ...
1
vote
3answers
92 views
Altering same table across multiple databases
I have multiple databases on the same instance and I am in the process of updating a table schema that must propagate across all the databases.
I am not sure I know what the right procedure for this ...
1
vote
2answers
95 views
Modify all tables in a database with a single command
Is there a single or a one line command to modify all tables within a database. I would like to issue this command in every table within a database:
ALTER TABLE `table_name` CONVERT TO CHARACTER SET ...
0
votes
0answers
30 views
MySQL: optimal configs / methods for ALTER of large MyISAM table to InnoDB (17gb+) [duplicate]
I have a large MySQL (5.1) MyISAM table (13gb table, 4GB indexes - 40mm+ records)
I'm going to be converting this table to InnoDB in a production environment.
The DB is on a cloud server with ...
0
votes
1answer
79 views
Modify MySQL foreign key related column without dropping foreign key relation
I need to modify a foreign key related column. When I tried to alter it I got "Error on rename of 'x' to 'y' (errno: 150)" error. So I googled and found foreign key relation is the 'villain'. I ...
2
votes
1answer
782 views
After truncating a single partition its Primary key's index becomes unusable and all inserts/updates into that partition fail
I have a partitioned table: SAMPLE_PARTITIONED_TBL with 60 partitions (no sub-paritions) based on the PERIOD_ID numeric field (Data set: 201001...201212.. and so on). This table has several local ...
2
votes
2answers
202 views
SQL set allowed values for a column
I want to make an ALTER TABLE expression which adds a new column and sets a default value and additionaly defines the allowed values for that column. It's a text column, and allowed should be only ...
3
votes
2answers
495 views
How to make a trigger that will compare input with a value of other table?
I had made two tables with the following fields:
TABLE 1: "Bookings"
FIELD: "fk_flight_number"
FIELD: "date_of_reservation"
TABLE 2: "Flights"
FIELD: "flight_number"
FIELD: ...
3
votes
1answer
2k views
What happens when you modify (reduce) a column's length?
Lets say I have two columns of type NUMBER (without precision, and scale) and VARCHAR(300). I saw that these columns are way too large for my data, so I want to modify them to NUMBER(11) and ...
3
votes
5answers
1k views
Alter table on live production databases
How do most "popular" (MySQL, Postgres...) database system handle altering tables on live production databases (like adding, deleting or changing the type of colums)?
I know the correct way is to ...
3
votes
2answers
175 views
Alternate ways to increase column size
I have a database that is in production, and in some instances will have millions of records. It has been requested that we increase the sizes of several columns, which is for the most part pretty ...
2
votes
2answers
964 views
SQL Server : how to drop all constraints on a table in T-SQL
I´d like to drop all constraints on all tables in my database, because I need to change the relationships of many of the tables.
Is there any way to do that with T-SQL? I cannot find any solution ...
6
votes
1answer
438 views
How do I swap tables in MySQL?
Suppose, I have a table foo, which contains some statistics that are computed every now and then. It is heavily used by other queries.
That's why I want to compute more recent statistics in foo_new ...
3
votes
1answer
447 views
Why does altering a table and adding a foreign key create an extra constaint?
Edit: It was a default constraint as a result of setting a default value when adding the column. The name gave it away when starting with 'DF__'. Anyway, solved it by adding the column and manually ...
0
votes
1answer
426 views
Why won't Oracle alter the size of a column that is used for sub partitioning?
I am trying to resize a varchar2 column, when I get an ORA-14265 error:
ORA-14265: data type or length of a table subpartitioning column may not be changed
With Oracle providing the unhelpful:
...
3
votes
3answers
2k views
Add a new Column and define its position in a table
I have Table A with 5 columns:
TableA
--
Name
Tel
Email
Address
I want to add a new column (mobile) in between Tel & Email:
TableA
--
Name
Tel
Mobile
Email
Address
If I use
ALTER TABLE ...
2
votes
1answer
190 views
Default values in SQLite3
When I created the table structure of my SQLite database, I provided a DEFAULT value which is used when no value is provided by the user. Now, because the application code changed, I need to modify ...
1
vote
1answer
68 views
how to use mysql temporal cache tables
I took this example from a book where visitors_today is the cache table from visitor_stored. The idea is to store all the visitors per day (visitors_stored) using a cache table.
Create table ...
5
votes
5answers
710 views
Altering table schema takes too much time
I am using PostgreSQL database.
I have a table with millions of rows. I have a varchar column with 2000 size and i want to make it to 4000. I ran the alter table command, but it is taking too much ...
2
votes
1answer
816 views
I need to run VACUUM FULL with no available disk space
I have one table that is taking up close to 90% of hd space on our server. I have decided to drop a few columns to free up space. But I need to return the space to the OS. The problem, though, is ...
1
vote
1answer
335 views
Changing collation type database-wide in MySQL
How do I retroactively update the collation type database wide without dropping and recreating it?
I accidentally created a database without specifying UTF8 as the default collation type. I then went ...
2
votes
1answer
373 views
Rotate a table in PostgreSQL
Problem description
I need to rotate (with DROP and CREATE) a table which is heavily used by other clients.
At present, I have a program which replaces (DROP + CREATE) this table.
Sometimes, just ...
2
votes
2answers
495 views
Will an ALTER TABLE ADD COLUMN on a slave break replication?
I need to add a last_modified column to a MyISAM table tbl_items. The crux of this problem is that tbl_items houses several gigabytes of data. Also of note, I am using a master-slave deployment with ...
2
votes
1answer
180 views
Add columns Query With parameters
Hello all I'm having 1 Question with following query syntax
ALTER TABLE table_name
ADD column_name column-definition;
Question is can we use parameters to column_name field like @column_name? ...
2
votes
1answer
344 views
Preventing replication of alter table command to change engine
I am in the process of switching a MySQL table from MyISAM to InnoDB and I do not want the slave to perform the alter table command until I execute it manually. The intention is to have a backup for a ...
1
vote
2answers
141 views
How to recover previous values in Mysql Table?
I have MySQL Innodb table of 14M rows. There was a coloumn called is_disabled which had different values. By mistake I ran the query without the where clause:
update url_queue set is_disabled=1
...
3
votes
2answers
4k views
Why does simple ALTER TABLE command take so long on table with full-text index?
I have a large (~67 million rows) name-value table that has full-text indexing on the value column (DataValue).
My question is:
If I try to run the following sql command:
ALTER TABLE VisitorData ...
0
votes
1answer
130 views
Columns are added in wrong order
I have a encountered strange behaviour adding columns to an existing table. I run those scripts using osql several instances.
Let Table a_table have initially three columns A_col, B_col and C_col.
...
