SQL 2008 create table method and KEY

Question

I've got a script that generates a table - only my version of SQL 2008 throws up an error - has the syntax changed? Or how do I fix it manually?

CREATE TABLE ScoHistory (
    CourseID varchar (255) NOT NULL ,
    SessionID int NOT NULL ,
    ScoID varchar (255) NOT NULL ,
    StudentID varchar (255) NOT NULL ,
    DateRecorded datetime NULL ,
    score_raw varchar (12)  NULL,
    KEY student_course_sess_scohist_idx (StudentID, CourseID, SessionID, ScoID) -- this is the bit it doesn't like! It says incorrect syntax near KEY...
);

Many thanks in advance,

Spud

zinglon · Accepted Answer · 2010-01-17 16:11:06Z

1

You can define a named primary key table constraint like this:

CREATE TABLE ScoHistory (
CourseID varchar (255) NOT NULL,
SessionID int NOT NULL,
ScoID varchar (255) NOT NULL,
StudentID varchar (255) NOT NULL,
DateRecorded datetime NULL,
score_raw varchar (12) NULL,
CONSTRAINT student_course_sess_scohist_idx PRIMARY KEY (StudentID, CourseID, SessionID, ScoID) 
);

answered Jan 17, 2010 at 16:11

zinglon

1,9081 gold badge15 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

treaschf · Accepted Answer · 2010-01-17 14:01:16Z

0

It would work like this, if this is the intended behaviour:

CREATE TABLE ScoHistory ( 
    CourseID varchar (255) NOT NULL , 
    SessionID int NOT NULL , 
    ScoID varchar (255) NOT NULL , 
    StudentID varchar (255) NOT NULL , 
    DateRecorded datetime NULL , 
    score_raw varchar (12)  NULL, 
    PRIMARY KEY (StudentID, CourseID, SessionID, ScoID) 
);

It you would only like to create a non-unique index on the table, create the index using the CREATE INDEX statement.

answered Jan 17, 2010 at 14:01

treaschf

5,9641 gold badge28 silver badges24 bronze badges

1 Comment

Spudhead Over a year ago

Okay - so what happens to 'student_course_sess_scohist_idx'? Have I missed something? : *

Damir Sudarevic · Accepted Answer · 2010-01-17 15:21:44Z

0

CREATE TABLE ScoHistory
  ( 
   CourseID varchar(255) NOT NULL
  ,SessionID int NOT NULL
  ,ScoID varchar(255) NOT NULL
  ,StudentID varchar(255) NOT NULL
  ,DateRecorded datetime NULL
  ,score_raw varchar(12) NULL,
  ) ;

ALTER TABLE dbo.ScoHistory ADD
CONSTRAINT PK_ScoHistory PRIMARY KEY (StudentID, CourseID, SessionID, ScoID);

answered Jan 17, 2010 at 15:21

Damir Sudarevic

22.2k3 gold badges50 silver badges72 bronze badges

4 Comments

Spudhead Over a year ago

Okay - so what happens to 'student_course_sess_scohist_idx'? Have I missed something? : * Will it affect anything if I follow your fix?

Damir Sudarevic Over a year ago

This will create index named PK_ScoHistory. Replace PK_ScoHistory with student_course_sess_scohist_idx if you like that name better. You can see index and key names in MSSMS (management studio) under Tables/dbo.ScoHistory/Keys and Tables/dbo.ScoHistory/Indexes

Damir Sudarevic Over a year ago

If you have code for database maintenance that rebuilds, reorganizes, drops or re-creates indexes (ALTER INDEX ...)than use your existing names. If not, than it is not that important, but it is a good thing to have some kind of recognizable index and constraint names when you get an error.

Spudhead Over a year ago

Okay - I'll give your fix a go and let you know! I'm still confused to be sure... Many thanks!

Collectives™ on Stack Overflow

SQL 2008 create table method and KEY

3 Answers 3

Comments

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related