Sql server alter table alter column

Практическое упражнение №3:

На основании таблицы customers ниже, добавьте два столбца. Один столбец с названием contact_name и типом данных VARCHAR2 (50), второй столбец с названием last_contacted и типом данных DATE.

Oracle PL/SQL

CREATE TABLE customers
( customer_id number(10) not null,
customer_name varchar2(50) not null,
address varchar2(50),
city varchar2(50),
state varchar2(25),
zip_code varchar2(10),
CONSTRAINT customers_pk PRIMARY KEY (customer_id)
);

1
2
3
4
5
6
7
8
9

CREATETABLEcustomers
(customer_idnumber(10)notnull,

customer_namevarchar2(50)notnull,

addressvarchar2(50),

cityvarchar2(50),

statevarchar2(25),

zip_codevarchar2(10),

CONSTRAINTcustomers_pkPRIMARYKEY(customer_id)
);

Решение для практического упражнения №3:

Следующий оператор Oracle/PLSQL ALTER TABLE добавит столбцы contact_name и last_contacted в таблицу customers:

Oracle PL/SQL

ALTER TABLE customers
ADD (contact_name varchar2(50),
last_contacted date);

1
2
3

ALTERTABLEcustomers

ADD(contact_namevarchar2(50),

last_contacteddate);

Представления, относящиеся к ограничениям и индексам

Как определить, какие ограничения существуют на столбцах таблицы? Когда процесс завершается неудачей с сообщением о нарушении ссылочного ограничения целостности, как лучше всего определить, какого ограничения и какой таблицы это касается? Для решения подобных проблем необходимы представления словаря данных, относящиеся к ограничениям и индексам. Далее подробно рассматриваются ключевые ограничения такого рода.

DBA_CONSTRAINTS

Представление DBA_CONSTRAINTS предоставляет информацию обо всех типах ограничений таблицы в базе данных. Опрос этого представления делается, когда необходимо узнать, какого типа ограничения имеет таблица. Представление перечисляет несколько типов ограничений, как показано в следующем запросе: 

SQL> SELECT DISTINCT constraint_type FROM DBA_CONSTRAINTS;
Constraint_type
--------------------
C                      /* check constraints */
                       /* ограничения проверки */
P                      /* primary key constraint */
                       /* ограничение первичного ключа */
R                      /* referential integrity (foreign key) constraint */
                       /* ограничение ссылочной целостности (внешний ключ) */
U                      /* unique key constraint */
                       /* ограничение уникального ключа */
SQL>

Следующий запрос позволит узнать, какие ограничения установлены для таблицы TESTD. Ответ на запрос показывает, что таблица имеет единственное ограничение CHECK. Префикс SYS в столбце NAME отражает тот факт, что CONSTRAINT_NAME — имя по умолчанию, а не явно указанное владельцем таблицы. 

SQL> SELECT constraint_name, constraint_type
2 FROM DBA_CONSTRAINTS
3* WHHERE table_name='TESTD';
CONSTRAINT_NAME CONSTRAINT_TYPE
------------------- ---------------
SYS_C005263 C
SQL>

Обратите внимание, что если необходимо увидеть определенное ссылочное ограничение и правило удаления, то нужно применить следующую вариацию предыдущего запроса: 

SQL> SELECT constraint_name, constraint_type,
R_constraint_name, delete_rule
FROM dba_constraints
WHERE table_name='ORDERS';
CONSTRAINT_NAME         TYPE   R_CONSTRAINT_NAME  DELETE_RULE
----------------------  ------ -----------------  -----------
ORDER_DATE_NN           C
ORDER_CUSTOMER_ID_NN    C
ORDER_MODE_LOV          C
ORDER_TOTAL_MIN         C
ORDER_PK                P
ORDERS_SALES_REP_FK     R      EMP_EMP_ID_PK      SET NULL
ORDERS_CUSTOMER_ID_FK   R      CUSTOMERS_PK       SET NULL
7 rows selected.
SQL>

DBA_CONS_COLUMNS

Представление DBA_CONS_COLUMNS показывает имя столбца и позицию в таблице,где определено ограничение:

SQL> DESC DBA_CONS_COLUMNS
Name
----------------
OWNER
CONSTRAINT_NAME
TABLE_NAME
COLUMN_NAME
POSITION
SQL> 

Вас заинтересует / Intresting for you:

Поддерживаемые Oracle типы дан…

3169 просмотров

Валерий Павлюков

Wed, 24 Oct 2018, 08:00:37

Видеокурс по администрированию…

4125 просмотров

Илья Дергунов

Mon, 14 May 2018, 05:08:47

Обновление до Oracle Database …

3837 просмотров

Илья Дергунов

Tue, 21 Nov 2017, 13:18:05

Создание базы данных Oracle

10366 просмотров

Александров Попков

Wed, 14 Nov 2018, 12:44:39

Author: Илья Дергунов

Другие статьи автора:

Изменить несколько столбцов в таблице

Синтаксис

Синтаксис для изменения нескольких столбцов в таблице в PostgreSQL (используя ALTER TABLE):

ALTER TABLE table_name ALTER COLUMN column_name TYPE column_definition, ALTER COLUMN column_name TYPE column_definition, … ;

table_name
Имя таблицы для изменения.
column_name
Имя столбца, который нужно изменить в таблице.
column_definition
Измененный тип данных столбца.

Пример

Рассмотрим пример, который показывает, как изменить несколько столбцов в таблице PostgreSQL с помощью оператора ALTER TABLE. Например:

PgSQL

ALTER TABLE order_details
ALTER COLUMN notes TYPE varchar(500),
ALTER COLUMN quantity TYPE numeric;

1
2
3

ALTERTABLEorder_details

ALTERCOLUMNnotesTYPEvarchar(500),

ALTERCOLUMNquantityTYPEnumeric;

В этом примере ALTER TABLE будут изменены два столбца таблицы order_details — notes и quantity. Поле notes будет изменен на тип данных varchar(500), а столбец quantity будет изменен на тип данных numeric.

Practice Exercise #5:

Based on the customers table below, change the customer_name column to NOT allow null values and change the state column to a varchar2(2) datatype.

CREATE TABLE customers
( customer_id number(10) NOT NULL,
  customer_name varchar2(50),
  address varchar2(50),
  city varchar2(50),
  state varchar2(25),
  zip_code varchar2(10),
  CONSTRAINT customers_pk PRIMARY KEY (customer_id)
);

Solution for Practice Exercise #5:

The following Oracle ALTER TABLE statement would modify the customer_name and state columns accordingly in the customers table:

ALTER TABLE customers
  MODIFY (customer_name varchar2(50) NOT NULL,
          state varchar2(2));

Not the answer you’re looking for? Browse other questions tagged sql sql-server sql-server-2005 sql-server-2000 or ask your own question.

The Overflow Blog

How to develop a defensive plan for your open-source software project

Coming together as a community to connect

Featured on Meta

The Q1 2020 Community Roadmap is on the Blog

An Update On Creative Commons Licensing

Community and Moderator guidelines for escalating issues via new response…

Triage needs to be fixed urgently, and users need to be notified upon…

How does the Triage queue work?

Visit chat

Linked

33

Add a column of zeros to table sql server

4

How do I add a column, with a default value, to an existing table in SQL Server 2008 or 2012?

-1

alter column with default value

-3

Add Column to an existing SQL Server table along with data

366

How to set a default value for an existing column

258

Add default value of datetime field in SQL Server to a timestamp

80

Equivalent of Oracle’s RowID in SQL Server

71

use current date as default value for a column

42

Code-first migration: How to set default value for new property?

19

Add a Column that Represents a Concatenation of Two Other Varchar Columns

see more linked questions…

