Questions tagged [alter-table]

SQL statement used to alter an existing table object.

Filter by
Sorted by
Tagged with
0
votes
1answer
87 views

H2 Database - Alter multiple databases

I need to alter a table in H2 db. But we have millions of databases with the same same table. What may be the feasible way to alter such databases?
0
votes
1answer
101 views

Alter table - existing column as primary key

I am attempting to set one of the columns in the table as primary key. Here are the columns from the table: Now, when I run the sql: ALTER TABLE cb_company ADD PRIMARY KEY(uuid); I get the ...
0
votes
0answers
21 views

Adding a column and new foreign key constraint in one line

Let's say we have a table called Salesman with the folling properties Create Table SalesMan( EmplyeeId int Primary Key, Name varchar(20) Not Null, LastName Varchar(20) Not Null, Email Varchar(...
0
votes
0answers
19 views

Alter Table operations blocks replication in MySQL

We are using Amazon Web Service's MySQL 5.7.26 with 4 read-only replicas. After some Alter Table operations we have found that the replication stops working. The symtoms are great replica lag that is ...
29
votes
4answers
25k views

Adding columns to production tables

What's the best way to add columns to large production tables on SQL Server 2008 R2? According to Microsoft's books online: The changes specified in ALTER TABLE are implemented immediately. If the ...
11
votes
3answers
30k views

How to add a column with a foreign key constraint to a table that already exists?

I have the following tables, CREATE TABLE users (id int PRIMARY KEY); -- already exists with data CREATE TABLE message (); How do I alter messages table such that, a new column called sender is ...
1
vote
1answer
38 views

Does “alter table … set data type” functionally differ from “alter table … type”?

The docs show [SET DATA] TYPE which implies the SET DATA part is optional, but the 9.1 release notes also imply that SET DATA TYPE may be different: Allow ALTER TABLE ... SET DATA TYPE to avoid ...
2
votes
1answer
104 views

ALTER fixed-length COLUMN on compressed table

I wonder if compression of a table actually changes ALTER TABLE rules. I found no information on this topic. Let's consider a simple example. I'll create a table and alter one of the columns from INT ...
0
votes
0answers
21 views

ALTER TABLE ADD [duplicate]

I have a development SQL Server (SQL 2017) and a production one (SQL 2019). When I run the following statement on dev it is pretty much instantaneous ALTER TABLE dbo.BatchStaging ADD ...
1
vote
3answers
801 views

Prevent replication of ALTER commands

I am using MariaDB 10.0 multi-source replication for a specific use case. For security reasons, I would like to prevent ALTER commands on master to replicate (such as CREATE, ALTER, DROP...) whatever ...
1
vote
4answers
3k views

Find constraint disabled in SQL Server

I'm doing an audit of constraints in SQL Server. How to find all constraints that are not enabled? This, after using the next declaration: alter table mytable nocheck constraint all
0
votes
1answer
125 views

TokuDB Hot Column Expansion

I need to expand a varchar field length from 255 to 4000. I am using tokudb_version: tokudb-7.5.8 running on Linux 3.16.0-60-generic #80~14.04.1-Ubuntu SMP Wed Jan 20 13:37:48 UTC 2016 x86_64 x86_64 ...
0
votes
1answer
34 views

Alter on huge table consumes all disk space

I'm running MySQL 8 on Ubuntu 19. I have a table that is 543G and I have to add a new column and index. I've started with the new column: alter table hugeTable add column newCol tinyint(1) after ...
0
votes
0answers
20 views

ALTER TABLE query to convert table to utf8mb4_bin takes many hours - how to optimise this?

I have a table which has approx 15 million rows - I won't add the schema here but it has 96 columns (far too many in my opinion). The current table collation is latin1_swedish_ci but for full emoji ...
4
votes
2answers
4k views

Column cannot be converted from type 'int' to type 'bigint(20) unsigned'?

I have a Percona MySQL cluster, with a master and a few slaves. Each of them is running "Ver 14.14 Distrib 5.5.40-36.1" of MySQL. Replication is row based. I ran an alter query on one of the tables ...
2
votes
1answer
61 views

Change column datatype without rewrite when data remains unchanged

For example: timestamp(tz) to int8 date to int4 json to text The raw data is unchanged, and the only difference is how that data is handled. So it should be possible to change back and forth cleanly ...
4
votes
1answer
47 views

Prevent drop column with index on it in PostgreSQL

In PostgreSQL when you drop column in a table, all indexes and constraints which use this column will be also dropped silently. If you overlook such situation, you can get serious problems in ...
1
vote
3answers
1k views

How do I move all tables from one Postgres schema to another

I want to move all tables from schema xyz to the default schema public. I can move individual tables with ALTER TABLE table_name SET SCHEMA public; And I can get all tables with SELECT ...
0
votes
2answers
793 views

Alter All Data Types To Different Data Type

I have a database that has multiple tables with datetime2 fields. I need to convert all of these to datetime fields. I ran this syntax that produced over 300 tables that need to be altered with ...
0
votes
0answers
26 views

Dynamic script to alter a column type having an index

I am writing a script to update the datatype of a column in SQL Server on a list of tables, if the existing column are not yet of the desired type. The column should be converted from a VARCHAR to a ...
2
votes
4answers
15k views

Unable to find how a table is being populated

Using SSMS2014 w/ SQL Server 2008 R2. I have a table that I am trying to find out how and from where data inside it is coming from. (I didn't create the table and am unable to contact the person who ...
0
votes
0answers
32 views

How to rename parent table and child tables in partition tables postgres 9.6

I am trying to rename parent table in partitions. I created 45 child tables on monthly wise manner. I can alter the name of parent table but I don't know how to alter the 45 child table references. ...
1
vote
1answer
66 views

Drop multiple columns at the same time from a SQL Server table

I am currently looking some way to delete multiple columns at the same time from a given table in sql server. One way of doing that is to use ALTER statement as shown below: Alter Table [USER_DB].[...
1
vote
1answer
44 views

SQL Server - Migration script testing - Refresh table definition manually without dropping it

How can a table definition be manually refreshed without also manually crafting a bunch of ALTER TABLE statements, or dropping and recreating the table? This is normally handled by the publish ...
12
votes
4answers
379 views

Fast alter column NVARCHAR(4000) to NVARCHAR(260)

I have a performance problem with very large memory grants handling this table with a couple of NVARCHAR(4000) columns. Thing is these columns are never larger than NVARCHAR(260). Using ALTER TABLE [...
-1
votes
1answer
17 views

what happen you using put the ALTER statement in a transaction.?

I got a question about "What happens when you using Alter table command when a database is running? is it possible to run the alter table when database is running? and if it's possible what will ...
0
votes
1answer
246 views

Error Code: 1845. ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE

I upgraded to MySQL 8.0.13 and tried to add a new field using ALGORITHM=INSTANT but got the following error: Error Code: 1845. ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=...
1
vote
1answer
40 views

Alter table add column with no default blocks

tl;dr The following query blocks the postgres server when executed ALTER TABLE "image_data" ADD COLUMN "user_status" varchar(30) NULL engine version 10.6 # of rows 1.5 mils Hi, I have a somewhat ...
2
votes
0answers
29 views

What is the order of execution of alter operations in an alter query in MySQL?

Consider the following queries CREATE TABLE MyTable (f2 INT); ALTER TABLE MyTable ADD COLUMN f1 INT, DROP COLUMN f1; In the above query drop column seems to happen before add column and query fails....
2
votes
1answer
1k views

Apparantly errorneous Exceeds 8K row length message

In attempting to alter an SQL Server table adding DATE and FLOAT columns. I get a message: Warning: The table "CustomerTransactions" has been created, but its maximum row size exceeds the allowed ...
0
votes
1answer
20 views

Altering Tables using Tablenames from other Table

I have a database with several tables and i have a table which contains all tablenames which I want to alter. I want to perform a query like this: ALTER TABLE (SELECT "TABLE_NAME" FROM "...
0
votes
1answer
550 views

Entity Framework 6: How can I add new column to existing table and at the same time update view (add column) in same migration file [closed]

Example in the EF6 migration file Up method. I wish to add a new column B in existing table Country. Sql("ALTER TABLE [dbo].[Country] ADD [B] INT DEFAULT((0)) NOT NULL;") Then I will need to ...
1
vote
2answers
202 views

Db2 REORGCHK returns the SQL2310N (Error “-668”) message

I have a Db2 database on my Linux laptop. The database is not heavily loaded. I access it from time to time for the development purposes. I removed a column: db2 "alter table SCHEMA.TABLE drop ...
2
votes
1answer
1k views

MySQL 5.6 Online DDL lead to to much table-metadata-locks when I just add index

It's normal that table-metadata-lock shows up for a short time when ALTER TABLE t add index ... starts and ends. But I have encountered all DML operations blocked in Waiting for table metadata lock, ...
0
votes
0answers
46 views

Possibility of using self-referential foreign key to create hierarchical comments

I am an accidental DBA and have a question regarding the table structure of a website I maintain (postgresql 9.6 backend). Imagine a typical content website (let's call it 'Links'), where users post ...
11
votes
1answer
1k views

Is ALTER TABLE … DROP COLUMN really a metadata only operation?

I've found several sources that state ALTER TABLE ... DROP COLUMN is a meta-data only operation. Source How can this be? Does the data during a DROP COLUMN not need to be purged from the underlying ...
3
votes
1answer
1k views

SQLServer add a default value for a column [closed]

I am using MS SQL Server. I have an "action" table with an "action_type" column that I want to set a default value of "to do" for. My DDL in MS SQL Studio errors! I have read many many articles trying ...
18
votes
4answers
4k views

Memory Optimized Tables - can they really be so difficult to maintain?

I'm investigating the benefits of upgrading from MS SQL 2012 to 2014. One of the big selling points of SQL 2014 is the memory optimized tables, which apparently make queries super-fast. I've found ...
12
votes
3answers
607 views

Increase speed altering column on large table to NON NULL

I recently added a NULL-able bit column to a table which has close to 500 million rows. There's not a default on the column, however all inserts are specifying a value of 0 or 1, and I ran a one-time ...
1
vote
1answer
20 views

Do the action blocks of an ALTER TABLE statement run atomically?

In Postgres, ALTER TABLE can accept multiple action forms, separated by a comma. For example: ALTER TABLE mytable DROP CONSTRAINT pkey, ADD CONSTRAINT pkey PRIMARY KEY USING INDEX myindex; ...
1
vote
1answer
29 views

MySQL replication: how to safely propagate ignored table structure changes from master to slave?

We have a MySQL database with a replication set up (master to single slave). Some tables are skipped from replication (they are listed in Replicate_Wild_Ignore_Table when calling SHOW SLAVE STATUS). ...
3
votes
1answer
1k views

How to set column DEFAULT using value of a variable

In a script for setting up a database which uses random UUIDs for primary keys, I have: CREATE TABLE alpha (id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), value INT); CREATE TABLE beta (aref UUID ...
0
votes
1answer
25 views

How to change all columns from one type to another, changing all FK columns as well?

In an inherited DB we found that there are around 25 tables with a primary key defined as tinyint. For a few of those, it's an imminent problem, since this optimistic optimization clashes with the ...
2
votes
2answers
2k views

Huge disk space usage increase when altering a column

I hope this question is easy for someone to answer :) I have a table that looks like this (unimportant columns hidden) CREATE TABLE [dbo].[Log]( [LogID] [int] IDENTITY(1,1) NOT NULL, --...
3
votes
1answer
4k views

Why dropping all foreign keys from a script is not working? [closed]

I have sysadmin rights on a SQL Server 2008 R2 box. I found a script that can drop all foreign keys and re-add them for us when we need to after a testing import. So I execute the below code with no ...
0
votes
1answer
37 views

How to add a constraint to a table using a partial index?

I have a simple table: create table item ( lan text not null, disabled date ); My aim is to ensure uniqueness for the lan column over all rows, where disabled is null. I created a partial ...
0
votes
1answer
19 views

During table migration of tables from one tablespace to another, does it needs free space to do the alter table move?

During migration of tables from one tablespace to another (all datafiles in the same disk/partition/mount), does it needs free space to do the alter table move? exemple:. I have 30gb of free space on ...
0
votes
2answers
50 views

Moving tables to new tablespace needs temp space or any kind of addictional space? (Oracle 11g)

I've been trying to search for this info on oracle documents but they just don't talk about it. I need to move about 2TB of different tables from 3 different tablespaces to a new tablespace (...
4
votes
3answers
5k 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 ...
1
vote
2answers
5k views

How can one drop all default values and allow NULL for all columns of a table?

I'm working on a PHP script to modify the primary key (PK) of a table. My current code is: $q = "SHOW KEYS FROM $db.$table WHERE Key_name='PRIMARY';"; $keys = Query ( $q); if ( $keys) { $q = "...

1 2 3 4 5 6