posicion - Agregue una columna con un valor predeterminado a una tabla existente en SQL Server




modificar valor por defecto de una columna sql (20)

Sintaxis:

ALTER TABLE {TABLENAME} 
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} 
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
WITH VALUES

Ejemplo:

ALTER TABLE SomeTable
        ADD SomeCol Bit NULL --Or NOT NULL.
 CONSTRAINT D_SomeTable_SomeCol --When Omitted a Default-Constraint Name is autogenerated.
    DEFAULT (0)--Optional Default-Constraint.
WITH VALUES --Add if Column is Nullable and you want the Default Value for Existing Records.

Notas:

Nombre de restricción opcional:
Si deja de CONSTRAINT D_SomeTable_SomeCol , SQL Server se generará automáticamente.
un Default-Contraint con un nombre divertido como: DF__SomeTa__SomeC__4FB7FEF6

Declaración opcional con valores:
WITH VALUES solo es necesario cuando su columna es anulable
y desea que el valor predeterminado se utilice para los registros existentes.
Si su columna NOT NULL es NOT NULL , entonces usará automáticamente el valor predeterminado
para todos los registros existentes, ya sea que especifique WITH VALUES o no.

Cómo funcionan las inserciones con una restricción predeterminada:
Si inserta un registro en SomeTable y no especifica el valor de SomeCol , entonces el valor predeterminado es 0 .
Si inserta un registro y especifica el valor de SomeCol como NULL (y su columna permite nulos),
entonces la Restricción predeterminada no se utilizará y NULL se insertará como el Valor.

Las notas se basaron en los excelentes comentarios de todos a continuación.
Agradecimientos especiales a:
@Yatrix, @WalterStabosz, @YahooSerious y @StackMan para sus comentarios.

¿Cómo se puede agregar una columna con un valor predeterminado a una tabla existente en SQL Server 2000 / SQL Server 2005 ?


Agregar una nueva columna a una tabla:

ALTER TABLE [table]
ADD Column1 Datatype

Por ejemplo,

ALTER TABLE [test]
ADD ID Int

Si el usuario quiere que se incremente automáticamente, entonces:

ALTER TABLE [test]
ADD ID Int IDENTITY(1,1) NOT NULL

Alternativamente, puede agregar un valor predeterminado sin tener que nombrar explícitamente la restricción:

ALTER TABLE [schema].[tablename] ADD  DEFAULT ((0)) FOR [columnname]

Si tiene un problema con las restricciones predeterminadas existentes al crear esta restricción, puede eliminarlas mediante:

alter table [schema].[tablename] drop constraint [constraintname]

Bueno, ahora tengo alguna modificación a mi respuesta anterior. He notado que ninguna de las respuestas se menciona IF NOT EXISTS . Así que voy a proporcionar una nueva solución, ya que he enfrentado algunos problemas para alterar la tabla.

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.columns WHERE table_name = 'TaskSheet' AND column_name = 'IsBilledToClient')
BEGIN
ALTER TABLE dbo.TaskSheet ADD
 IsBilledToClient bit NOT NULL DEFAULT ((1))
END
GO

Aquí TaskSheet es el nombre de la tabla particular e IsBilledToClient es la nueva columna que va a insertar y 1 el valor predeterminado. Eso significa que en la nueva columna cuál será el valor de las filas existentes, por lo tanto, una se establecerá automáticamente allí. Sin embargo, puede cambiar lo que desee con respecto al tipo de columna, ya que he usado BIT , así que le puse el valor predeterminado 1.

Sugiero el sistema anterior, porque me he enfrentado a un problema. Entonces, ¿cuál es el problema? El problema es que si la columna IsBilledToClient existe en la tabla, si ejecuta solo la parte del código que se indica a continuación, verá un error en el generador de consultas del servidor SQL. Pero si no existe, por primera vez no habrá ningún error al ejecutar.

ALTER TABLE {TABLENAME}
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL}
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
[WITH VALUES]

Ejemplo:

ALTER TABLE tes 
ADD ssd  NUMBER   DEFAULT '0';

En SQL Server 2008-R2, voy al modo de diseño, en una base de datos de prueba, y agrego mis dos columnas con el diseñador y las configuraciones con la GUI, y luego el infame botón derecho da la opción " Generar un script de cambio " !

La explosión abre una pequeña ventana con, usted lo adivinó, la secuencia de comandos de cambio garantizada para el trabajo con el formato correcto. Pulse el botón fácil.


Esto también se puede hacer en la GUI de SSMS. Muestro una fecha predeterminada a continuación, pero el valor predeterminado puede ser cualquiera, por supuesto.

  1. Coloque su tabla en la vista de diseño (haga clic con el botón derecho en la tabla en el explorador de objetos-> Diseño)
  2. Agregue una columna a la tabla (o haga clic en la columna que desea actualizar si ya existe)
  3. En Propiedades de columna a continuación, ingrese (getdate()) o abc o 0 o el valor que desee en el campo Valor predeterminado o Enlace como se muestra a continuación:


Esto tiene muchas respuestas, pero siento la necesidad de agregar este método extendido. Esto parece mucho más largo, pero es extremadamente útil si está agregando un campo NOT NULL a una tabla con millones de filas en una base de datos activa.

ALTER TABLE {schemaName}.{tableName}
    ADD {columnName} {datatype} NULL
    CONSTRAINT {constraintName} DEFAULT {DefaultValue}

UPDATE {schemaName}.{tableName}
    SET {columnName} = {DefaultValue}
    WHERE {columName} IS NULL

ALTER TABLE {schemaName}.{tableName}
    ALTER COLUMN {columnName} {datatype} NOT NULL

Lo que esto hará es agregar la columna como un campo que puede contener nulos y con el valor predeterminado, actualizar todos los campos al valor predeterminado (o puede asignar valores más significativos), y finalmente cambiará la columna para que NO sea NULA.

La razón de esto es si actualiza una tabla a gran escala y agrega un nuevo campo no nulo que debe escribir en cada fila y, por lo tanto, bloqueará toda la tabla a medida que agrega la columna y luego escribe todos los valores.

Este método agregará la columna anulable que opera mucho más rápido por sí misma, luego llena los datos antes de establecer el estado de no nulo.

Descubrí que hacer todo esto en una sola declaración bloqueará una de nuestras tablas más activas durante 4-8 minutos y, con bastante frecuencia, he eliminado el proceso. Este método cada parte generalmente toma solo unos segundos y causa un bloqueo mínimo.

Además, si tiene una tabla en el área de miles de millones de filas, puede valer la pena agrupar la actualización así:

WHILE 1=1
BEGIN
    UPDATE TOP (1000000) {schemaName}.{tableName}
        SET {columnName} = {DefaultValue}
        WHERE {columName} IS NULL

    IF @@ROWCOUNT < 1000000
        BREAK;
END

Para agregar una columna a una tabla de base de datos existente con un valor predeterminado, podemos usar:

ALTER TABLE [dbo.table_name]
    ADD [Column_Name] BIT NOT NULL
Default ( 0 )

Esta es otra forma de agregar una columna a una tabla de base de datos existente con un valor predeterminado.

Una secuencia de comandos SQL mucho más completa para agregar una columna con un valor predeterminado se encuentra abajo, lo que incluye verificar si la columna existe antes de agregarla, también controlar la restricción y eliminarla si la hay. Esta secuencia de comandos también nombra la restricción para que podamos tener una buena convención de nomenclatura (me gusta DF_) y, si no es así, SQL nos dará una restricción con un nombre que tiene un número generado aleatoriamente; por lo que es bueno poder nombrar la restricción también.

-------------------------------------------------------------------------
-- Drop COLUMN
-- Name of Column: Column_EmployeeName
-- Name of Table: table_Emplyee
--------------------------------------------------------------------------
IF EXISTS (
            SELECT 1
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_NAME = 'table_Emplyee'
              AND COLUMN_NAME = 'Column_EmployeeName'
           )
    BEGIN

        IF EXISTS ( SELECT 1
                    FROM sys.default_constraints
                    WHERE object_id = OBJECT_ID('[dbo].[DF_table_Emplyee_Column_EmployeeName]')
                      AND parent_object_id = OBJECT_ID('[dbo].[table_Emplyee]')
                  )
            BEGIN
                ------  DROP Contraint

                ALTER TABLE [dbo].[table_Emplyee] DROP CONSTRAINT [DF_table_Emplyee_Column_EmployeeName]
            PRINT '[DF_table_Emplyee_Column_EmployeeName] was dropped'
            END
     --    -----   DROP Column   -----------------------------------------------------------------
        ALTER TABLE [dbo].table_Emplyee
            DROP COLUMN Column_EmployeeName
        PRINT 'Column Column_EmployeeName in images table was dropped'
    END

