some error in table definition

if exists(select * from dbo.sysobjects where id =3D
object_id(N'BBSItems')
and OBJECTPROPERTY(id,N'IsUserTable')=3D1)
drop table BBSItems
GO

CREATE TABLE BBSItems(
ITEM_ID int IDENTITY(1,1) NOT NULL,
ITEM_TITLE nvarchar(30) COLLATE Chinese_PRC_CS_AS NOT NULL,
ITEM_CONTENT nvarchar(3000) COLLATE Chinese_PRC_CS_AS NOT NULL,
COMPOSE_DATE smalldatetime NOT NULL,
MODIFY_DATE smalldatetime NULL,
IS_ORIGIONAL bit NOT NULL,
CONSTRAINT DF_BBSItem_IS_ORIGIONAL DEFAULT(0),
AUTHOR nvarchar(15) COLLATE Chinese_PRC_CS_AS NOT NULL,
FARTHER_ID int NULL,
REPLYD_TIMES smallint NOT NULL
CONSTRAINT DF_BBSItem_REPLYD_TIMES DEFAULT(0),
BROWSED_TIMES smallint NOT NULL
CONSTRAINT DF_BBSItem_BROWSED_TIMES DEFAULT(0),
LATEST_REPLICATION_ID int NULL,
CONSTRAINT PK_BBSItem PRIMARY KEY CLUSTERD
(
ITEM_ID
) ON PRIMARY,
CONSTRAINT FK_BBSItem_BBSItem1 FOREIGN KEY
(
LATEST_REPLICATION_ID
)REFERENCES BBSItems(
ITEM_ID),
CONSTRAINT FK_BBSItem_UserInfo FOREIGN KEY
(
AUTHOR
)REFERENCES useinfo(nickname)
)
ON PRIMARY
GO

row 9:ITEM_CONTENT nvarchar(3000) COLLATE Chinese_PRC_CS_AS NOT NULL,
constraint 'TABLE' definition wrong in gramma¡£

can you help me?

Re: some error in table definition

On Tue, 30 Oct 2007 10:35:08 -0000, cong wrote:

>IS_ORIGIONAL bit NOT NULL,
>CONSTRAINT DF_BBSItem_IS_ORIGIONAL DEFAULT(0),
>AUTHOR nvarchar(15) COLLATE Chinese_PRC_CS_AS NOT NULL,

>row 9:ITEM_CONTENT nvarchar(3000) COLLATE Chinese_PRC_CS_AS NOT NULL,
>constraint 'TABLE' definition wrong in gramma??

A constraint that comes after a comma is a table constraint. Table
constraints must be specified AFTER all the columns. Since this is a
DEFAULT constraint that must be associated with a column remove the
comma and make it a column constraint.

>BROWSED_TIMES smallint NOT NULL
>CONSTRAINT DF_BBSItem_BROWSED_TIMES DEFAULT(0),
>LATEST_REPLICATION_ID int NULL

When you get past that you will find that CLUSTERD is not the correct
spelling of the keyword CLUSTERED. And you might prefer to spell
ORIGIONAL as ORIGINAL, and if REPLYD is supposed to be the past tense
of REPLY then you might want REPLYED.

Roy Harvey
Beacon Falls, CT

Re: some error in table definition

Roy Harvey (SQL Server MVP) (roy_harvey@snet.net) writes:
> When you get past that you will find that CLUSTERD is not the correct
> spelling of the keyword CLUSTERED. And you might prefer to spell
> ORIGIONAL as ORIGINAL, and if REPLYD is supposed to be the past tense
> of REPLY then you might want REPLYED.

Any particular reason he might want precisely that spelling? Do you
happen to know that REPLYED is preferred over REPLIED in China? :-)

There is also a FARTHER_ID that maybe should be FATHER_ID.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: some error in table definition

Erland Sommarskog wrote:

> There is also a FARTHER_ID that maybe should be FATHER_ID.

Or PARENT_ID, unless China also has some relevant stance on
figurative gender that I'm unaware of. :)

Re: some error in table definition

On Tue, 30 Oct 2007 22:13:05 +0000 (UTC), Erland Sommarskog
wrote:

>> When you get past that you will find that CLUSTERD is not the correct
>> spelling of the keyword CLUSTERED. And you might prefer to spell
>> ORIGIONAL as ORIGINAL, and if REPLYD is supposed to be the past tense
>> of REPLY then you might want REPLYED.

>Any particular reason he might want precisely that spelling? Do you
>happen to know that REPLYED is preferred over REPLIED in China? :-)

I have no idea about what is preferred, except in English, but it
appeared that the names might be intended to be in English. Which is
why I said "might" and "perhaps".

I once wrote an entire forecasting system, misspelling forecast as
forcast. I ended up fixing report headings when the users noticed,
but left all the variables and field names alone. The poor guy who
took over when I left knew how to spell it though and it drove him
nuts.

Roy Harvey
Beacon Falls, CT

Re: some error in table definition

On 10ÔÂ31ÈÕ, ÉÏÎç11ʱ00·Ö, "Roy Harvey (SQL Server =
MVP)"
wrote:
> On Tue, 30 Oct 2007 22:13:05 +0000 (UTC), Erland Sommarskog
>
> wrote:
> >> When you get past that you will find that CLUSTERD is not the correct
> >> spelling of the keyword CLUSTERED. And you might prefer to spell
> >> ORIGIONAL as ORIGINAL, and if REPLYD is supposed to be the past tense
> >> of REPLY then you might want REPLYED.
> >Any particular reason he might want precisely that spelling? Do you
> >happen to know that REPLYED is preferred over REPLIED in China? :-)
>
> I have no idea about what is preferred, except in English, but it
> appeared that the names might be intended to be in English. Which is
> why I said "might" and "perhaps".
>
> I once wrote an entire forecasting system, misspelling forecast as
> forcast. I ended up fixing report headings when the users noticed,
> but left all the variables and field names alone. The poor guy who
> took over when I left knew how to spell it though and it drove him
> nuts.
>
> Roy Harvey
> Beacon Falls, CT

if exists(select * from dbo.sysobjects where id =3D
object_id(N'BBSItems')
and OBJECTPROPERTY(id,N'IsUserTable')=3D1)
drop table BBSItems
GO

CREATE TABLE BBSItems(
ITEM_ID int IDENTITY(1,1) NOT NULL,
ITEM_TITLE nvarchar(30) COLLATE Chinese_PRC_CS_AS NOT NULL,
ITEM_CONTENT nvarchar(3000) COLLATE Chinese_PRC_CS_AS NOT NULL,
COMPOSE_DATE smalldatetime NOT NULL,
MODIFY_DATE smalldatetime NULL,
IS_ORIGIONAL bit NOT NULL
CONSTRAINT DF_BBSItem_IS_ORIGIONAL DEFAULT(0),
AUTHOR nvarchar(15) COLLATE Chinese_PRC_CS_AS NOT NULL,
FATHER_ID int NULL,
REPLYD_TIMES smallint NOT NULL
CONSTRAINT DF_BBSItem_REPLYD_TIMES DEFAULT(0),
BROWSED_TIMES smallint NOT NULL
CONSTRAINT DF_BBSItem_BROWSED_TIMES DEFAULT(0),
LATEST_REPLICATION_ID int NULL,
CONSTRAINT PK_BBSItems PRIMARY KEY CLUSTERED
(
ITEM_ID
) ON PRIMARY,
CONSTRAINT FK_BBSItem_BBSItem1 FOREIGN KEY
(
LATEST_REPLICATION_ID
)REFERENCES BBSItems(
ITEM_ID),
CONSTRAINT FK_BBSItem_UserInfo FOREIGN KEY
(
AUTHOR
)REFERENCES userinfo(nickname)
)
ON PRIMARY
GO

wrong in row 20 nearPRIMARY

Re: some error in table definition

On 10ÔÂ31ÈÕ, ÉÏÎç11ʱ00·Ö, "Roy Harvey (SQL Server =
MVP)"
wrote:
> On Tue, 30 Oct 2007 22:13:05 +0000 (UTC), Erland Sommarskog
>
> wrote:
> >> When you get past that you will find that CLUSTERD is not the correct
> >> spelling of the keyword CLUSTERED. And you might prefer to spell
> >> ORIGIONAL as ORIGINAL, and if REPLYD is supposed to be the past tense
> >> of REPLY then you might want REPLYED.
> >Any particular reason he might want precisely that spelling? Do you
> >happen to know that REPLYED is preferred over REPLIED in China? :-)
>
> I have no idea about what is preferred, except in English, but it
> appeared that the names might be intended to be in English. Which is
> why I said "might" and "perhaps".
>
> I once wrote an entire forecasting system, misspelling forecast as
> forcast. I ended up fixing report headings when the users noticed,
> but left all the variables and field names alone. The poor guy who
> took over when I left knew how to spell it though and it drove him
> nuts.
>
> Roy Harvey
> Beacon Falls, CT

if exists(select * from dbo.sysobjects where id =3D
object_id(N'BBSItems')
and OBJECTPROPERTY(id,N'IsUserTable')=3D1)
drop table BBSItems
GO

CREATE TABLE BBSItems(
ITEM_ID int IDENTITY(1,1) NOT NULL,
ITEM_TITLE nvarchar(30) COLLATE Chinese_PRC_CS_AS NOT NULL,
ITEM_CONTENT nvarchar(3000) COLLATE Chinese_PRC_CS_AS NOT NULL,
COMPOSE_DATE smalldatetime NOT NULL,
MODIFY_DATE smalldatetime NULL,
IS_ORIGIONAL bit NOT NULL
CONSTRAINT DF_BBSItem_IS_ORIGIONAL DEFAULT(0),
AUTHOR nvarchar(15) COLLATE Chinese_PRC_CS_AS NOT NULL,
FATHER_ID int NULL,
REPLYD_TIMES smallint NOT NULL
CONSTRAINT DF_BBSItem_REPLYD_TIMES DEFAULT(0),
BROWSED_TIMES smallint NOT NULL
CONSTRAINT DF_BBSItem_BROWSED_TIMES DEFAULT(0),
LATEST_REPLICATION_ID int NULL,
CONSTRAINT PK_BBSItems PRIMARY KEY CLUSTERED
(
ITEM_ID
) ON PRIMARY,
CONSTRAINT FK_BBSItem_BBSItem1 FOREIGN KEY
(
LATEST_REPLICATION_ID
)REFERENCES BBSItems(
ITEM_ID),
CONSTRAINT FK_BBSItem_UserInfo FOREIGN KEY
(
AUTHOR
)REFERENCES userinfo(nickname)
)
ON PRIMARY
GO

wrong in row 20 near keyword primary

Re: some error in table definition

Put PRIMARY in [square brackets].

Roy Harvey
Beacon Falls, CT

On Wed, 31 Oct 2007 07:27:56 -0700, cong wrote:

>On 10??31??, ????11??00??, "Roy Harvey (SQL Server MVP)"
> wrote:
>> On Tue, 30 Oct 2007 22:13:05 +0000 (UTC), Erland Sommarskog
>>
>> wrote:
>> >> When you get past that you will find that CLUSTERD is not the correct
>> >> spelling of the keyword CLUSTERED. And you might prefer to spell
>> >> ORIGIONAL as ORIGINAL, and if REPLYD is supposed to be the past tense
>> >> of REPLY then you might want REPLYED.
>> >Any particular reason he might want precisely that spelling? Do you
>> >happen to know that REPLYED is preferred over REPLIED in China? :-)
>>
>> I have no idea about what is preferred, except in English, but it
>> appeared that the names might be intended to be in English. Which is
>> why I said "might" and "perhaps".
>>
>> I once wrote an entire forecasting system, misspelling forecast as
>> forcast. I ended up fixing report headings when the users noticed,
>> but left all the variables and field names alone. The poor guy who
>> took over when I left knew how to spell it though and it drove him
>> nuts.
>>
>> Roy Harvey
>> Beacon Falls, CT
>
>if exists(select * from dbo.sysobjects where id =
>object_id(N'BBSItems')
>and OBJECTPROPERTY(id,N'IsUserTable')=1)
>drop table BBSItems
>GO
>
>CREATE TABLE BBSItems(
>ITEM_ID int IDENTITY(1,1) NOT NULL,
>ITEM_TITLE nvarchar(30) COLLATE Chinese_PRC_CS_AS NOT NULL,
>ITEM_CONTENT nvarchar(3000) COLLATE Chinese_PRC_CS_AS NOT NULL,
>COMPOSE_DATE smalldatetime NOT NULL,
>MODIFY_DATE smalldatetime NULL,
>IS_ORIGIONAL bit NOT NULL
>CONSTRAINT DF_BBSItem_IS_ORIGIONAL DEFAULT(0),
>AUTHOR nvarchar(15) COLLATE Chinese_PRC_CS_AS NOT NULL,
>FATHER_ID int NULL,
>REPLYD_TIMES smallint NOT NULL
>CONSTRAINT DF_BBSItem_REPLYD_TIMES DEFAULT(0),
>BROWSED_TIMES smallint NOT NULL
>CONSTRAINT DF_BBSItem_BROWSED_TIMES DEFAULT(0),
>LATEST_REPLICATION_ID int NULL,
>CONSTRAINT PK_BBSItems PRIMARY KEY CLUSTERED
>(
>ITEM_ID
>) ON PRIMARY,
>CONSTRAINT FK_BBSItem_BBSItem1 FOREIGN KEY
>(
>LATEST_REPLICATION_ID
>)REFERENCES BBSItems(
>ITEM_ID),
>CONSTRAINT FK_BBSItem_UserInfo FOREIGN KEY
>(
>AUTHOR
>)REFERENCES userinfo(nickname)
>)
>ON PRIMARY
>GO
>
>wrong in row 20 nearPRIMARY

