create table error - SQL Server Forums

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 Administration

create table error

Author

Topic

dtrivedi
Posting Yak Master

153 Posts

Posted - 2011-04-14 : 14:05:50

I am trying to create a table. I am getting this error
Msg 102, Level 15, State 1, Line 28
Incorrect syntax near 'CLUSTERED'.

CREATE TABLE [dbo].[Events](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](100) NOT NULL,
[HostingSchoolId] [int] NOT NULL,
[HostingTeacherId] [int] NULL,
[Location] [nvarchar](200) NULL,
[EventTypeId] [int] NOT NULL,
[StartLow] [datetime] NOT NULL,
[StartHigh] [datetime] NOT NULL,
[EndLow] [datetime] NOT NULL,
[EndHigh] [datetime] NOT NULL,
[Supervisor] [nvarchar](50) NULL,
[StaffNeeded] [int] NULL,
[StaffNames] [nvarchar](500) NULL,
[Note] [nvarchar](500) NULL,
[RentalMeetingTypeId] [int] NOT NULL,
[RentalFulfillmentTypeId] [int] NOT NULL,
[MouthpieceTesting] [bit] NOT NULL,
[BandProgramAttending] [bit] NOT NULL,
[OrchestraProgramAttending] [bit] NOT NULL,
[EstimatedBandRentals] [int] NULL,
[EstimatedOrchestraRentals] [int] NULL,
[IsHidden] [bit] NOT NULL,
[create_admin_userid] [int] NOT NULL,
[createDate] [datetime] NOT NULL,
[last_admin_userid] [int] NOT NULL,
[lastModDate] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED

CREATE TABLE [dbo].[EventTypes](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](30) NOT NULL,
[create_admin_userid] [int] NOT NULL,
[createDate] [datetime] NOT NULL,
[last_admin_userid] [int] NOT NULL,
[lastModDate] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED

CREATE TABLE [dbo].[RentalFulfillmentTypes](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](128) NULL,
[create_admin_userid] [int] NOT NULL,
[createDate] [datetime] NOT NULL,
[last_admin_userid] [int] NOT NULL,
[lastModDate] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED

CREATE TABLE [dbo].[RentalMeetingTypes](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](30) NOT NULL,
[create_admin_userid] [int] NOT NULL,
[createDate] [datetime] NOT NULL,
[last_admin_userid] [int] NOT NULL,
[lastModDate] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED

CREATE TABLE [dbo].[SchoolEvents](
[EventId] [int] NOT NULL,
[SchoolId] [int] NOT NULL,
PRIMARY KEY CLUSTERED

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-14 : 14:08:30

You need a closing bracket after each create statement.

CREATE TABLE [dbo].[Events](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](100) NOT NULL,
[HostingSchoolId] [int] NOT NULL,
[HostingTeacherId] [int] NULL,
[Location] [nvarchar](200) NULL,
[EventTypeId] [int] NOT NULL,
[StartLow] [datetime] NOT NULL,
[StartHigh] [datetime] NOT NULL,
[EndLow] [datetime] NOT NULL,
[EndHigh] [datetime] NOT NULL,
[Supervisor] [nvarchar](50) NULL,
[StaffNeeded] [int] NULL,
[StaffNames] [nvarchar](500) NULL,
[Note] [nvarchar](500) NULL,
[RentalMeetingTypeId] [int] NOT NULL,
[RentalFulfillmentTypeId] [int] NOT NULL,
[MouthpieceTesting] [bit] NOT NULL,
[BandProgramAttending] [bit] NOT NULL,
[OrchestraProgramAttending] [bit] NOT NULL,
[EstimatedBandRentals] [int] NULL,
[EstimatedOrchestraRentals] [int] NULL,
[IsHidden] [bit] NOT NULL,
[create_admin_userid] [int] NOT NULL,
[createDate] [datetime] NOT NULL,
[last_admin_userid] [int] NOT NULL,
[lastModDate] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED );

CREATE TABLE [dbo].[EventTypes](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](30) NOT NULL,
[create_admin_userid] [int] NOT NULL,
[createDate] [datetime] NOT NULL,
[last_admin_userid] [int] NOT NULL,
[lastModDate] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED  );

CREATE TABLE [dbo].[RentalFulfillmentTypes](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](128) NULL,
[create_admin_userid] [int] NOT NULL,
[createDate] [datetime] NOT NULL,
[last_admin_userid] [int] NOT NULL,
[lastModDate] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED );

CREATE TABLE [dbo].[RentalMeetingTypes](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](30) NOT NULL,
[create_admin_userid] [int] NOT NULL,
[createDate] [datetime] NOT NULL,
[last_admin_userid] [int] NOT NULL,
[lastModDate] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED  );

CREATE TABLE [dbo].[SchoolEvents](
[EventId] [int] NOT NULL,
[SchoolId] [int] NOT NULL,
PRIMARY KEY CLUSTERED );

dtrivedi
Posting Yak Master

