Clustered Primary Key and Foreign Key: T-SQL

I am having a little trouble getting this to work right, but have come a ways since I started this...
...other tables created first and with no problems.... then these two with the last table being the problem
I need to set one foreign key in the second table referencing the first table.
But, the primary key is clustered with the two foreign keys and I get the error...
There are no primary or candidate keys in the referenced table 'courseScores'
that match the referencing column list in the foreign key 'FK_course'.

CREATE TABLE dbo.courseScores (
courseId varchar(20) NOT NULL
CONSTRAINT FK_courseId_courseStructure2 FOREIGN KEY (courseId)
REFERENCES courseStructure (courseId),

studentId varchar(20) NOT NULL
CONSTRAINT FK_studentId_students2 FOREIGN KEY (studentId)
REFERENCES students (studentId),

CONSTRAINT PK_courseScore PRIMARY KEY CLUSTERED (courseId, studentId)

)
CREATE TABLE dbo.objScores ( tmp int IDENTITY(1,1) PRIMARY KEY,
objective varchar(50) NOT NULL,

courseIdvarchar(20)NOT NULL
CONSTRAINT FK_course FOREIGN KEY (courseId)
REFERENCES courseScores (courseId)
)

Once I get it working, then the tmp will be gone and then set 3 foreign keys as the clustered primary, fyi.
Not sure how to reference half a primary key?
Any help is greatly appreciated....
Thanks all,
Zath

A primary key guarantees uniqueness of rows, thereby also creating aguarantee that any foreign key referencing that primary key will bereferencing exactly one row. Your primary key is guaranteeinguniqueness of the combination of columns (courseid, studentid) -- butno such guarantee is made for ONLY courseid. If that column isindeed unique, apply a UNIQUE constraint to it and your foreign keywill work. Otherwise, you are going to have to propagate thestudentid column into the objScores table to get the full reference.
By the way, what do these tables represent? Perhaps we shouldback up before figuring out how to create these keys and see if there'sa better way to model the data.

|||

Thanks! Setting the previous field to UNIQUE did the trick!
Worked on this all day yesterday and databases are not my forte. I'll stick to code thank you
But, if you want to see the complete working version and have suggestions, I'm open...
CREATE TABLE dbo.courseStructure (courseID varchar(20) NOT NULL PRIMARY KEY,
courseName varchar(256) NOT NULL
)

CREATE TABLE dbo.objStructure (objID varchar(20) NOT NULL PRIMARY KEY,
objName varchar(256) NOT NULL,
courseID varchar(20) NOT NULL,
CONSTRAINT FK_courseID_courseStructure FOREIGN KEY (courseID)
REFERENCES courseStructure (courseID)
)

CREATE TABLE dbo.students (studentId varchar(20) NOT NULL PRIMARY KEY
CONSTRAINT FK_studentId_students FOREIGN KEY (studentId)
REFERENCES userAccount (userID)
)

CREATE TABLE dbo.courseScores (
courseId varchar(20) NOT NULL UNIQUE
CONSTRAINT FK_courseId_courseStructure2 FOREIGN KEY (courseId)
REFERENCES courseStructure (courseId),

studentId varchar(20) NOT NULL
CONSTRAINT FK_studentId_students2 FOREIGN KEY (studentId)
REFERENCES students (studentId),

lessonLocation varchar(20),
lessonStatus varchar(20),
lessonScoreRaw varchar(20),
lessonScoreMin varchar(20),
lessonScoreMax varchar(20),
startDate datetime,
completeDate datetime,
CONSTRAINT PK_courseScore PRIMARY KEY CLUSTERED (courseId, studentId)

)

CREATE TABLE dbo.objScores (
objective varchar(50) NOT NULL,

objID varchar(20) NOT NULL
CONSTRAINT FK_objId_objstructure FOREIGN KEY (objID)
REFERENCES objStructure (objID),

studentId varchar(20) NOT NULL
CONSTRAINT FK_studentId_students3 FOREIGN KEY (studentId)
REFERENCES students (studentId),

courseId varchar(20) NOT NULL
CONSTRAINT FK_course FOREIGN KEY (courseId)
REFERENCES courseScores (courseId),

objStatus varchar(20),
objScoreRaw varchar(20),
objScoreMin varchar(20),
objScoreMax varchar(20)

CONSTRAINT PK_objScores PRIMARY KEY CLUSTERED (objID, studentId, courseId)
)
Zath

|||What do these tables represent? What is an 'objScore'?

|||Doing a SCORM and LMS thing and for testing, we are using sql server db.
It is a testing site.
A student may enroll in multiple courses.
Each course has multiple objectives and scores...
There are many other tables in the db, but for this part, only the useraccount table needed to be accessed for the student ID.
But, it seems to be running ok for now.....
But always open to suggestions to improve it.
Zath