Hot Network Questions

  • Are there significant additional risks in changing jobs during the COVID-19 pandemic?

  • Name your novel disease

  • How should we interpret Dave Cutler’s criticism of Unix?

  • The Single Digit — a chess riddle

  • Why would a B-29 consume more fuel at 17 kft than at 9 kft?

  • Numbers by Position

  • KoTH: Highest HP is Eliminated!

  • Commutator of finite global dimension algebras

  • Does a ranged casting distance include touch?

  • How to find n points on a plane so that as many pairs of points as possible have the same distance?

  • I am yet to grow old

  • What is the context of the button «Is MS DOS a feminist?»

  • Why is Bxh6 not preferred here?

  • What are the implications of the EARN IT Act?

  • When use setq or set ‘

  • Remove a specific element of a TimeSeries?

  • Why is there no ruling to offset Mandatory Loops?

  • Why do Western countries seem to be less efficient than Asian countries in fighting against COVID-19?

  • Why would the offspring of a clone not suffer from a degenerative illness?

  • Conservation of Energy with Identical Rolling Disks

  • Could vikings make something close to modern reverse draw crossbow?

  • Required documents on arrival with person waiting outside, at arrival area. How can the documents be provided to the person arriving, at customs?

  • Words that only differ in tones

  • No electrical to the dashboard after attempting jumpstart with Schumacher charger

Question feed

Stack Overflow works best with JavaScript enabled

Remove fragmentation

Remove fragmentation using SQL Server Management Studio

To reorganize or rebuild an index

  1. In Object Explorer, Expand the database that contains the table on which you want to reorganize an index.
  2. Expand the Tables folder.
  3. Expand the table on which you want to reorganize an index.
  4. Expand the Indexes folder.
  5. Right-click the index you want to reorganize and select Reorganize.
  6. In the Reorganize Indexes dialog box, verify that the correct index is in the Indexes to be reorganized grid and click OK.
  7. Select the Compact large object column data check box to specify that all pages that contain large object (LOB) data are also compacted.
  8. Click OK.

Note

Reorganizing a columnstore index using Management Studio will combine rowgroups together, but does not force all rowgroups to be compressed into the columnstore. CLOSED rowgroups will be compressed but OPEN rowgroups will not be compressed into the columnstore. To compress all rowgroups, use the Transact-SQL example .

  1. In Object Explorer, Expand the database that contains the table on which you want to reorganize the indexes.
  2. Expand the Tables folder.
  3. Expand the table on which you want to reorganize the indexes.
  4. Right-click the Indexes folder and select Reorganize All.
  5. In the Reorganize Indexes dialog box, verify that the correct indexes are in the Indexes to be reorganized. To remove an index from the Indexes to be reorganized grid, select the index and then press the Delete key.
  6. Select the Compact large object column data check box to specify that all pages that contain large object (LOB) data are also compacted.
  7. Click OK.

To rebuild an index

  1. In Object Explorer, Expand the database that contains the table on which you want to reorganize an index.
  2. Expand the Tables folder.
  3. Expand the table on which you want to reorganize an index.
  4. Expand the Indexes folder.
  5. Right-click the index you want to reorganize and select Rebuild.
  6. In the Rebuild Indexes dialog box, verify that the correct index is in the Indexes to be rebuilt grid and click OK.
  7. Select the Compact large object column data check box to specify that all pages that contain large object (LOB) data are also compacted.
  8. Click OK.

Remove fragmentation using Transact-SQL

Note

For more examples about using Transact-SQL to rebuild or reorganize indexes, see and .

To reorganize a fragmented index

The following example reorganizes the index on the table in the database.

The following example reorganizes the columnstore index on the table in the database.

To rebuild all indexes in a table

The following example rebuilds all indexes associated with the table in the database using the keyword. Three options are specified.

For more information, see ALTER INDEX (Transact-SQL).

Automatic index and statistics management

Leverage solutions such as Adaptive Index Defrag to automatically manage index defragmentation and statistics updates for one or more databases. This procedure automatically chooses whether to rebuild or reorganize an index according to its fragmentation level, amongst other parameters, and update statistics with a linear threshold.

PostgreSQL ADD COLUMN examples

The following CREATE TABLE statement creates a new table named with two columns: and :

1
2
3
4

CREATETABLEcustomers(

idSERIALPRIMARYKEY,

customer_nameVARCHARNOT NULL

);

To add the column to the table, you use the following statement:

1
2

ALTERTABLEcustomers

ADDCOLUMNphoneVARCHAR;

1
2
3

ALTERTABLEcustoemr

ADDCOLUMNfaxVARCHAR,

ADDCOLUMNemailVARCHAR;

The following command describes the   table structure.

1 #\dcustomers
1
2
3
4
5
6
7
8
9
10

Table»public.customers»

Column|Type|Modifiers
—————+——————-+———————————————————

id|integer|not nulldefaultnextval(‘customers_id_seq’::regclass)

customer_name|character varying|

phone|character varying|

fax|character varying|

email|character varying|

Indexes:

«customers_pkey»PRIMARYKEY,btree(id)

Modify column in table

Syntax

To MODIFY A COLUMN in an existing table, the Oracle ALTER TABLE syntax is:

ALTER TABLE table_name
  MODIFY column_name column_type;

Example

Let’s look at an example that shows how to modify a column in an Oracle table using the ALTER TABLE statement.

For example:

ALTER TABLE customers
  MODIFY customer_name varchar2(100) NOT NULL;

This Oracle ALTER TABLE example will modify the column called customer_name to be a data type of varchar2(100) and force the column to not allow null values.

In a more complicated example, you could use the ALTER TABLE statement to add a default value as well as modify the column definition:

ALTER TABLE customers
  MODIFY city varchar2(75) DEFAULT 'Seattle' NOT NULL;

Примеры

Добавление в таблицу столбца типа :

ALTER TABLE distributors ADD COLUMN address varchar(30);

Удаление столбца из таблицы:

ALTER TABLE distributors DROP COLUMN address RESTRICT;

Изменение типов двух существующих столбцов в одной операции:

ALTER TABLE distributors
    ALTER COLUMN address TYPE varchar(80),
    ALTER COLUMN name TYPE varchar(100);

Смена типа целочисленного столбца, содержащего время в стиле Unix, на тип с применением предложения :

ALTER TABLE foo
    ALTER COLUMN foo_timestamp SET DATA TYPE timestamp with time zone
    USING
        timestamp with time zone 'epoch' + foo_timestamp * interval '1 second';

То же самое, но в случае, когда у столбца есть значение по умолчанию, не приводимое автоматически к новому типу данных:

ALTER TABLE foo
    ALTER COLUMN foo_timestamp DROP DEFAULT,
    ALTER COLUMN foo_timestamp TYPE timestamp with time zone
    USING
        timestamp with time zone 'epoch' + foo_timestamp * interval '1 second',
    ALTER COLUMN foo_timestamp SET DEFAULT now();

Переименование существующего столбца:

ALTER TABLE distributors RENAME COLUMN address TO city;

Переименование существующей таблицы:

ALTER TABLE distributors RENAME TO suppliers;

Переименование существующего ограничения:

ALTER TABLE distributors RENAME CONSTRAINT zipchk TO zip_check;

Добавление в столбец ограничения NOT NULL:

ALTER TABLE distributors ALTER COLUMN street SET NOT NULL;

Удаление ограничения NOT NULL из столбца:

ALTER TABLE distributors ALTER COLUMN street DROP NOT NULL;

Добавление ограничения-проверки в таблицу и все её потомки:

ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5);

Добавление ограничения-проверки только в таблицу, но не в её потомки:

ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5) NO INHERIT;

(Данное ограничение-проверка не будет наследоваться и будущими потомками тоже.)

Удаление ограничения-проверки из таблицы и из всех её потомков:

ALTER TABLE distributors DROP CONSTRAINT zipchk;

Удаление ограничения-проверки только из самой таблицы:

ALTER TABLE ONLY distributors DROP CONSTRAINT zipchk;

(Ограничение-проверка остаётся во всех дочерних таблицах.)

Добавление в таблицу ограничения внешнего ключа:

ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address);

Добавление в таблицу ограничения внешнего ключа с наименьшим влиянием на работу других:

ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address) NOT VALID;
ALTER TABLE distributors VALIDATE CONSTRAINT distfk;

Добавление в таблицу ограничения уникальности (по нескольким столбцам):

ALTER TABLE distributors ADD CONSTRAINT dist_id_zipcode_key UNIQUE (dist_id, zipcode);

Добавление в таблицу первичного ключа с автоматическим именем (учтите, что в таблице может быть только один первичный ключ):

ALTER TABLE distributors ADD PRIMARY KEY (dist_id);

Перемещение таблицы в другое табличное пространство:

ALTER TABLE distributors SET TABLESPACE fasttablespace;

Перемещение таблицы в другую схему:

ALTER TABLE myschema.distributors SET SCHEMA yourschema;

Пересоздание ограничения первичного ключа без блокировки изменений в процессе перестроения индекса:

CREATE UNIQUE INDEX CONCURRENTLY dist_id_temp_idx ON distributors (dist_id);
ALTER TABLE distributors DROP CONSTRAINT distributors_pkey,
    ADD CONSTRAINT distributors_pkey PRIMARY KEY USING INDEX dist_id_temp_idx;

Присоединение секции к таблице, разбиваемой по диапазонам:

ALTER TABLE measurement
    ATTACH PARTITION measurement_y2016m07 FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');

Присоединение секции к таблице, разбиваемой по списку:

ALTER TABLE cities
    ATTACH PARTITION cities_ab FOR VALUES IN ('a', 'b');

Удаление секции из секционированной таблицы:

Change Statement

The following table contains the fields of employee table and it shows the fields to be changed (in bold).

Field Name Convert from Data Type Change Field Name Convert to Data Type
eid int eid int
name String ename String
salary Float salary Double
designation String designation String

The following queries rename the column name and column data type using the above data:

hive> ALTER TABLE employee CHANGE name ename String;
hive> ALTER TABLE employee CHANGE salary salary Double;

JDBC Program

Given below is the JDBC program to change a column.

import java.sql.SQLException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.DriverManager;

public class HiveAlterChangeColumn {
   private static String driverName = "org.apache.hadoop.hive.jdbc.HiveDriver";
   
   public static void main(String[] args) throws SQLException {
   
      // Register driver and create driver instance
      Class.forName(driverName);
      
      // get connection
      Connection con = DriverManager.getConnection("jdbc:hive://localhost:10000/userdb", "", "");
      
      // create statement
      Statement stmt = con.createStatement();
      
      // execute statement
      stmt.executeQuery("ALTER TABLE employee CHANGE name ename String;");
      stmt.executeQuery("ALTER TABLE employee CHANGE salary salary Double;");
      
      System.out.println("Change column successful.");
      con.close();
   }
}

Save the program in a file named HiveAlterChangeColumn.java. Use the following commands to compile and execute this program.

$ javac HiveAlterChangeColumn.java
$ java HiveAlterChangeColumn

Introduction to the PostgreSQL ADD COLUMN statement

To add a new column to an existing table, you use the   statement as follows:

1
2

ALTERTABLEtable_name

ADDCOLUMNnew_column_namedata_type;

Let’s examine the statement in a greater detail.

  • First, specify the table that you want to add a new column in the clause.
  • Second, indicate the column name with its attribute such as data type, default value, etc., in the clause.

When you add a new column to the table, PostgreSQL appends it at the end of the table. PostgreSQL has no option to specify the position of the new column in the table.

To add multiple columns to an existing table, you use multiple clauses in the statement as follows:

1
2
3
4
5

ALTERTABLEtable_name

ADDCOLUMNnew_column_name_1data_typeconstraint,

ADDCOLUMNnew_column_name_2data_typeconstraint,

ADDCOLUMNnew_column_name_ndata_typeconstraint;

Замечания

Ключевое слово COLUMN не несёт смысловой нагрузки и может быть опущено.

Когда колонка добавляется с помощью ADD COLUMN, во всех существующих в таблице строках эта колонка инициализируется значением по умолчанию (или NULL, если предложение DEFAULT для колонки отсутствует). Если предложение DEFAULT отсутствует, это сводится только к изменению метаданных, непосредственного изменения данных таблицы не происходит; добавленные значения NULL выводятся при чтении.

Добавление колонки с предложением DEFAULT или изменение типа существующей колонки влечёт за собой перезапись всей таблицы и её индексов. Но возможно исключение при смене типа существующей колонки: если предложение USING не меняет содержимое колонки и старый тип двоично приводится к новому или является неограниченным доменом поверх нового типа, перезапись таблицы не требуется; хотя все индексы с затронутыми колонками всё же требуется перестроить. При добавлении или удалении системной колонки clause oid также необходима перезапись всей таблицы. Для перестроения больших таблиц и/или их индексов может понадобиться довольно много времени и временно потребуется вдвое больше места на диске.

При добавлении ограничений CHECK или NOT NULL требуется просканировать таблицу с целью убедиться в том, что все существующие строки удовлетворяют ограничению.

Возможность объединения множества изменений в одну команду ALTER TABLE полезна в основном тем, что позволяет совместить сканирования и перезаписи таблицы, требуемые этим операциям, и выполнить их за один проход.

Форма DROP COLUMN не удаляет колонку физически, а просто делает её невидимой для операций SQL. При последующих операциях добавления или изменения в эту колонку будет записываться значение NULL. Таким образом, удаление колонки выполняется быстро, но при этом размер таблицы на диске не уменьшается, так как пространство, занимаемое удалённой колонкой, не высвобождается. Это пространство будет освобождено со временем, по мере изменения существующих строк. (При удалении системной колонки oid это поведение не наблюдается, так как немедленно выполняется перезапись таблицы.)

Чтобы выполнить перезапись таблицы немедленно, можно воспользоваться командой VACUUM FULL, CLUSTER или одной из форм ALTER TABLE, инициирующих перезапись. При этом никаких семантически видимых изменений в таблице не происходит, но таблица избавляется от уже неиспользуемых данных.

В указании USING предложения SET DATA TYPE на самом деле можно записать выражение со старыми значениями строки; то есть, оно может ссылаться как на преобразуемые колонки, так и на другие. Это позволяет записывать в SET DATA TYPE очень общие преобразования данных. Ввиду такой гибкости, выражение USING не применяется к значению по умолчанию данной колонки (если таковое есть); результат может быть не константным выражением, что требуется для значения по умолчанию. Это означает, что в случае отсутствия явного приведения или присваивания старого типа новому, SET DATA TYPE может не справиться с преобразованием значения по умолчанию, несмотря на то, что применяется предложение USING. В этих случаях нужно удалить значение по умолчанию с помощью DROP DEFAULT, выполнить ALTER TYPE, а затем с помощью SET DEFAULT задать новое подходящее значение по умолчанию. Подобные соображения применимы и в отношении индексов и ограничений с этой колонкой.

Если у таблицы есть дочерние таблицы, добавлять, переименовывать или менять тип колонки, либо переименовывать наследуемое ограничение в родительской таблице, не делая того же самого во всех дочерних таблицах, нельзя. То есть, команда ALTER TABLE ONLY выполнена не будет. Это гарантирует, что дочерние таблицы всегда будут содержать те же колонки, что и родительская.

Рекурсивная операция DROP COLUMN удалит колонку из дочерней таблицы, только если эта колонка не наследуется от каких-то других родителей и не была определена в дочерней таблице независимо. Нерекурсивная операция DROP COLUMN (т. е., ALTER TABLE ONLY ... DROP COLUMN) никогда не удаляет унаследованные колонки, но вместо этого помечает их как независимо определённые, а не наследуемые.

Действия TRIGGER, CLUSTER, OWNER и TABLESPACE никогда не распространяются рекурсивно на дочерние таблицы; то есть, они всегда выполняются так, как будто указано ONLY. Операция добавления ограничения выполняется рекурсивно только для ограничений CHECK, не помеченных как NO INHERIT.

Какие-либо изменения таблиц системного каталога не допускаются.

SQL ALTER TABLE Example

Look at the «Persons» table:

ID LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger

Now we want to add a column named «DateOfBirth» in the «Persons» table.

We use the following SQL statement:

ALTER TABLE Persons
ADD DateOfBirth date;

Notice that the new column, «DateOfBirth», is of type date and is going to hold a
date. The data type specifies what type of data the column can hold. For a complete
reference of all the data types available in MS Access, MySQL, and SQL Server,
go to our complete Data Types reference.

The «Persons» table will now look like this:

ID LastName FirstName Address City DateOfBirth
1 Hansen Ola Timoteivn 10 Sandnes  
2 Svendson Tove Borgvn 23 Sandnes  
3 Pettersen Kari Storgt 20 Stavanger  
Ссылка на основную публикацию