شرح - إضافة عمود مع قيمة افتراضية إلى جدول موجود في SQL Server




sql server2005 (20)

كيف يمكن إضافة عمود له قيمة افتراضية إلى جدول موجود في SQL Server 2000 / SQL Server 2005 ؟


بناء الجملة:

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

مثال:

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.

ملاحظات:

اسم القيد الاختياري:
إذا تركت CONSTRAINT D_SomeTable_SomeCol فسيتم إنشاء خادم SQL
a-Contraint الافتراضي مع مضحكة اسم مثل: DF__SomeTa__SomeC__4FB7FEF6

بيان القيم الاختياري:
مطلوب WITH VALUES فقط عندما يكون العمود الخاص بك هو Nullable
وتريد استخدام القيمة الافتراضية للسجلات الموجودة.
إذا كان العمود الخاص بك NOT NULL ، NOT NULL تلقائيًا القيمة الافتراضية
لجميع السجلات الحالية ، سواء قمت بتحديد WITH VALUES أم لا.

كيفية عمل الإدخالات باستخدام قيد افتراضي:
إذا قمت بإدراج سجل في SomeTable ولم تقم بتحديد قيمة SomeCol ، SomeCol القيمة الافتراضية 0 .
إذا قمت بإدراج قيمة Record و Specify SomeCol NULL (ويسمح العمود الخاص بك بالقيم الفارغة) ،
ثم لن يتم استخدام القيد الافتراضي وسيتم إدراج NULL كقيمة.

تستند الملاحظات على ملاحظات الجميع الرائعة أدناه.
شكر خاص ل:
تضمين التغريدة ردًا علىYatrix،WalterStabosz،YahooSerious، و @ StackMan للتعليقات.


SQL Server + Alter Table + Add unique Column + Default Value uniqueidentifier ...

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

إذا كان الإعداد الافتراضي هو Null ، فحينئذٍ:

  1. في SQL Server ، افتح شجرة الجدول المستهدف
  2. انقر بزر الفأرة الأيمن "Columns" ==> New Column
  3. اكتب اسم العمود ، Select Type ، وتحقق من خانة الاختيار السماح بالقيم الخالية
  4. من شريط القوائم ، انقر فوق Save

فعله!


إذا كنت تريد إضافة أعمدة متعددة ، فيمكنك القيام بذلك على سبيل المثال:

ALTER TABLE YourTable
    ADD Column1 INT NOT NULL DEFAULT 0,
        Column2 INT NOT NULL DEFAULT 1,
        Column3 VARCHAR(50) DEFAULT 'Hello'
GO

احذر عندما يكون العمود الذي تضيفه قيد NOT NULL ، ومع ذلك لا يحتوي على قيد DEFAULT (قيمة). سوف تفشل العبارة ALTER TABLE في هذه الحالة إذا كان الجدول يحتوي على أي صفوف فيه. الحل هو إزالة القيد NOT NULL من العمود الجديد أو توفير قيد DEFAULT لها.


استعمال:

-- Add a column with a default DateTime  
-- to capture when each record is added.

ALTER TABLE myTableName  
ADD RecordAddedDate smalldatetime NULL DEFAULT(GetDate())  
GO 

النسخة الأساسية مع خطين فقط

ALTER TABLE MyTable
ADD MyNewColumn INT NOT NULL DEFAULT 0

بدلاً من ذلك ، يمكنك إضافة افتراضي دون الحاجة إلى تسمية القيد صراحةً:

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

إذا كانت لديك مشكلة في القيود الافتراضية الحالية عند إنشاء هذا القيد ، فيمكن إزالتها من خلال:

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

حسنًا ، لدي الآن بعض التعديل على إجابتي السابقة. لقد لاحظت أن أيا من الإجابات المذكورة IF NOT EXISTS . لذلك سأقدم حلاً جديدًا لها حيث واجهت بعض المشكلات في تغيير الجدول.

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

هنا TaskSheet هو اسم جدول معين و IsBilledToClient هو العمود الجديد الذي ستقوم بإدراجه و 1 القيمة الافتراضية. ويعني ذلك في العمود الجديد ما ستكون قيمة الصفوف الموجودة ، وبالتالي سيتم تعيين واحد تلقائيًا هناك. ومع ذلك ، يمكنك تغيير ما ترغب به مع احترام نوع العمود مثل استخدام BIT ، لذلك أضع القيمة الافتراضية 1.

أقترح النظام أعلاه ، لأنني واجهت مشكلة. فما هي المشكلة؟ المشكلة هي ، إذا كان عمود IsBilledToClient موجودًا في جدول الجدول ، وإذا قمت بتنفيذ جزء التعليمة البرمجية الموجود أدناه فقط ، IsBilledToClient خطأ في منشئ استعلام SQL server. ولكن إذا لم يكن موجودًا ، فلن يكون هناك أي خطأ عند التنفيذ لأول مرة.

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

عند إضافة عمود لاغية ، سيضمن WITH VALUES تطبيق قيمة DEFAULT المحددة على الصفوف الموجودة:

