plz point error in table alter or creation

Posted by Sushant under Sql Server on 6/15/2013 | Points: 10 | Views : 2393 | Status : [Member] | Replies : 5

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

hi all
can any one point
what is error in following
alter TABLE [dbo].[AreaMaster](
[AreaID] [int] NOT NULL primary key,
[LowerLimit] [int] NOT NULL,
[UpperLimit] [int] NOT NULL,
[PostFix] [varchar](50) NULL,
[AreaCaption] [varchar](100) NULL,
[DispOrder] [int] NOT NULL
)
error:----------------------------

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '('.

Sushat

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Santosh.Choudhury on: 6/15/2013 [Member] Starter | Points: 25

0	You write the syntax properly.What ever you want to you mention after table name such as modify,add. Sushant, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Kirthiga on: 6/17/2013 [Member] Starter | Points: 25

0	Hi Sushant, At first Syntax of Alter table for adding column is Alter table [TableName] add [ColumnName] datatype That's why it is showing error like Incorrect syntax near '(' After changing syntax we will get Alter TABLE [dbo].[AreaMaster] add [AreaID] [int] NOT NULL primary key, [LowerLimit] [int] NOT NULL, [UpperLimit] [int] NOT NULL, [PostFix] [varchar](50) NULL, [AreaCaption] [varchar](100) NULL, [DispOrder] [int] NOT NULL But again Error occurs because ALTER TABLE only allows columns to be added that can contain NULL So we have to use default Statement to solve this error. We cant create primary key in alter table while adding a column. Hence Primary key can be created after adding a column. Alter table [dbo].[AreaMaster] add [AreaID] [int] identity , [LowerLimit] [int] NOT NULL default 0, [UpperLimit] [int] NOT NULL default 0, [PostFix] [varchar](50) NULL, [AreaCaption] [varchar](100) NULL, [DispOrder] [int] NOT NULL default 0 Alter table [dbo].[AreaMaster] add primary key([AreaID]) And finally, hope you get your required result Sushant, if this helps please login to Mark As Answer. \| Alert Moderator

Hi Sushant,

At first Syntax of Alter table for adding column is

Alter table [TableName] add [ColumnName] datatype

That's why it is showing error like
Incorrect syntax near '('

After changing syntax we will get

Alter TABLE [dbo].[AreaMaster] add

[AreaID] [int] NOT NULL primary key, 

[LowerLimit] [int] NOT NULL, 

[UpperLimit] [int] NOT NULL, 

[PostFix] [varchar](50) NULL, 

[AreaCaption] [varchar](100) NULL, 

[DispOrder] [int] NOT NULL

But again Error occurs because ALTER TABLE only allows columns to be added that can contain NULL
So we have to use default Statement to solve this error. We cant create primary key in alter table while adding a column. Hence Primary key can be created after adding a column.

Alter table [dbo].[AreaMaster] add 

[AreaID]	[int] identity ,

[LowerLimit]	[int] NOT NULL default 0, 

[UpperLimit]	[int] NOT NULL default 0, 

[PostFix]	[varchar](50)  NULL, 

[AreaCaption]	[varchar](100) NULL, 

[DispOrder]	[int] NOT NULL default 0



Alter table [dbo].[AreaMaster] add primary key([AreaID])

And finally, hope you get your required result

Sushant, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Saranya Boopathi on: 6/17/2013 [Member] Starter | Points: 25

0	For Adding New Column in Existing table: Alter TABLE [dbo].[AreaMaster1] add [AreaID] [int] NOT NULL primary key, [LowerLimit] [int] NOT NULL, [UpperLimit] [int] NOT NULL, [PostFix] [varchar](50) NULL, [AreaCaption] [varchar](100) NULL, [DispOrder] [int] NOT NULL If you Satisfy with this answer...Then,Mark As Answer.. Saranya Boopathi Sushant, if this helps please login to Mark As Answer. \| Alert Moderator

For Adding New Column in Existing table:

Alter TABLE [dbo].[AreaMaster1] add 

[AreaID] [int] NOT NULL primary key, 

[LowerLimit] [int] NOT NULL, 

[UpperLimit] [int] NOT NULL, 

[PostFix] [varchar](50) NULL, 

[AreaCaption] [varchar](100) NULL, 

[DispOrder] [int] NOT NULL

If you Satisfy with this answer...Then,Mark As Answer..

Saranya Boopathi

Sushant, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rahmankhan on: 6/17/2013 [Member] Starter | Points: 25

0	Hi ALTER TABLE [TABLENAME] ADD [COLUMNNAMR] DATATYPE and refer www.w3schools.com Sushant, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Allemahesh on: 6/17/2013 [Member] [MVP] Silver | Points: 25

0	You can use the below query:- alter TABLE [dbo].[AreaMaster] [AreaID] [int] NOT NULL primary key, [LowerLimit] [int] NOT NULL, [UpperLimit] [int] NOT NULL, [PostFix] [varchar](50) NULL, [AreaCaption] [varchar](100) NULL, [DispOrder] [int] NOT NULL Sushant, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts