CODEWITHSUNDEEP
sql-serversql-server-2008
Baqer Naqvi
Baqer Naqvi

Reputation: 6504

How to set default value in SQL server 2008?

I have bulk amount of data stored in SQL Server 2008. Now I want to add new field [Book_ID] with default value 1 to existing table but its not working.

code

CREATE TABLE [dbo].[Ayyat_Translation_Language_old_20131209] (
    [Ayat_Translation_Language_ID] INT            IDENTITY (1, 1) NOT NULL,
    [Translation_Laanguage_ID]     INT            NULL,
    [Juz_ID]                       INT            NULL,
    [Surah_ID]                     INT            NOT NULL,
    [Ayat_Description]             NVARCHAR (MAX) COLLATE Arabic_CI_AI_KS_WS NOT NULL,
    [Ayat_No]                      INT            NULL,
    [Book_ID]                      INT NULL DEFAULT 1, 
    PRIMARY KEY CLUSTERED ([Ayat_Translation_Language_ID] ASC),
    CONSTRAINT [fkey2] FOREIGN KEY ([Translation_Laanguage_ID]) REFERENCES [dbo].[Translation_Language] ([TransLation_Language_ID]) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT [fkey1] FOREIGN KEY ([Surah_ID]) REFERENCES [dbo].[Surah] ([Surah_ID]) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT [fkey0] FOREIGN KEY ([Juz_ID]) REFERENCES [dbo].[Juz] ([Juz_ID])
);

New field is added to table but it contains Null. any help!

Upvotes: 0

Views: 2227

Answers (2)

StuartLC
StuartLC

Reputation: 107237

This behavior depends on the way you insert data. If you explicitly insert NULL into the column, it will take NULL, not the default. Omit the Book_ID column out totally during an insert if you want it to take on the default (or, you can also use the keyword DEFAULT as a placeholder).

e.g. This will still insert NULL:

INSERT INTO [dbo].[Ayyat_Translation_Language_old_20131209] 
(
    [Ayat_Translation_Language_ID], ,
    [Translation_Laanguage_ID]     ,
    [Juz_ID]                       ,
    [Surah_ID]                     ,
    [Ayat_Description]             ,
    [Ayat_No]                      ,
    [Book_ID]                      
)
VALUES (1, 2, 3, 4, 'Foo', 5, NULL);

Whereas this will assume the default:

INSERT INTO [dbo].[Ayyat_Translation_Language_old_20131209] 
(
    [Ayat_Translation_Language_ID], ,
    [Translation_Laanguage_ID]     ,
    [Juz_ID]                       ,
    [Surah_ID]                     ,
    [Ayat_Description]             ,
    [Ayat_No]
    -- BOOK_ID is omitted, or use DEFAULT
)
VALUES (1, 2, 3, 4, 'Foo', 5);

Upvotes: 1

BAReese
BAReese

Reputation: 501

You need to now alter the table to not accept NULL for Book_ID

ALTER TABLE [dbo].[Ayyat_Translation_Language_old_20131209]
    ALTER COLUMN [Book_ID] INT NOT NULL DEFAULT 1

If that gives errors, you may need to update the existing records first:

UPDATE [dbo].[Ayyat_Translation_Language_old_20131209]
    SET [Book_ID] = 1

Upvotes: 0

Related Questions