--------------------------------------------------------------------------
-- ADD  COLUMN Column_EmployeeName IN table_Emplyee table
--------------------------------------------------------------------------
IF NOT EXISTS (
                SELECT 1
                FROM INFORMATION_SCHEMA.COLUMNS
                WHERE TABLE_NAME = 'table_Emplyee'
                  AND COLUMN_NAME = 'Column_EmployeeName'
               )
    BEGIN
    ----- ADD Column & Contraint
        ALTER TABLE dbo.table_Emplyee
            ADD Column_EmployeeName BIT   NOT NULL
            CONSTRAINT [DF_table_Emplyee_Column_EmployeeName]  DEFAULT (0)
        PRINT 'Column [DF_table_Emplyee_Column_EmployeeName] in table_Emplyee table was Added'
        PRINT 'Contraint [DF_table_Emplyee_Column_EmployeeName] was Added'
     END

GO

Estas son dos formas de agregar una columna a una tabla de base de datos existente con un valor predeterminado.


Prueba esto

ALTER TABLE Product
ADD ProductID INT NOT NULL DEFAULT(1)
GO

SQL Server + Modificar tabla + Agregar columna + Valor predeterminado uniqueidentifier ...

ALTER TABLE [TABLENAME] ADD MyNewColumn INT not null default 0 GO

SQL Server + Modificar tabla + Agregar columna + Valor predeterminado uniqueidentifier

ALTER TABLE Product 
ADD ReferenceID uniqueidentifier not null 
default (cast(cast(0 as binary) as uniqueidentifier))

Si el valor predeterminado es nulo, entonces:

  1. En SQL Server, abra el árbol de la tabla de destino.
  2. Haga clic derecho en "Columnas" ==> New Column
  3. Escriba el nombre de la columna, Select Type y marque la casilla de verificación Permitir valores nulos
  4. En la barra de menú, haga clic en Save

¡Hecho!


Tenga cuidado cuando la columna que está agregando tiene una restricción NOT NULL , pero no tiene una restricción DEFAULT (valor). La instrucción ALTER TABLE fallará en ese caso si la tabla tiene filas. La solución es eliminar la restricción NOT NULL de la nueva columna o proporcionarle una restricción DEFAULT .


Utilizar:

ALTER TABLE {TABLENAME} 
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} 
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}

Referencia: ALTER TABLE (Transact-SQL) (MSDN)


Primero crea una tabla con el nombre de estudiante:

CREATE TABLE STUDENT (STUDENT_ID INT NOT NULL)

Añade una columna a ella:

ALTER TABLE STUDENT 
ADD STUDENT_NAME INT NOT NULL DEFAULT(0)

SELECT * 
FROM STUDENT

Se crea la tabla y se agrega una columna a una tabla existente con un valor predeterminado.


ALTER TABLE <table name> 
ADD <new column name> <data type> NOT NULL
GO
ALTER TABLE <table name> 
ADD CONSTRAINT <constraint name> DEFAULT <default value> FOR <new column name>
GO

ALTER TABLE ADD ColumnName {Column_Type} Constraint

El artículo de MSDN ALTER TABLE (Transact-SQL) tiene toda la sintaxis de la tabla alter.


ALTER TABLE Protocols
ADD ProtocolTypeID int NOT NULL DEFAULT(1)
GO

La inclusión de DEFAULT llena la columna en las filas existentes con el valor predeterminado, por lo que no se infringe la restricción NOT NULL.


ALTER TABLE tbl_table ADD int_column int NOT NULL DEFAULT(0)

Desde esta consulta puede agregar una columna de tipo de datos entero con el valor predeterminado 0.





sql-server-2000