CODEWITHSUNDEEP
databaseoracle-databasesql-scripts
Charychap
Charychap

Reputation: 69

Alter column length with or without data in table

Its about ORACLE (PL/SQL) script. I am not very familiar with databse to be honest. I want to alter the length of a string in a column from 30 to 60. It is not null column. If the table is empty and I run following script then it works:

alter table [TABLE_NAME] add ( NEW_COLUMN NVARCHAR2(60)  DEFAULT 'null' NOT NULL );
/
alter table [TABLE_NAME] DROP CONSTRAINT PK_[TABLE_NAME];
/
begin
   for rec in ( select * from [TABLE_NAME] )
   loop
      update [TABLE_NAME] set NEW_COLUMN =rec.OLD_COLUMN where Name_ID=rec.Name_ID;
   end loop;
end;
/
alter table [TABLE_NAME] drop column OLD_COLUMN;
/
alter table [TABLE_NAME] rename column NEW_COLUMN to OLD_COLUMN;
/
alter table [TABLE_NAME] add CONSTRAINT PK_[TABLE_NAME] PRIMARY KEY(Name_ID);
/

But if the table has values then this script does not work. It gives error: Cannot drop constraint - nonexistent constraint

However, if I remove lines about constraints (second and second last) then it works. Now I don’t know if the table will be empty or it will have data so I need a script that can work in both the situations. Can anyone help please?

Following script for creating table:

CREATE TABLE TABLE_NAME
(
Name_ID NVARCHAR2(7) NOT NULL,
OLD_COLUMN NVARCHAR2(30) NOT NULL,
CONSTRAINT PK_TABLE_NAME PRIMARY KEY(Name_ID, OLD_COLUMN)
)
/

So while creating table it puts the primary key constraints but while updating table it drops this constraints somehow. I am simplyfying the sitation here. The tables are updates through java code. What I need to do is make a script that work in both situations - with data or just after creating table and modifying the column.

Upvotes: 0

Views: 867

Answers (2)

vlad
vlad

Reputation: 21

As an alternative, you can save the old data and create a new table with new parameters. Then insert the old values.

enter image description here

In SQL Server Management Studio:

"your database" => task => generatescripts => select specific database object => "your table" => advanced => types of data to script - schema and data => generate

Upvotes: 1

Boneist
Boneist

Reputation: 23588

The following script works for me, regardless of whether the insert statement is present or not (ie. the table has or has not data):

CREATE TABLE TABLE_NAME
(
Name_ID NVARCHAR2(7) NOT NULL,
OLD_COLUMN NVARCHAR2(30) NOT NULL,
CONSTRAINT PK_TABLE_NAME PRIMARY KEY(Name_ID, OLD_COLUMN)
);

insert into table_name (name_id, old_column)
values ('test', 'test_old_col');

commit;

alter table table_name add (new_column nvarchar2(60) default 'null' not null);

update table_name set new_column = old_column;

commit;

alter table table_name drop constraint PK_TABLE_NAME;

alter table table_name drop column old_column;

alter table table_name rename column new_column to old_column;

alter table TABLE_NAME add CONSTRAINT PK_TABLE_NAME PRIMARY KEY(Name_ID, old_column);

drop table table_name;

I have assumed that you meant to recreate the primary key with the old_column in it, otherwise you would be unable to recreate it if there are any duplicate values present in the name_id column.

Upvotes: 1

Related Questions