SQL 2008 create table method and KEY...

Question

Hi again,

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

Answer 1

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.

Answer 2

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);

Answer 3

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) 
);