October 21, 2009 at 12:56 pm
I have two tables, studentRosterFinal and password, each with a composite PK comprised of the First and Last column. Not certain of the index properties either (clustered, non-clustered/unique, etc). Student would have duplicate keys, as there are multiple entries for First/Last name of student in studentRosterFinal
Table: studentRosterFinal
First, Last, username, Password, ClassID, Grade
Table:password
I want to create a result set using info from both tables. I though I would use both composite keys in the criteria but not working. Below is as far as I've gotten:
CREATE TABLE studentRosterPassword
(
First varchar(50) REFERENCES studentRosterFinal (PK_studentRosterFinal),
Last varchar(50) REFERENCES password (PK_password)
PRIMARY KEY (First, Last)
)
GO
Please include sample code using composite keys
Thanks
Jeff
October 21, 2009 at 4:37 pm
You can JOIN tables based on more than one column. It will work, even in the absence of compound keys or other indexes. Indexes may make the query run faster, but they don't change the final result.
FROM OneTable A
JOIN AnotherTable B on A.First = B.First and A.Last = B.Last
Instead of asking other to post up sample code, why don't you post up your code that isn't working so we can make suggestions. Also post up an example (visual, not verbal) of what you want your output to look like. You will get much better assistance, much quicker, that way. 😉
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 21, 2009 at 5:19 pm
Jeff, I guess you were looking for syntax of the FK constraint for multiple columns?
CREATE TABLE password
(
[First] varchar(50) NOT NULL,
[Last] varchar(50) NOT NULL,
[Password] varchar(20) NOT NULL,
CONSTRAINT FK__studentRosterPassword FOREIGN KEY([First], [Last])
REFERENCES studentRosterFinal([First], [Last])
)
Now, you mentioned that student can have duplicate keys - if so, the pair [First], [Last] can't be the primary key for the table, because primary key has to be unique. If it is set up as primary key in studentRosterFinal, the code above should work, but the table studentRoseterFinal will not allow you to add two students with the same name.
Regards
Piotr
...and your only reply is slàinte mhath
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply