Questions tagged [alter-table]

SQL statement used to alter an existing table object.

Filter by
Sorted by
Tagged with
0
votes
2answers
24 views

In mysql, how to add a primary key and a composite key to a table so that it forbids duplicate rows, when there are null values within the columns?

In the following table (8 mi rows), every column has Null values. However, there can never be two identical rows (there will be always at least one column with a different value for each row). How can ...
0
votes
1answer
183 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 ...
8
votes
2answers
24k views

How long will it take to rename table

How long will it take to rename a table with a size of 219 GB? This is the specification of our server: Memory: 65 GB Available disk space: 266 GB MySQL Ver 14.12 Distrib 5.0.77 Sorry for the ...
0
votes
1answer
29 views

How do you change the storage parameters of a already made table (sql developer)?

Summarize the problem I have been given 6 tables that have all been created already and I was told to change the storage parameters of those tables. the issue I run into is that I can only change ...
0
votes
1answer
24 views

alter table alter column causes huge log and data file growth

I have a table with 2.6B rows in it and I need to change a column from Char(1) to Varchar(64). the table is over 300GB with a few indexes. The column in nullable, so the transaction i'm running is: ...
1
vote
3answers
86 views

How to calculate end_date from start_date and duration in SQL Server Query

I have a table with 4 fields .Start-date, Duration, End_date, Actual_date. I am providing the start_date and duration as the input to calculate end-date. While calculating the end_date the ...
1
vote
2answers
31 views

Promote MySQL Table Partition to Table

My Google Searching skills were not enough to find a solution to this. If possible, how can I promote a partition on a table to a table on its own? Let's assume I have part0, part1, and part2. Is ...
2
votes
3answers
1k 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 ...
5
votes
2answers
4k 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 ...
0
votes
1answer
34 views

Why is ALTER faster than UPDATE (for full table rewrites)?

Let's create a garbage table.. CREATE TABLE foo AS SELECT x::text FROM generate_series(1,20e6) AS t(x); Time: 14077.654 ms (00:14.078) Now, let's look at boring UPDATE, UPDATE foo SET x = 'foo'; ...
0
votes
1answer
36 views

SQL Server :: how to move country code column to another table?

I have 2 tables: tbl_Countries: 250 values, with columns like CountryName (Afghanistan), TwoCharCountryCode (AF), ThreeCharCountryCode (AFG). Like this: CountryName TwoCharCountryCode ...
0
votes
1answer
131 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
95 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
19 views

How to insert '1001-01-01 00:00:00' into MariaDB when altering a table?

I created a table in HeidiSQL: CREATE TABLE `publish_dt` ( `ID` BIGINT(20) NOT NULL AUTO_INCREMENT, `TS` DATETIME NULL DEFAULT NULL, `Text` VARCHAR(50) NOT NULL COLLATE 'utf8mb4_unicode_ci'...
0
votes
1answer
35 views

Alter column fails due to “hidden” statistic

I'm trying to change a column data type from datetime2 to datetimeoffset. I have db owner permissions. Whenever I run the query: alter table dbo.<my-table> alter column [TimeStamp] ...
2
votes
1answer
5k views

Why does adding a column to a MySQL table take incredibly long?

I am using MySQL version 5.6 and I have a database that is in total around 110 GB in size. One of the tables, "outputs" is 49 GB in size. I tried to add a simple BOOL column to the outputs table with ...
4
votes
2answers
179 views

How can I set a column to NOT NULL without locking the table during a table scan?

(former question was: Will Postgres use a multi-column index when setting multiple colums not null?) Typically, when I set a column not null, if it doesn't have an index then I add it first, so that ...
1
vote
1answer
24 views

MySQL Store Procedure - Setting AUTO_INCREMENT value

I am in trouble coding a store procedure as shown below CREATE DEFINER=`dbcomexuser`@`%` PROCEDURE `clearance_imp`(IN CAPIfilename VARCHAR(12)) BEGIN DECLARE `_rollback` BOOL DEFAULT 0; DECLARE ...
0
votes
2answers
37 views

MySQL: change column type using expression

In a MySQL database, I need to convert some UUID columns (BINARY(16)) into VARCHAR. I want to convert them in the canonical form, not as bytes. For clarity, in lowercase and with hyphens, and not in ...
0
votes
1answer
53 views

Why is ADD CONSTRAINT … NOT VALID taking a long time?

I added NOT NULL constraints to some columns like this, expecting the command to be very fast because of NOT VALID. However, it took about 60-100 seconds, and seemed to lock the table for the whole ...
0
votes
1answer
32 views

Specific format/formula when changing column datatype [duplicate]

Can one modify a column's datatype using a very specific conversion format? For example, when converting from datetime to varchar I'd like to use the yyyy-MM-dd hh:mm:ss format, not the system ...
0
votes
0answers
24 views

Altering big table with gh-ost on Google Cloud

We need to add some indexes and one additional column to a large MySQL table. MySQL is Google Cloud SQL version 5.7.25-google-log. Some table info: currently holding 1 331 363 322 rows size of the ...
2
votes
1answer
2k 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
0answers
25 views

Check constraint on grouped conditions

Take the following table as an example: GroupID AlphaID BetaID 1 0 1 1 1 1 1 2 2 2 0 1 2 1 2 ...
0
votes
1answer
38 views

How to fix conversion error from InnoDB to MyISAM?

How can I fix conversion error from InnoDB to MyISAM for these tables? MariaDB [db_name]> ALTER TABLE wp_posts ENGINE = MyISAM; ERROR 1071 (42000): Specified key was too long; max key length is ...
5
votes
5answers
45k views

How To change the column order?

How To change the column order in sql server 2008 R2? Eg: My table order is Eid------Ename-----Esalary Know i want this format like Eid---Salary----Ename Thanks.
4
votes
1answer
5k 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
20 views

Atler Table Alter Column Set Default FALSE: Why my alter table took 10 minutes to finish?

I have run the following command and it took about 10 minutes to finish: ALTER TABLE "bookings" ALTER COLUMN "local_direct_booking" SET DEFAULT FALSE for a table bookings which had 111.834 rows (...
1
vote
2answers
1k views

Converting Varchar to Int error

I came across an issue today, and while I was able to fix it, I am unsure what caused the issue to happen. I have a column that is Varchar(20). It is used to store just year data (2010, 2011, etc.) ...
2
votes
1answer
167 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 ...
12
votes
4answers
38k 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 ...
4
votes
3answers
10k views

mysql - ALTER IGNORE ADD INDEX removed in mysql 5.7.4 - What to use instead?

I am trying to add an index to a table, but only if one doesn't exist. I don't know what the index name is. In the mysql manual I found the alter ignore command: ALTER [IGNORE] TABLE tbl_name [...
0
votes
1answer
60 views

MySql Error #1054 when adding new column to table using prepared statement

I'm trying to add a new column named lot to a table, but I'm getting error #1054 - Unknown column 'lot' in 'field list'. What could be the problem? Here's the query: SET @lot = (SELECT lot_no_start ...
0
votes
0answers
23 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
28 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
26k 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 ...
1
vote
1answer
42 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 ...
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
4answers
5k 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
49 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
24 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
104 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
131 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 ...
2
votes
3answers
3k 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
1k 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
112 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
16k 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 ...
1
vote
1answer
240 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 ...

1
2 3 4 5
7