plz point error in table alter or creation

Posted by Sushant under Sql Server on 6/15/2013 | Points: 10 | Views : 1025 | Status : [Member] | Replies : 6
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


Responses

Posted by: Satyapriyanayak on: 6/15/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
http://www.w3schools.com/sql/sql_alter.asp

If this post helps you mark it as answer
Thanks

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

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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Login to post response