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, 
   
    courseId varchar(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

0
Zath
6/10/2005 1:21:49 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

4 Replies
830 Views

Similar Articles

[PageSpeed] 35


A primary key guarantees uniqueness of rows, thereby also creating a guarantee that any foreign key referencing that primary key will be referencing exactly one row.  Your primary key is guaranteeing uniqueness of the combination of columns (courseid, studentid) -- but no such guarantee is made for ONLY courseid.  If that column is indeed unique, apply a UNIQUE constraint to it and your foreign key will work.  Otherwise, you are going to have to propagate the studentid column into the objScores table to get the full reference.

By the way, what do these tables represent?  Perhaps we should back up before figuring out how to create these keys and see if there's a better way to model the data.



Adam Machanic
SQL Server MVP
0
AdamMachanic
6/10/2005 1:29:42 PM

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 Smile [:)]

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

0
Zath
6/10/2005 1:55:22 PM
What do these tables represent?  What is an 'objScore'?



Adam Machanic
SQL Server MVP
0
AdamMachanic
6/10/2005 4:35:17 PM
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
0
Zath
6/10/2005 5:19:51 PM
Reply:

Similar Artilces:

SQL statement failed: (-194) No primary key value for foreign key
I'm getting this error message sporadically when my remote sites are replicating to my central server. The odd thing is the record that is the primary key value does exist! I'm at a lost...Does any have any idea, why this may be happening? Thanks Tasha Did the record exist at the time the error was reported? I have seen cases before where the Parent record is physically sent to the remote *after* the Child record. This would explain why the error was legitimately reported but when you look at the database later the Parent record now exists. -- -------------...

SQL statement failed, no primary key value for foreign key ... in table ...
Problem: SQL statement failed, no primary key value for foreign key ... in table ... After deleting or changing primary key values in either consolidated or remote database. Question: Are there any triggers that can resolve inserting a record that has a foreign key value with no existing primary key value in primary table? Description: We have a serious problem with the dbremote program. I think it's a general problem that could appear in every database with related tables. Everytime the problem turns up we must create new remote databases from the consolidated datab...

Primary Key Prob with SQL table innerjoin with primary key
Hello Exception Details: System.Data.MissingPrimaryKeyException: Table doesn't have a primary key. I have used Stored Procedure in SQL with innerjoin. I think this Stored Procedure is problem because when I use one table with primary key rather using innerjoin to get data from 2 SQL tables I don't have this Table doesn't have a primary key: CREATE proc dbo._BookLink As select Convert(nvarchar(10),BookID)as BookId, CategoryName,Title, Author, Publisher, PublicationDate, ISBN, Price, BriefDesc, FullDesc, [Image], link, htmlready from dbo.BookCategory inner join dbo.B...

integer primary/foreign key vs. varchar(255) primary/foreign key
Hello. Is there any principal performance (or some other) difference between using the integer primary/foreign key and using the string (varchar(255)) keys in queries that use tables joins? Server is Sybase SQL Anywhere 5.5.04 Yes! The primary key will only be made up of 12 bytes. By using an integer you ensure that your entire column is in the key. -- Jim Egan [TeamPS] Dynamic Data Solutions, Inc. http://www.dyn-data.com Houston, TX *********************************************************** Enterprise Applications in Your F...

Difference between candidate key and primary key in sql server 2000.
hi can any explan  in detailed,the difference between the candidate key and primary key in sql server 2000 A table may have more than one combination of set of columns that could uniquely identify the rows in a table. Each combination is a Candidate key. We pick one of the Candidate keys to be the Primary key.Primary Key dont have NULL values, where as Candidate key column may contain one NULL value record.and By default Primary key is Clustered index.Somnath Mali.NET Developer , Pune INDIA.Please Mark As Answer If my reply helped you. Hi, read this thread : http://www...

Primary key/Foreign key again
Thanks, Nat and David. Under "SQL Anywhere", I get into ISQL to use "ALTER TABLE" command to add my primary key: ALTER TABLE Client ADD PRIMARY KEY (SYSID) I got an error: user 'dba' has the row in 'Client' locked. How can I release the locking condition? Click on another table in Sybase Central. Is suspect this is the issue. Dave Wolf Jaguar Product Team John Chyan wrote: > Thanks, Nat and David. > > Under "SQL Anywhere", I get into ISQL to use "ALTER TABLE" command > to add my primary key: > ...

primary key and foreign Key????
 Hi i have 2 table.. Table1UID         Username1               AB2               CD3               DD Table 2ITEM       Price          UIDPOO1      20.00          1POO2      10.00          2POO3       15.00         1 Now i am letting the Admin to delete the user in table1But since UID 1 and 2 are ...

Keys, keys,keys...
Just installed PGP 6.5.8 and all is running fine, so far :-) Question: When I opened the PGP Keys window for the first time, apart from the *Create Key* wizard, there are about 50 odd keys belonging to various and sundry at NAI. Were these supplied for a purpose or only for illustration? Is there any reason they could not or should no be deleted? Question 2: I sent someone an encrypted test message using that person's public key. It went off fine except, I am left with only the encrypted eMail. What setting should I change in order for my eMail client (Outlook 2000) ...

