Does anyone know of a way to alter a computed column without dropping the column in SQL Server. I want to stop using the column as a computed column and start storing data directly in the column, but would like to retain the current values. Is this even possible? ...
With which tool / library it is possible to update an existing database structure. On the update of the software it is also needed to change the database. Because there can be different versions of the software it should compare the current status with the target status of the database. It should: add table columns, fill it with defaul...
Hello, I am in a situation where I must update an existing database structure from varchar to nvarchar using a script. Since this script is run everytime a configuration application is run, I would rather determine if a column has already been changed to nvarchar and not perform an alter on the table. The databases which I must support...
We have a database that many persons have to have access to. I am looking for a way that will allow us to get notification whenever "alter" occurs on this database, so other parties can be aware of it. Please advise. ...
How do I ALTER the POSITION of a COLUMN in a Postgresql database? I've tried the following, but I was unsuccessful: ALTER TABLE person ALTER COLUMN dob POSITION 37; ...
When doing an ALTER TABLE statement in MySQL, the whole table is read-locked for the duration of the statement. If it's a big table, that means insert or update statements could be locked for a looooong time. Is there a way to do a "hot alter", like adding a column in such a way that the table is still updatable throughout the process? ...
I'm adding a new, "NOT NULL" column to my Postgresql database using the following query (sanitized for the Internet): ALTER TABLE mytable ADD COLUMN mycolumn character varying(50) NOT NULL; Each time I run this query, I receive the following error message: ERROR: column "mycolumn" contains null values I'm stumped. Where am I go...
Quick question, I was curious if there is any difference between a database table that has been defined in one shot, and one that has had columns added to it over time. Does the one with added columns performance suffer in someway? Anyways database vendor isn't too relevant here unless there are vendor differences for this case. Thanks...
I have a MySQL script which is executed automatically under certain conditions. That script executes an ALTER TABLE command, because that column is needed in the database, but it may or may not have it... Is it possible to make MySQL 4 execute the ALTER TABLE statement if the column doesn't exist or ignore the duplicate column error for...
I have the following table: CREATE TABLE [dbo].[EntityAttributeRelship]( [IdNmb] [int] IDENTITY(1,1) NOT NULL, [EntityIdNmb] [int] NOT NULL, [AttributeIdNmb] [int] NOT NULL, [IsActive] [bit] NOT NULL CONSTRAINT [DF_EntityAttributeRelship_IsActive] DEFAULT ((0)), CONSTRAINT [PK_EntityAttributeRelship] PRIMARY KEY CLUSTER...
I am trying to change a column from a varchar(50) to a nvarchar(200). What is the SQL command to alter this table? ...
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 table file is 50G. All tables are InnoDB, and it was set up as one big data file (instead ...
Hi, I am using the following code to check if the temp table exists and drop the table if it exists before creating again. It works fine as long as I don't change the columns. If I add a column later, it will give error saying "invalid column". Please let me know what I am doing wrong. IF OBJECT_ID('tempdb..#Results') IS NOT NULL D...
I am trying to add a column (MSSQL 2005) to a table (Employee) with a default constraint of a primary key of another table (Department). Then I am going to make this column a FK to that table. Essentially this will assign new employees to a base department based off the department name if no DepartmentID is provided. This does not work...
I have a table that has several nullable integer columns. This is undesirable for several reasons, so I am looking to update all nulls to 0 and then set these columns to "NOT NULL." Aside from changing nulls to 0, data must be preserved. I am looking for the specific sql syntax to alter a column (call it ColumnA) to "not null." Assum...
Assume that the default ordering of a MySQL-table (ISAM) is changed by executing: ALTER TABLE tablename ORDER BY columnname ASC; From now on, am I guaranteed to obtain records retrieved from the table in the order of "columnname ASC" assuming no "ORDER BY" is specified in my queries (i.e. "SELECT * FROM tablename WHERE ... LIMIT 10;")...
Suppose a check constraint (involving multiple columns) with the "not for replication" option was added to a database table (e.g. "alter table table_name add constraint constraint_name check not for replication (constraint_expression)") I found syntax for dropping "not for replication" from a column, but not for an existing check constr...
I would need to rename a few columns in some tables in a SQLite database. I know that a similar question has been asked on stackoverflow previously, but it was for SQL in general, and the case of SQLite was not mentioned. From the SQLite documentation for ALTER TABLE, I gather that it's not possible to do such a thing "easily" (i.e. a s...
Hi, To put it in a nutshell - In an oracle db, I want to make a column varchar2(16) which is now varchar2(8), without affecting the values presnet. I have already tried this and it does weird things. The query I tried was - alter table SOME_TABLE modify (SOME_COL varchar2(16)); But the values(some not all) already present in the table ...
We have a few customers with large data sets and during our upgrade procedure we need to modify the schema of various tables (adding some columns, renaming others, occasionally changing data types, but that's rare). Previously we've been going via a temporary table with the new schema, and then dropping the original and renaming the tem...