Creating inter-table relationships using Primary Keys/Foreign Keys Problem


I'm going through my tables and rewriting them so that I can create relationship-based constraints and create foreign keys among my tables. I didn't have a problem with a few of the tables but I seem to have come across a slightly confusing hiccup.

Here's the query for my Classes table:
Code: CREATE TABLE Classes ( class_id INT IDENTITY PRIMARY KEY NOT NULL, teacher_id INT NOT NULL, class_title VARCHAR(50) NOT NULL, class_grade SMALLINT NOT NULL DEFAULT 6, class_tardies SMALLINT NOT NULL DEFAULT 0, class_absences SMALLINT NOT NULL DEFAULT 0, CONSTRAINT Teacher_instructs_ClassFKIndex1 FOREIGN KEY (teacher_id) REFERENCES Users (user_id) ) This statement runs without problems and I Create the relationship with my Users table just fine, having renamed it to teacher_id. I have a 1:n relationship between users and tables AND an n:m relationship because a user can be a student or a teacher, the difference is one field, user_type, which denotes what type of user a person is. In any case, the relationship that's 1:n from users to classes is that of the teacher instructing the class. The problem exists when I run my query for the intermediary table between the class and the gradebook:
Code: CREATE TABLE Classes_have_Grades ( class_id INT PRIMARY KEY NOT NULL, teacher_id INT NOT NULL, grade_id INT NOT NULL, CONSTRAINT Grades_for_ClassesFKIndex1 FOREIGN KEY (grade_id) REFERENCES Grades (grade_id), CONSTRAINT Classes_have_gradesFKIndex2 FOREIGN KEY (class_id, teacher_id) REFERENCES Classes (class_id, teacher_id) ) Query Analyzer spits out: Quote: Originally Posted by Query Analyzer There are no primary or candidate keys in the referenced table 'Classes' that match the referencing column list in the foreign key 'Classes_have_gradesFKIndex2'. Now, I know in SQL Server 2000 you can only have one primary key. Does that mean I can have a multi-columned Primary key (which is in fact what I would like) or does that mean that just one field can be a primary key and that a table can have only the one primary key

In addition, what is a "candidate" key Will making the other fields "Candidate" keys solve my problem

Thank you for your assistance.

Posted On: Friday 26th of October 2012 12:01:40 AM Total Views:  562
View Complete with Replies

Related Messages:

Cannot connect locally through Query analyzer using IP address   (379 Views)
I am having problem with my SQL server. The server is running Windows 2K3 Standard Edition /w SQL 2000 sp4. For some reason, when I try to connect to it using Query Analyzer and put the IP address for "SQL Server", it gives me the following error: Quote: Unable to connect to server : Server: Msg 17, Level 16, State 1 [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied. If I use the sql instance name, which in my case is "WIN\SQL" (WIN is the name of the server, SQL is the name of the SQL server), it connects with no problem. One would think that there's problem with TCP/IP. However, I downloaded Aqua Data Studio, and if I use that to connect remotely to the server using the IP address, I can connect with no problem at all. Anyone got any idea what's going on
Database Index - How to find it using the Application's GUI   (174 Views)
On the Enterprise Manager of the MS-SQL server, how do I find or see the database index in there Both that was done by SQL query or Stored Procedure
loop though table using RowID, not cursor (was "Loop")   (400 Views)
I have a table with RowID(identity). I need to loop though the table using RowID(not using a cursor). Please help me.
Code for creating a table in SQL from external source   (458 Views)
I'm looking to create a table in SQL using data from the as/400. I need some code that extracts it from as/400 using odbc and I need it to loop and create that table every minute. The file its retrieving is a live file but not very large.. I need to basically poll the data. Does anyone have code for something like this..
Send HTML maile using xp_sendmail in SQL Mail   (173 Views)
I am using SQL Mail to send an email. Is there some way to send it with an HTML body
MSSQL server is using 851mb of memory?!   (196 Views)
Recently here at work we have been having a lot of our web applications time out after a couple minutes. Really doing just basic things like counting the number of records in the database, etc. I went on to the machine itself (win2k) and looked at what MSSQL server was doing in the process list... and it was taking up a mind boggling 851 megabytes of memory. Out of a gig of RAM on that machine there was only 13mb available. We restarted it last night and when I checked it today it has already grown back up to 460mb. Is this normal I've never heard of applications taking up this much ram... especially since its not like anyone, or anything, is pulling down 850mb every second from the database. Is there some kind of known memory leak in MSSQL server (8.00.760 SP3) Or is there some other reason our web apps are now timing out, when previously they never did ...
Search and Results using multiple columns   (417 Views)
I have a DBTable named Vendors which includes the following columns: CompanyName (Name Here) SBI (Yes, No) MBE (Yes, No) SBI (Yes, No) WBE (Yes, No) I'm trying to do a search on Vendors where either SBI or MBE or SBI or WBE is equal to 'yes' and then order by company name. I'm lost on both the search and the results code. I'd like to have a drop down for the search like this: Show all Vendors who are: SBI SBE MBE WBE Any ideas on how to make this work would be greatly appreciated.
Problem using count function   (160 Views)
I am having a problem trying to use COUNT in the following statement in a Stored Procedure; select #TEMPTABLE.ID, #TEMPTABLE.DESC, #TEMPTABLE.PDF, #TEMPTABLE.WEB, #TEMPTABLE.POST, #TEMPTABLE.COMMENT, #TEMPTABLE.LINK, count(#TEMPTABLE.WEB) as NUMWEB, from #TEMPTABLE order by #TEMPTABLE.ID which returns the error; Column '#TEMPTABLE.ID' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause the value WEB should be either 1 or null and I want NUMWEB to indicate whether their are any set to 1 or not. Any ideas
Sp Parameters using 'In()'   (182 Views)
I have a MS Access projects database thats linked to an Sql Server database. I want to base my reports on stored procedures that have parameters. How would I pass an in statement to my parameter so that instead of @Location = Dallas I could do something like an in statement @Location in (Dallas,Chicago,Seattle). I know that if I used a view I could just pass a filter. How can I accomplish the same thing with a sp and parameters I know there has to be a way to do this but I can't figure it out
using sqldmo to get some property of db   (231 Views)
how to make use of sqldmo to 1. get DB name (the physical name of the database) 2. perform an auto clear on all connection using the DB (or maybe running some stored proc or query), so that the DB is no longer used by any connections and can be detached anytime without error saying the "db is in use"
Connect to sql server using ole db provider   (163 Views)
Using a javascript program i try to maintain a table defined in Sql server . Unfortunatelly the program can't update the table because the OLE DE PROVIDER cannot determine if you can write to each field . It returns for each field of the table , the field property adFldUnknownUpdatable .This value indicates that the provider cannot determine if you can write to the field. iT IS STRANGE TO ME BECAUSE I HAVE INSTALL MDAC 8 IN MY COMPUTER AND I USE THE MICROSOFT OLD DB PROVIDER FOR SQL SERVER .
connect to MSDE instance using osql   (194 Views)
I am new and blue! Utlimately I want to connect to a database using asp. I have created an instance named Joker using securitymode=SQL because I foolishly created the original default server using Windows authentication and I can't connect via asp because it keeps telling me I am not trusted. So I am testing the connection using osql like this, and getting the error below. I created the instance with a verbose log and the log said it was created successfully, and I see it under services. BUT it doesn't have its own directory like the default server does. That's kinda weird. Since my server is an instance, is there perhaps some prefix to the name that I am missing
Upload file to SQL database using classic ASP   (241 Views)
, I am trying to upload files (.doc/.pdf) to a SQL database (2005) but I am really struggling to find any step by step guides. This is what happens on my ASP form: User selects a document Document is currently upload to a temp file and sent to a email address However it also needs to be stored within a database field. I have set the field type within the database to Image. But I am unsure about how I save the binary data information when all I really have is a link to the document (for example C://) from the user. I also want to be able to download this document at a later date, this is also possible Thank you in advance for any help.
Automatically using SQL Server [Delete record]   (175 Views)
I have a newsletter registration with a confirm link. If the new email has not been confirmed within 48 hours I would like SQL to automatically delete the record No idea how to do this with SQL server as perhaps I have not ventured into this area of operations but would appreciate any help Trevor
Discover relationships between existing tables of a database   (294 Views)
Friends, I am right now working on a project that has a database with over 100 tables in a database. Because of extreme time constraints the developers didn't build in any relationships or constraints between or in the tables. Now I need to remodel the database such that the database is more structured and normalized. I don't have much knowledge about the database design since it is a 2 year old application and the person who developed the database is now gone. I know remodelling the database would require knowledge of the existing database and business rules. I was wondering if there are any tools that could suggest or discover relationships between tables. For eg. Lets say there are two tables named 'Customer' and 'Order'. I notice that there is a column named 'id' in Customer and a column named 'customer_id' in Order. So I ask the tool to discover a relationship between id and customer_id and it tells me that there is a one-one or one-many or no relationship by comparing values. I heard ERWin would be able to do that but thats expensive. Please do let me know asap.
creating a SQL Query   (226 Views)
Hi friends! I am facing a difficulty in creating a query in SQL. I have a table name as master which has fields ------------------------------------------------------------------ pid | pretailPrice | pourPrice | cat ------------------------------------------------------------------ 1 10 8 1 2 8 7 1 3 7 5 1 ------------------------------------------------------------------ I am trying to create a query, which calculates discount between pretailprice and pourprice and the output should give the maximum discount from all the records for a particular category i.e 1. Please help Thank you.
I need a good tutorial/book to learn creating a secure/concise database.   (267 Views)
, I'm a software developer for a fairly large company, and I've worked with various languages including C#, Java, PowerBuilder and others. However, as a software developer my skills and experience have been limited to such. I'm interested in creating some websites in my spare time in the hopes of making a little passive income on the side. I'm curious if there are any really good tutorials or books out there on how to properly and concisely create databases. As a software developer my knowledge of DB's is extended to submitting DB Mods to our DBA's. The DB Mods I would typically write would be regular DDL stuff; creating tables, adding columns, adding data, creating stored procs, etc. And then of course using DML from the code itself. However, I want to know the things I don't know (I don't even really know what that is... which is why I want a book/tutorial). I'm assuming I'm going to need things like security/roles/access-control/password-encryption/backups/indexing etc etc. What would be a great tutorial-series or book for me. Preferably something that will contain all the information I need and nothing more. I'd hope not to need to read a 1000 page book on only a few topics, or have to buy multiple books. Concise and effective is what I'm looking for here. Maybe something that is about Databases strictly related to web development P.S. I believe I would like to use whatever free service SQL Server offers.
Help with creating procedures   (408 Views)
I have to create 5 procedures and the study material I have dont explaine procedures enough. My 5 procedures are as follows: *sp_ProductInsert - ows the user to add a record to the PRODUCT and PRICE tables. *sp_PriceUpdate - ows the user to change the price of a specified product. The start and end dates (columns) of the price must also be updated. Arguments: ProductID, new price, start date, end date. *sp_DeleteProduct - ows the user to delete a specified product. Records related to the product must be removed from the PRICE table but if the product appears on an invoice that is not more than 60days old, it may not be deleted. Argument: Product ID. *sp_DeleteClient - ows the user to delete a specified client. Argument: ClientID. *sp_displayInvoice - Displays the details and the total cost of a specific invoice. **************************************** Please id appreciate any help!! my tables that I have: CLIENT with id, name, address, tel INVOICE with id, date, clientID PRODUCT with code, description PRICE with productCode, startDate, endDate, price ********* I will also be greatful for extra reading regarding procedures.
Ideas on creating ARchive Table   (325 Views)
I need some ideas on how to archive some of the data that I am capturing in a table. I have this table that has the following columns. KPI ID, KPiNAME, KPITYPE, WEEK1, WEEK2, WEEK3, WEEK4. My table displays last four weeks of data. What I want to do is to create a sub page for this main page where users can go and produce archive reports. Any ideas on how I can archive them to display the report.
need help creating stored procedure   (382 Views)
hi , i nid to create a stored procedure which clears the database every 3 months for example on June 1 [sep 1,dec 1, etc] the stored procedure will delete all records dated b4 june 1. i have browse thru different tutorials on the web and have read abt cursors, but i do not know how to use it. could someone kindly give me a head start thanx