ALTER TABLE table
ADD column BIT     -- Demonstration with NULL-able column added
CONSTRAINT Constraint_name DEFAULT 0 WITH VALUES

لإضافة عمود إلى جدول قاعدة بيانات موجود بقيمة افتراضية ، يمكننا استخدام:

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

فيما يلي طريقة أخرى لإضافة عمود إلى جدول قاعدة بيانات موجود بقيمة افتراضية.

برنامج نصي SQL أكثر شمولا لإضافة عمود مع قيمة افتراضية هو أدناه بما في ذلك التحقق من وجود العمود قبل إضافة فإنه أيضا التحقق من القيد وإسقاطها إذا كان هناك واحد. هذا البرنامج النصي أيضا اسم القيد حتى نتمكن من الحصول على اصطلاح تسمية لطيفة (أنا أحب DF_) وإذا لم يكن SQL سيعطينا مع وجود اسم يحتوي على عدد عشوائي. لذلك من الجيد أن تكون قادرًا على تسمية القيد أيضًا.

-------------------------------------------------------------------------
-- 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

هذان طريقتان لإضافة عمود إلى جدول قاعدة بيانات موجود بقيمة افتراضية.


مثال:

ALTER TABLE [Employees] ADD Seniority int not null default 0 GO

هذا لديه الكثير من الأجوبة ، لكني أشعر بالحاجة إلى إضافة هذه الطريقة الموسعة. يبدو هذا أطول ، لكنه مفيد للغاية إذا كنت تضيف حقل NOT NULL إلى جدول بملايين الصفوف في قاعدة بيانات نشطة.

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

ما ستفعله هذا هو إضافة العمود كحقل nullable ومع القيمة الافتراضية ، تحديث كل الحقول إلى القيمة الافتراضية (أو يمكنك تعيين قيم أكثر معنى) ، وأخيرًا سيغير العمود ليكون NOT NULL.

السبب في ذلك هو إذا قمت بتحديث جدول مقياس كبير وإضافة حقل جديد غير فارغ عليه الكتابة إلى كل صف واحد ، وبذلك سيتم قفل الجدول بالكامل لأنه يضيف العمود ثم يكتب جميع القيم.

ستضيف هذه الطريقة العمود الفارغ الذي يعمل بشكل أسرع كثيرًا بنفسه ، ثم يملأ البيانات قبل تعيين الحالة غير الفارغة.

لقد وجدت أن القيام بكل شيء في بيان واحد سيؤدي إلى إغلاق أحد طاقتنا الأكثر نشاطًا لمدة 4-8 دقائق وكثيراً ما قتلت العملية. هذه الطريقة يستغرق كل جزء منها بضع ثوانٍ فقط ويؤدي إلى الحد الأدنى من القفل.

بالإضافة إلى ذلك ، إذا كان لديك جدول في منطقة المليارات من الصفوف ، فقد يكون من المفيد تجميع التحديث مثل:

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

    IF @@ROWCOUNT < 1000000
        BREAK;
END

ويمكن القيام بذلك عن طريق الرمز أدناه.

CREATE TABLE TestTable
    (FirstCol INT NOT NULL)
    GO
    ------------------------------
    -- Option 1
    ------------------------------
    -- Adding New Column
    ALTER TABLE TestTable
    ADD SecondCol INT
    GO
    -- Updating it with Default
    UPDATE TestTable
    SET SecondCol = 0
    GO
    -- Alter
    ALTER TABLE TestTable
    ALTER COLUMN SecondCol INT NOT NULL
    GO

يمكنك القيام بهذا الشيء مع T-SQL بالطريقة التالية.

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

بالإضافة إلى ذلك ، يمكنك استخدام SQL Server Management Studio أيضًا بالنقر بزر الماوس الأيمن فوق الجدول في قائمة التصميم ، مع تعيين القيمة الافتراضية على الجدول.

علاوة على ذلك ، إذا كنت ترغب في إضافة نفس العمود (إذا لم يكن موجودًا) إلى جميع الجداول في قاعدة البيانات ، فاستخدم:

 USE AdventureWorks;
 EXEC sp_msforeachtable
'PRINT ''ALTER TABLE ? ADD Date_Created DATETIME DEFAULT GETDATE();''' ;

أولاً ، أنشئ جدولًا باسم الطالب:

CREATE TABLE STUDENT (STUDENT_ID INT NOT NULL)

أضف عمودًا واحدًا إليه:

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

SELECT * 
FROM STUDENT

يتم إنشاء الجدول وإضافة عمود إلى جدول موجود مع قيمة افتراضية.


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

يحتوي MSDN المقال ALTER TABLE (Transact-SQL) على كل من بناء جملة الجدول البديل.


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

يملأ تضمين DEFAULT العمود في الصفوف الموجودة بالقيمة الافتراضية ، لذلك لا يتم انتهاك القيد NOT NULL.


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

من هذا الاستعلام يمكنك إضافة عمود من عدد صحيح نوع البيانات مع القيمة الافتراضية 0.





sql-server-2000