Primary Key/Foreign Key
Can I use SQL Commands to add Primary key or reference keys to existed tables? (Not a new table, that's why I can't use CREATE TABLE command.) If does, could someone give me some sample codes? Thanks. Hi John, What you need is the ALTER TABLE statement. Look for it in SQLA help for the syntax. Regards, Nat. John Chyan wrote in message <35A4FF5D.5F192806@dissol.com>... >Can I use SQL Commands to add Primary key or reference keys to existed >tables? (Not a new table, that's why I can't use CREATE TABLE command.) > >If does, could someone ...

Primary key
Hello, I've got a modeling problem betwin a conceptual model and a physical model and espacially about the foreign key. See below the illustrated exemple of the problem : -Whether the entity � ARTICLE � with two informations : CODE & WORDING -Whether the entity � UNITE � with two informations : CODE & WORDING An article has necesseraly three unity, the unity of command, of storage and of sails. These three unity can be differents. Conceptual model phycical model The generated physical model doesn't suitable me. How ...

foreign key as a primary key
hi, I am using a foreign key (which is a primary key in a secondary table) as a primary key in a primary table. There is also another primary key in the primary table. Do You have to declare the foregin key as a primary key ? regards Jan Eikeland email:jaeikela@c2i.net url :http://home.c2i.net/jaeikela In article <368b0234.5056065@forums.powersoft.com>, jaeikela@c2i.net says... > hi, > > I am using a foreign key (which is a primary key in a secondary table) > as a primary key in a primary table. > There is also another primary key in the primary table...

SQL db create tables and cluster primary key
With the last table being created below, it has a clustered primary key.One of the fields it is referencing on the previous table, courseId, can NOT be unique.But without it being unique, the cluster primary key won't work.Is there another way to achieve what I am trying to do here?CREATE TABLE dbo.courseScores   (          courseId varchar(20) NOT NULL     CONSTRAINT FK_courseId_courseStructure2 FOREIGN KEY (courseId)    REFERENCES courseStructure (courseId),       ...

Linq to sql table doesn't have Primary Key
Hi, I am working with Linq to Sql . I want to do insert,update, delete operations with the table which doesn't have primary key. but i am unable to do this ,i am getting following error... "Can't perform Create, Update or Delete operations on 'Table(test)' because it has no primary key." Is any one can help me to do operations for the table does't have primary key. Thanks Shiva Sorry but I think that isn't possible because these statements are created with the primary key.  Please: Don't forget to click "Mark as Answer" on the post...

Merge Model shows missing Primary Key and Unique Key entries for a SQL Server Database PDM
I have reverese engineered in a database. I have saved the model and did another reverse engineer to make sure nothing was missed. Regular indexes show no differences but Primary Key and Unique Constraints show up as missing indexes in the model. Is this a known problem or is there some way to have it not mark them as missing? Thanks What version of PowerDesigner are you using? What database and version are you reversing? Are you using script or ODBC? Jay Stevens[TeamSybase] On 23 May 2008 12:03:26 -0700, "Michael Meyer" <michael_meyer@csgsystems....

Web resources about - Clustered Primary Key and Foreign Key: T-SQL - asp.net.sql-datasource

Clustered file system - Wikipedia, the free encyclopedia
A clustered file system is a file system which is shared by being simultaneously mounted on multiple servers . There are several approaches to ...

Even storage that isn't clustered wants to be
Soon it will seem like every storage vendor with a name will have a clustered storage box. Why? In a word: Web 2.0. It would appear that your ...

MegaDroid: 300,000 Androids clustered together to study network havoc
Sandia researcher David Fritz standing in front of a cluster of Android virtual machines, holding two Galaxy Nexus phones. Sandia National Laboratories ...

Sales incentive growth clustered around brands with few CUVs, trucks
Filed under: Honda , Kia , Toyota , Volkswagen , Earnings/Financials While it's arguably been around the longest, the dominance of the four-door ...

Hubble Space Telescope captures 4 galaxies clustered together illuminating brightly in deep space
Deep in space a group of 4 galaxies including a galactic cannibal are illuminating brightly. The image of the quartet of galactic neighbors has ...

Kids with no vaccinations clustered in some schools
Some Ohio schools might as well have a target painted on the side of the building as far as public-health experts are concerned. In some schools ...

NetApp Releases Clustered ONTAP New Software Technology For Big Data and Cloud Infrastructure
Big data and cloud infrastructure is creating an innovation boom and massive business opportunity. Anytime a market is going under radical reformation ...

Clustered Spires Golf Course
We offer deals from local businesses, including our popular Dining Deals, where you can save 30 to 50 percent on your next meal. Special for ...

Sponsored post: NetApp uses clustered data ONTAP to offer open approach to the cloud
As the unabated adoption of cloud technology continues at a truly explosive rate, more and more CIOs and IT leaders are embracing a critical ...

MusicStore fails to run against sql azure database unless clustered indexes are explicitly added for ...
... worked with older builds To fix this error, I had to add this statement to every entity builder.Entity ().Index("Id").ForSqlServer().Clustered(); ...

Resources last updated: 1/16/2016 8:04:28 PM