Help with adding a column to existing table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with adding a column to existing table

Author

Topic

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2012-10-31 : 19:29:12

I am trying to use the ALTER command to add a column to an existing table then add a FK constraint to that column but keep getting an error.

Error:

quote:
Msg 8183, Level 16, State 1, Line 2
Only UNIQUE or PRIMARY KEY constraints can be created on computed columns, while CHECK, FOREIGN KEY, and NOT NULL constraints require that computed columns be persisted.

CODE:

ALTER TABLE dbo.tblCharacterInfo
ADD SectionID AS smallint NOT NULL

ALTER TABLE dbo.tblCharacterInfo
ADD CONSTRAINT FK_CharacterInfo_SectionInfo FOREIGN KEY (SectionID) REFERENCES dbo.tblSectionID (SectionID)

I get the error wither I run both alters at once or only the column alter by it's self so i am not sure what is wrong. Here is the target table create code:

CREATE TABLE dbo.tblCharacterInfo
        (
            CharacterInfoID int IDENTITY(0,1) NOT NULL,
            CharacterID int NOT NULL,
            AttributeName varchar(250) NOT NULL,
            AttributeValue varchar(MAX) NOT NULL,
            CONSTRAINT PK_CharacterInfo_CharacterInfoID PRIMARY KEY CLUSTERED (CharacterInfoID ASC),
            CONSTRAINT FK_ChracterInfo_Characters FOREIGN KEY (CharacterID) REFERENCES dbo.tblCharacters (CharacterID)
        )

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-10-31 : 21:36:49

Is there any data in the table? If so the column add should fail as it is not null without a default.
Can you post the same for tblSectionID.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2012-10-31 : 22:58:01

tblSectionID was a type-o it should have been tblSectionInfo and here is thta code (tblSectionInfo contains lots of data):

CREATE TABLE dbo.tblSectionInfo
        (
            SectionID smallint IDENTITY(0,1) NOT NULL,
            SectionTitle varchar(250) NOT NULL,
            ShortName varchar(30) NOT NULL,
            RouteName varchar(15) NOT NULL,
            Synopsis varchar(MAX) NOT NULL,
            SectionType varchar(15) NOT NULL,
            LogoFileID int NULL,
            CONSTRAINT PK_SectionInfo_SectionID PRIMARY KEY CLUSTERED (SectionID ASC),
            CONSTRAINT UC_SectionInfo_RouteName UNIQUE NONCLUSTERED (RouteName ASC),
            CONSTRAINT FK_SectionInfo_Files FOREIGN KEY (LogoFileID) REFERENCES dbo.tblFiles (FileID)
        )

tblCharacterInfo contains no data in it (confirmed in Management Studio) and it never has.

Modifying the first ALTER statment so SectionID could be NULL causes the same error.

If I use code:

ALTER TABLE dbo.tblCharacterInfo
ADD SectionID AS varchar(MAX) NULL

I get error:

quote:
Msg 195, Level 15, State 10, Line 2
'varchar' is not a recognized built-in function name.

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-11-01 : 10:49:00

Hah! Remove the "as"

ALTER TABLE dbo.tblCharacterInfo
ADD SectionID smallint NOT NULL

ALTER TABLE dbo.tblCharacterInfo
ADD SectionID varchar(MAX) NULL

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2012-11-01 : 19:16:28

Yes, I descovered that this morning real quick before having to go to work, removed the "AS" and it works fine. Thanks

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources