Stack Overflow
Stack Overflow

Questions tagged [alter-table]

The "ALTER TABLE" is a SQL statement that allows you to make schema changes to a database table (i.e. add, drop or alter columns from an existing table).

1118 questions
35
votes
4 answers

How to add a boolean datatype column to an existing table in sql?

I have a table called person in my database. I want to add another column to the same table and it's a Boolean datatype column. I have tried following queries but it says syntax error near default. I know this is a common and there are lot of…
Mike
  • 1,017
  • 4
  • 19
  • 34
34
votes
3 answers

Mysql set default value to a json type column

I heard that mysql version prior to 8.0.13 accept default value for json type column, so I using the cmd: ALTER TABLE templates CHANGE COLUMN values JSON NOT NULL DEFAULT '{}' ; but receive error: Error Code: 1101. BLOB, TEXT, GEOMETRY or JSON…
Dave kam
  • 373
  • 1
  • 3
  • 5
31
votes
3 answers

Can you index tables differently on Master and Slave (MySQL)

Is it possible to set up different indexing on a read only slave, from on the master? Basically, this seems like it makes sense given the different requirements of the two systems, but I want to make sure it will work and not cause any problems.
Scott Miller
  • 2,298
  • 3
  • 21
  • 24
31
votes
3 answers

Best way to add column with default value while under load

When adding a column to a table that has a default value and a constraint of not null. Is it better to run as a single statement or to break it into steps while the database is under load. ALTER TABLE user ADD country VARCHAR2(4) DEFAULT 'GB' NOT…
Ben George
  • 975
  • 3
  • 12
  • 23
31
votes
5 answers

Best way to add a new column with an initial (but not default) value?

I need to add a new column to a MS SQL 2005 database with an initial value. However, I do NOT want to automatically create a default constraint on this column. At the point in time that I add the column the default/initial value is correct, but…
Adrian
  • 1,198
  • 1
  • 12
  • 18
30
votes
6 answers

Optimizing MySQL for ALTER TABLE of InnoDB

Sometime soon we will need to make schema changes to our production database. We need to minimize downtime for this effort, however, the ALTER TABLE statements are going to run for quite a while. Our largest tables have 150 million records, largest…
schuilr
  • 674
  • 1
  • 7
  • 11
30
votes
4 answers

Alter table if exists or create if doesn't

I need to run an installer which can also be an updater. The installer needs to be able to end up having a certain scheme/structure of the mysql database, regardless if some of the tables existed, missed a few columns, or need not to be changed…
Alon_T
  • 1,430
  • 4
  • 26
  • 47
30
votes
3 answers

How do you ADD and DROP columns in a single ALTER TABLE

I tried the following but I got a syntax error ALTER TABLE Grades ( DROP COLUMN (Student_FamilyName, Student_Name), ADD Student_id INT ); Is it possible to perform a DROP and an ADD in the same ALTER TABLE statement?
squeezy
  • 607
  • 1
  • 7
  • 15
29
votes
2 answers

What does the Alter Table syntax look like for adding a DATETIME column?

I can't find what the syntax looks like for adding a DATETIME column to a mysql table when I want to set the default to - example - 2011-01-26 14:30:00 Does anyone know what that syntax looks like? Here's what I've got ADD COLUMN new_date DATETIME…
H. Ferrence
  • 7,906
  • 31
  • 98
  • 161
26
votes
7 answers

MySQL - Duplicate entry error when trying to add new column

I have a MySQL database with a table that has 2 million rows using innodb engine. I want to add another column, but I keep getting the following error: Error 1062: Duplicate entry '' for key 'PRIMARY' SQL Statement: ALTER TABLE `mydb`.`table` ADD…
Hank
  • 3,367
  • 10
  • 48
  • 86
26
votes
3 answers

PostgreSQL - change precision of numeric?

I tried to change precision like this: ALTER Table account_invoice ALTER amount_total SET NUMERIC(5); But I get syntax error, so I'm clearly doing something wrong. What is the right syntax to change precision of numeric in PostgreSQL?
Andrius
  • 19,658
  • 37
  • 143
  • 243
26
votes
5 answers

MySQL - How do I update the decimal column to allow more digits?

I'm a beginner in MySQL, and I accidentally created a table with a column named (price decimal(2,2)); It needs to be decimal(4,2) to allow 4 digits. Since I've already created it, what is the easiest way to update that decimal value to…
ValleyDigital
  • 1,460
  • 4
  • 21
  • 37
23
votes
1 answer

Add constraint to existing SQLite table

I'm using SQLite, which doesn't support adding a constraint to an existing table. So I can't do something like this (just as an example): ALTER TABLE [Customer] ADD CONSTRAINT specify_either_phone_or_email CHECK (([Phone] IS NOT NULL) OR ([Email] IS…
grokky
  • 8,537
  • 20
  • 62
  • 96
23
votes
3 answers

How do I drop 'NOT NULL' from a column in MySQL?

A show create table command shows the following: 'columnA' varchar(6) NOT NULL DEFAULT ''; How do I modify that column so that the not null is removed? I need it to be: 'columnA' varchar(6) DEFAULT NULL; I thought the following would work, but it…
Will
  • 875
  • 2
  • 8
  • 19
22
votes
10 answers

check if column exists before ALTER TABLE -- mysql

Is there a way to check if a column exists in a mySQL DB prior to (or as) the ALTER TABLE ADD coumn_name statement runs? Sort of an IF column DOES NOT EXIST ALTER TABLE thing. I've tried ALTER IGNORE TABLE my_table ADD my_column but this still…
julio
  • 6,630
  • 15
  • 60
  • 82
1 2 3
…
74 75