153 Posts

Posted - 2011-04-14 : 14:26:35

thank you now how about this?

Msg 8135, Level 16, State 0, Line 1
Table level constraint does not specify column list, table 'dbo.Events'.
Msg 8135, Level 16, State 0, Line 30
Table level constraint does not specify column list, table 'dbo.EventTypes'.
Msg 8135, Level 16, State 0, Line 39
Table level constraint does not specify column list, table 'dbo.RentalFulfillmentTypes'.
Msg 8135, Level 16, State 0, Line 48
Table level constraint does not specify column list, table 'dbo.RentalMeetingTypes'.
Msg 8135, Level 16, State 0, Line 57
Table level constraint does not specify column list, table 'dbo.SchoolEvents'.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-14 : 14:43:25

My bad. You also need to specify the columns for the primary key. For example, in the Events table, if you want ID and Name in ascending and descending order respectively to be the primary key, you would specify it as


....
....
[lastModDate] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED  (Id ASC, [Name] DESC));

I am only showing an example. You will need to decide which column(s) make up your primary key. Each column that is part of the primary key has to be non-nullable.

Jahanzaib
Posting Yak Master

115 Posts

Posted - 2011-04-15 : 14:33:42

PRIMARY KEY by default create clustered key on a particular column,define as PRIMARY KEY on a required column

CREATE TABLE [dbo].[Events43](
[Id] [int] PRIMARY KEY IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](100) NOT NULL,
[HostingSchoolId] [int] NOT NULL,
[HostingTeacherId] [int] NULL,
[Location] [nvarchar](200) NULL,
[EventTypeId] [int] NOT NULL,
[StartLow] [datetime] NOT NULL,
[StartHigh] [datetime] NOT NULL,
[EndLow] [datetime] NOT NULL,
[EndHigh] [datetime] NOT NULL,
[Supervisor] [nvarchar](50) NULL,
[StaffNeeded] [int] NULL,
[StaffNames] [nvarchar](500) NULL,
[Note] [nvarchar](500) NULL,
[RentalMeetingTypeId] [int] NOT NULL,
[RentalFulfillmentTypeId] [int] NOT NULL,
[MouthpieceTesting] [bit] NOT NULL,
[BandProgramAttending] [bit] NOT NULL,
[OrchestraProgramAttending] [bit] NOT NULL,
[EstimatedBandRentals] [int] NULL,
[EstimatedOrchestraRentals] [int] NULL,
[IsHidden] [bit] NOT NULL,
[create_admin_userid] [int] NOT NULL,
[createDate] [datetime] NOT NULL,
[last_admin_userid] [int] NOT NULL,
[lastModDate] [datetime] NOT NULL);

Regards,
Syed Jahanzaib Bin Hassan
MCTS,MCITP,OCA,OCP,OCE,SCJP,IBMCDBA

Bigburl
Starting Member

2 Posts

Posted - 2011-05-13 : 18:19:59

I am taking basic SQL online at University of Phoenix and I seem to be having a problem with my table can anyone help

CREATE TABLE Job_Title
(
Job_Title_ID int NOT NULL, PRIMARY KEY,
EmployerInformationReport varchar(40) NOT NULL,
Job_title varchar(40) NOT NULL,
Job_Discription varchar(100) NOT NULL,
Exempt_nonexempt varchar(15) NOT NULL
);

CREATE TABLE Employee
(
EmployeeID int NOT NULL, PRIMARY KEY,
Job_Title_ID int NOT NULL
FOREIGN KEY REFERENCES Job_Title(Job_Title_ID),
LastName varchar(40) NOT NULL,
FirstName varchar(40) NOT NULL,
StreetAddress varchar(40) NOT NULL,
City varchar(20) NOT NULL,
State char(2) NOT NULL,
TelephoneNumber char(10) NOT NULL,
HireDate date NOT NULL CONSTRAINT DF_HireDate DEFAULT getdate(),
Salary money NOT NULL CONSTRAINT DF_Salary DEFAULT 20,
Age char(3) NOT NULL CONSTRAINT DF_Age DEFAULT 18
);

Msg 8135, Level 16, State 0, Line 1
Table level constraint does not specify column list, table 'Job_Title'.
Msg 8135, Level 16, State 0, Line 10
Table level constraint does not specify column list, table 'Employee'.

Bigburl
Starting Member

2 Posts

Posted - 2011-05-13 : 18:37:01

never mind I see that in my code I put as , after the NOT NULL statment before the PRIMARY KEY statement. I just didn't catch it the first time.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-13 : 18:38:23

Remove the comma just before the keyword PRIMARY KEY, as in:


....
Job_Title_ID	int	NOT NULL PRIMARY KEY,
....

BTW, people who run/own this forum advise that if someone wants to ask a new question, they start a new post rather than reply to an existing thread. Part of the reason is that if your question looks like a response to an old post, people may not read it. The only reason I happened to read it is because I had responded earlier in this thread.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources