Questions tagged [alter-table]

SQL statement used to alter an existing table object.

Filter by
Sorted by
Tagged with
1
vote
2answers
3k views

SET sql_log_bin = 0 in RDS MYSQL 5.6

I have moved into RDS MYSQL 5.6 and now I have a problem. I have 5 read replicas and a/z master. I am trying to alter a table and want to change a column value and also on some tables I want to add a ...
-2
votes
3answers
187 views

Disadvantages of converting column with 2 billion data from varchar to int [closed]

I want to convert a column from varchar(50) to bigint or int. What are the disadvantages when tables has 2 billion data in SQL Server 2012? Length of data is 11 and data has only numbers. They were ...
1
vote
1answer
48 views

How to restrict column creation for existing tables?

I have a table already created in a schema which has columns a, b, and c. I want to restrict the user(s) from creating new columns. How to do this? I have gone through the manual pages about GRANT ...
3
votes
2answers
9k views

Is there a fast way to change column type without dropping the clustered index?

I have a large table that is clustered index on a bigint. We would like to change it to just an int to reduce the space and improve the performance. However, dropping the clustered index and recreate ...
1
vote
1answer
43 views

Changing Key (PK, FK) properties in DROP/Add column statements

I want to change a table by adding a column. So far nothing to see here, BUT I would like to make this column part of a composite key, i.e., I now have a layout table_name( Field_1 datatype PK, ...
5
votes
1answer
9k views

The ALTER TABLE statement conflicted with the CHECK constraint

I need to check datecom is less than datelivr create table Commande ( Numcom int identity primary key, Datecom date, Datelivr date, Codecli int foreign key references clients (Codecli) ) alter ...
3
votes
2answers
737 views

Always Encrypted - Out of Memory Exception when attempting to encrypt a column in a table

Using Always Encrypted we are attempting to encrypt one column of type varchar(50) in a table that contains 5.5 million rows with the deterministic encryption. After a while the wizard returns failure ...
1
vote
1answer
19k views

How to add/update a column with an incremented value and reset said value based on another column in SQL

I am stumped, I have searched and researched until my browser tabs no longer show the close button ("X") until you hover over them. I tried rewriting the search term over and over but I guess I do not ...
4
votes
1answer
1k views

Any reason not to add 'rowversion' column?

The TechNet article, "Optimizing Microsoft Office Access Applications Linked to SQL Server", recommends adding a rowversion column to SQL Server tables linked from MS Access. From the Supporting ...
16
votes
1answer
9k views

Why is adding a NOT NULL column with a default constraint instantaneous?

CREATE TABLE TestTab (ID INT IDENTITY(1,1), st nvarchar(100)) INSERT INTO TestTab (st) values ('a') INSERT INTO TestTab (st) values ('b') INSERT INTO TestTab (st) values ('c') INSERT INTO TestTab (st)...
0
votes
1answer
237 views

Alter table with casting

Is there a way of run an alter table command with cast on MySQL databases? I know that is possible to do it with Postgresql and Sql servers, but I didn't find anything about it when searching the ...
8
votes
3answers
35k 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 '...
2
votes
3answers
4k views

How to change VARCHAR length in PRIMARY KEY in SQL Server 2014

I need to change the VARCHAR length from 20 to 25 in a code column in a library table in SQL Server 2014, but SSMS wants to DROP and RE-CREATE all my tables where the library table in question is ...
1
vote
1answer
2k views

How can I change TIMESTAMP to TIMESTAMP(3)?

I have a column of TIMESTAMP, NOT NULL, DEFAULT CURRENT_TIMESTAMP. I want to modify it as TIMESTAMP(3). I tried ALTER TABLE <table> MODIFY COLUMN <column> TIMESTAMP(3) NOT NULL DEFAULT ...
0
votes
1answer
298 views

DB2 Alter column type and index state RBDP vs. “needs a rebuild”

I'm new to DB2 and databases overall. I'm building a script to alter several column data types in several tables of which some are part of indexes and some not. So after ALTER-statements I assumed I ...
2
votes
1answer
973 views

Date of last change of an InnoDB table structure

I was wondering how to determine when was the last change (ALTER TABLE...) done to a given table. An old answer is available but unfortunately that doesn't work for InnoDB tables. Would this work, ...
14
votes
3answers
45k 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 DataValue column. If I try to run the following command: ALTER TABLE VisitorData ADD NumericValue bit DEFAULT 0 ...
2
votes
1answer
959 views

Problem with aborted PT-online-schema change command

I aborted a pt-online-schema change command to change a table definition. Now, when I run pt-online-schema change again, I get this error: The table <db name>.<table name> has triggers. ...
2
votes
1answer
789 views

ALTER TABLE with missing FROM-clause entry for table “inventory”

ALTER TABLE inventory.subtitles ALTER COLUMN id SET DEFAULT (inventory.subtitle_subtitle_id_seq); produces missing FROM-clause entry for table "inventory" but even pgadmin 3 gives me the same ...
3
votes
1answer
1k views

Renaming a column in a materialized view with an unknown name

I have a materialized view with an unknown column name, and I need to give that column a name. I can't just recreate the view because it took a long time to create. dba=# \d temp_name_relationships; ...
1
vote
2answers
2k views

Nvarchar column getting truncated to 256 characters

I have a SQL Server Express 2012 database. There is a table which previously had a column of type nvarchar(255). Now my requirements have changed and I am required to store long strings in the same ...
12
votes
5answers
40k views

MySQL - fastest way to ALTER TABLE for InnoDB

I have an InnoDB table that I want to alter. The table has ~80M rows, and quit a few indices. I want to change the name of one of the columns and add a few more indices. What is the fastest way to ...
1
vote
3answers
2k views

How can delete an orphaned temporary table on Mysql Innodb?

I was issuing an alter table statement and suddenly the server crashed. Now I can not run any alter table command on that table, because it shows me error 1050, and says that a table named #sql1504d ...
2
votes
1answer
4k views

Does innodb_online_alter_log_max_size took memory from ram or Hard Disk

I got an Mysql (5.7) error while altering huge table (400 G) for adding an index: ERROR 1799 (HY000): Creating index 'FTS_DOC_ID_INDEX' required more than 'innodb_online_alter_log_max_size' bytes of ...
12
votes
4answers
9k views

Dropping a constraint (index) on a column

How can I modify the type on a table that has an index on it? I tried to do an alter column on an empty table to modify the type from date time to varchar(15) and got errors saying that it had ...
5
votes
2answers
8k views

Alter large table without downtime

I have a very large table, lets call it example, and I try to execute a little alter command on that table: ALTER TABLE `example` ADD `example_column` TINYINT(1) NOT NULL DEFAULT 0; Checking the ...
2
votes
2answers
5k views

Changing data type from mediumint to int without downtime

My current table is using MEDIUMINT for ID column ‘primary key auto increment’. The current table records soon will reach the maximum value for mediumint which is 8388607. That being the case, I need ...
2
votes
0answers
376 views

How to find MariaDb/Mysql structure differences

I have two similar databases with many tables, in one of these there was some alter commands maybe changing data types or adding some columns. Exists a free analyzer/merging tool to check their ...
0
votes
1answer
92 views

Is using innodb_file_per_table improve speed of alter table command?

As innodb_file_per_table option separates table files instead of putting all data and indexes of DBs into one ibdata file, is using this option improve speed of alter table? I have a table of 40M ...
0
votes
1answer
535 views

Amazon Postgres RDS: Update pg_attribute to update my custom attribute

I want to update my attribute to change its length. I am getting Error: Permission denied for relation pg_attribute. My SQL Query is like this: DO $$ BEGIN UPDATE pg_attribute SET ...
1
vote
1answer
319 views

Nothing changed; always get connection timeout on alter table in MariaDB

I have a MariaDB 10.1.21 database running in Fedora 25. I successfully ran alter table statements on auto_incrementcolumns in the past. After updating MariaDB to 10.1.21, but without changing any ...
0
votes
2answers
5k views

alter primary id field to uniqueidentifier GUID as default in sql server

I have tables with id(primary key - int) with identity specification (1,1) . I want to change this column to uniqueidentifier with default ( newid() ) . What I've tried is ALTER TABLE myTable ALTER ...
0
votes
1answer
508 views

Has the ID column configured as PK, NN and AI, be the first column on my table definition?

I have a .csv file with a bunch of data which I am using for load all the data into a DB table. The file lacks the ID column (because that's is an internal column managed through our software and they ...
0
votes
1answer
39 views

alter table which is under the load (sql server)

we have table that being used for sending sms and other notification for confirmation as second factor. Some changes in business logic requires us to alter table to make some columns NOT NULL. Our ...
0
votes
2answers
5k views

UPDATE Column values with Case and JOIN

I have 2 tables "a" and "b". Table "a" | property | element | ply | .. Table "b" | property | ply | material | .. What I want to do is to add a column to the first table called "material" and ...
3
votes
2answers
532 views

Adding a nullable column concurrently with an update or insert

In SQL Server, when adding a column that allows nulls to a live SQL Server database with millions of rows, what happens if an insert or update is occurring at the same time as the DDL update? For ...
2
votes
1answer
1k views

Oracle: changing a global temporary table to a normal table and vice versa

Our code make use of a global temporary table which the option 'ON COMMIT DELETE ROWS'. Works well and does the job as expected. But when running my automated tests, i would like the rows to persist,...
0
votes
1answer
666 views

Mysql takes more tmp space than the table size

I have a mysql table with 2.8GB data+index size. I wanted to add a column to the table and it was failing due to insufficient space on /tmp. When I looked at the size available on /tmp it was 3.9GB. ...
1
vote
1answer
78 views

ALTER TABLE online by keeping data as BLOBs

Could you please explain me how extreme de-normalization (store the value as a serialized blob will help to ALTER TABLE online? source Additionally, it's crucial to be able to change the schema: ...
1
vote
1answer
505 views

Can I alter ENUM value from lowercase to studlyCase without reindex?

I have a several big tables, and enum in each of them (example): ENUM ('webmasters', 'stackexchange', 'stackoverflow') Documenation says, that return value is in lettercase of ENUM definition, so I ...
1
vote
1answer
5k views

How to change a column type from SET to an ENUM

I have a table with a SET column type that need to be changed to ENUM type. Can I do it with a simple ALTER or will this mess up my data? NB: there are no multiple values (a,b,c) in the table.
13
votes
3answers
28k 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 ...
3
votes
3answers
4k views

Is it bad practice to add columns to an existing table?

Conversely: Is it better to get all the columns created at the time when a new table is created? I'm working on a new system and new requirements are coming up all the time. The latest requirement ...
2
votes
1answer
858 views

Oracle 12c, Will dropping partition which has 25% of total data, rebuild the indexes?

I have Oracle 12c database and partitioned table. There is a old partition which has 68734MB which is 27% of total. I need to drop this partition. This is a production database and I does not have a ...
2
votes
1answer
4k views

How does the alter command work internally when adding a column?

Can anyone explain what the internal operations are that takes place when an alter command is issued? How is column modified? Does it use created_tmp_tables or created_tmp_files? As per my test, I ...
4
votes
2answers
88 views

Which changes to a table in SSMS cause a DROP/CREATE and can this always be avoided with TSQL?

I'm aware that there are some circumstances where making certain changes to a table (for example, changing the type of a column) through the GUI of SSMS will cause the table to be dropped and re-...
0
votes
1answer
442 views

What is the syntax to add a constraint on an already-created table?

PostgreSQL exclusion constraints are documented briefly under DDL-Constraints CREATE TABLE .. CONSTRAINT .. EXCLUDE As linked in ddl-constraint But, the syntax for ALTER TABLE does not mention /...
0
votes
1answer
6k views

How to set a NOT NULL column into NULL in PostgreSQL?

I'm new to PostgreSQL and that is why I'm asking this question. I have a table app_client_users and a column named client_id. Initially I set it as NOT NULL and now I would like to change it to allow ...
25
votes
2answers
34k views

Changing a column from NOT NULL to NULL - What's going on under the hood?

We have a table with 2.3B rows in it. We'd like to change a column from NOT NULL to NULL. The column is contained in one index (not the clustered or PK index). The data type isn't changing (it's an ...
5
votes
2answers
2k views

Truncating table without using Alter Table permission

So my DBA doesn't want developers to give Alter table permission. Now one of the procedures that we created requires to truncate tables (since apparently delete only removes records, but truncate ...