Re: some error in table definition

>> I once wrote an entire forecasting system, misspelling forecast as forcast. I ended up fixing report headings when the users noticed, but left all the variables and field names alone. The poor guy who took over when I left knew how to spell it though and it drove him nuts. <<

LOL! One of the few things I like in Agile Programming is the use of
a bulk text editor with a good regular expression to re-factor
code. With my spelling, I have to!!

Re: some error in table definition

On 10ÔÂ30ÈÕ, ÏÂÎç7ʱ54·Ö, "Roy Harvey (SQL Server M=
VP)"
wrote:
> On Tue, 30 Oct 2007 10:35:08 -0000, cong wrote:
> >IS_ORIGIONAL bit NOT NULL,
> >CONSTRAINT DF_BBSItem_IS_ORIGIONAL DEFAULT(0),
> >AUTHOR nvarchar(15) COLLATE Chinese_PRC_CS_AS NOT NULL,
> >row 9:ITEM_CONTENT nvarchar(3000) COLLATE Chinese_PRC_CS_AS NOT NULL,
> >constraint 'TABLE' definition wrong in gramma??
>
> A constraint that comes after a comma is a table constraint. Table
> constraints must be specified AFTER all the columns. Since this is a
> DEFAULT constraint that must be associated with a column remove the
> comma and make it a column constraint.
>
> >BROWSED_TIMES smallint NOT NULL
> >CONSTRAINT DF_BBSItem_BROWSED_TIMES DEFAULT(0),
> >LATEST_REPLICATION_ID int NULL
>
> When you get past that you will find that CLUSTERD is not the correct
> spelling of the keyword CLUSTERED. And you might prefer to spell
> ORIGIONAL as ORIGINAL, and if REPLYD is supposed to be the past tense
> of REPLY then you might want REPLYED.
>
> Roy Harvey
> Beacon Falls, CT

this is an example in a chinese book about ASP .

thank you for your reply, and I have a new problem. I uesd MYSQL
before,don't familiar with SQL SERVER


if exists(select * from dbo.sysobjects where id =3D
object_id(N'BBSItems')
and OBJECTPROPERTY(id,N'IsUserTable')=3D1)
drop table BBSItems
GO

CREATE TABLE BBSItems(
ITEM_ID int IDENTITY(1,1) NOT NULL,
ITEM_TITLE nvarchar(30) COLLATE Chinese_PRC_CS_AS NOT NULL,
ITEM_CONTENT nvarchar(3000) COLLATE Chinese_PRC_CS_AS NOT NULL,
COMPOSE_DATE smalldatetime NOT NULL,
MODIFY_DATE smalldatetime NULL,
IS_ORIGIONAL bit NOT NULL
CONSTRAINT DF_BBSItem_IS_ORIGIONAL DEFAULT(0),
AUTHOR nvarchar(15) COLLATE Chinese_PRC_CS_AS NOT NULL,
FATHER_ID int NULL,
REPLYD_TIMES smallint NOT NULL
CONSTRAINT DF_BBSItem_REPLYD_TIMES DEFAULT(0),
BROWSED_TIMES smallint NOT NULL
CONSTRAINT DF_BBSItem_BROWSED_TIMES DEFAULT(0),
LATEST_REPLICATION_ID int NULL,

CONSTRAINT PK_BBSItems PRIMARY KEY CLUSTERED
(
ITEM_ID
) ON PRIMARY,
CONSTRAINT FK_BBSItem_BBSItem1 FOREIGN KEY
(
LATEST_REPLICATION_ID
)REFERENCES BBSItems(
ITEM_ID),
CONSTRAINT FK_BBSItem_UserInfo FOREIGN KEY
(
AUTHOR
)REFERENCES ¸öÈËÐÅÏ¢(êdzÆ)
)
ON PRIMARY
)
GO

ROW 21:NEAR KEYWORD "PRIMARY" WRONG IN GRAMMA

THANK YOU!

Re: some error in table definition

cong (franzcai80@gmail.com) writes:
> AUTHOR
> )REFERENCES ????(??)
> )
> ON PRIMARY
> )
> GO
>
> ROW 21:NEAR KEYWORD "PRIMARY" WRONG IN GRAMMA

As Roy said in an earlier post, put PRIMARY in square brackets:

ON [PRIMARY]

This is needed because PRIMARY here is just an identifier for the primary
filegroup. The clause could also be:

ON My_other_filegroup

As long as you don't use multiple filegroups, and it may take long before
you come around to that, you can skip the ON clause althogether, as it
just adds noise.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx