May 22, 2008 at 9:06 am
I need to create a primary key on one of my tables that consists of more then one field. Are this any issues or concerns that I should be aware of if the first field in the key is a VarChar and the second field is an int?
Thanks
May 27, 2008 at 10:27 am
Sorry that no one has replied to your message, maybe you got caught by the American holiday weekend.
There should be no problem having a varchar and int as your primary key. Obviously you have to follow the rules for null and uniqueness, otherwise you should be fine.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
May 28, 2008 at 6:58 am
Thanks. I didn't think that I would have any problems. I just needed to be sure.
May 28, 2008 at 8:39 am
There are specific data types that don't work in keys, such as bits, text, and image, and the approximate numerics: float and real.
One thing that's cool to play with is basing a primary key off of a calculated field. The only restriction is that the calculation must not be indeterminate. I just put some students through a ringer by requiring a unique key on a calculated field based off a date field that had to be calculated in a specific way to avoid the indeterminacy problem, I had fun!
😀
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
May 29, 2008 at 5:54 am
Sounds like a fun exercise.
May 29, 2008 at 8:16 am
It was pretty simple, at least on the surface. The case study was a drug study where you have a group of subjects with a condition and you're testing different treatments against them. One of the rules was that each subject could have only one test per day and could take any given test only once. So the test table would reasonably consist of SubjectID (smallint), TestID (smallint), TestDate (smalldatetime), Result (char(1)). So you need a unique constraint on SubjectID and TestID, and also one on SubjectID and TestDate. But if you add a time value to the test date, you can have two tests on the same day as the default for no time value entered is midnight, 00:00.
Enforcing that unique constraint and keeping the field as a smalldatetime was amusing. Obviously if your front-end program only allowed a date to be entered, this wouldn't be a problem, but we were working only in Query Analyzer and Enterprise Manager.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
May 30, 2008 at 2:44 pm
What you have described is a M:M intersection table between Subjects and Tests with the addition of the date and result of each test. The restriction that each subject is limited to one of each test fits into the normal operation of an intersection table -- the two FK fields form the composite key. It makes sense that the time portion of the test date would need to be preserved, so the requirement of one test per day would have to be implemented without altering that date -- thus the computed column. Making the computed column part of a unique index requires only that the calculation be deterministic (which it obviously is -- aamof, I don't see how this would have presented any problem) and that the result be persisted. (Well, there are some key settings you must make sure are set to ON, but that is probably beyond the scope of your test.) An easy puzzle, really, but I can see that it may have thrown me for a loop back in my student days. It's hard to say because it has been, ahem, a few months since my student days. 😀
CREATE TABLE dbo.SubjectTests(
SubjectID smallint NOT NULL, -- FK to Subjects table and one part of composite PK
TestID smallint NOT NULL, -- FK to Tests table and second part of composite PK
TestDate smalldatetime NOT NULL,
Result char(1) NOT NULL,
TruncTestDate AS (DateAdd( dd, 0, DateDiff( dd, 0, TestDate ))) PERSISTED,
-- Each subject may take no more than one of each test
CONSTRAINT PK_SubjectTests PRIMARY KEY CLUSTERED(
SubjectID asc,
TestID asc
);
GO
-- Each subject may only take one test on any given day.
CREATE UNIQUE NONCLUSTERED INDEX UX_SubjectTests_SubjectID_TruncTestDate
ON dbo.SubjectTests(
SubjectID asc,
TruncTestDate asc -- Look Ma, we can index this!
);
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
May 30, 2008 at 4:07 pm
I don't have time to test what you did, but it looks like it might be viable. Try two dates: 5/2/08 and 2/5/08, it's a good test if people want to try parsing the date into a fixed value. But you're definitely correct on the M:N relationship, the case study had 1:1, 1:M, and M:N in it.
This is what I did, your solution is a lot simpler, but I'm known for sometimes over-complicating things :D:
CREATE TABLE [dbo].[TestResults] (
[TestID] [smallint] NOT NULL ,
[SubjectID] [smallint] NOT NULL ,
[TestDate] [smalldatetime] NOT NULL ,
[Results] [char] (1) NOT NULL ,
[Comments] [varchar] (8000) NOT NULL ,
[DateOnly] AS (datepart(year,[TestDate]) * 10000 + datepart(month,[testdate]) * 100 + datepart(day,[testdate]))
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TestResults] WITH NOCHECK ADD
CONSTRAINT [PK_TestResults] PRIMARY KEY CLUSTERED
([TestID], [SubjectID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TestResults] ADD
CONSTRAINT [IX_TestResults_Subject_TestDate] UNIQUE NONCLUSTERED
([SubjectID], [DateOnly]) ON [PRIMARY] ,
CONSTRAINT [CK__TestResul__Resul__078C1F06] CHECK ([Results] = 'N' or ([Results] = 'U' or [Results] = 'F'))
GO
(I didn't previously mention the constraint on test result, but it was part of the spec that I gave my students. I was teaching SQL Server to three Cobol programmers with no previous relational experience, I was using a case study to make them use what I'd taught, and I naturally threw a few curves in to the study.)
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
May 30, 2008 at 6:02 pm
Tomm,
Your code won't work on SQL Server 2000 (this forum is a 2k forum)... keyword PERSISTED does not exist and the calculated column is considered "non-deterministic" in 2k so the unique index won't fly on it. One way to get this to work in 2k is to use "CONVERT" in the calculated column in 2k instead of datediff .
Works fine in 2k5... (add the missing parenthesis, though).
--Jeff Moden
Change is inevitable... Change for the better is not.
June 2, 2008 at 8:11 am
Ah, I hadn't noticed the PERSISTED, it was off into the scrolled section. It's kind of funny, I'd never had a problem with non-deterministic indexing until I started developing this case study.
I have found the saying very true that goes "he who teaches learns twice." You have to know the material before you teach, and when you teach or develop the material the way you're going to teach it, you learn more.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
June 2, 2008 at 12:14 pm
Your code won't work on SQL Server 2000 ...
Yes, I missed that. When teach posted his answer, I had no sooner framed the question, "Now why did he do it like that?" when I realized the answer. I really must start paying more attention to which forum I'm in. :blush:
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
June 2, 2008 at 1:03 pm
I would like to say, there is no issue in selecting columns as compound indexes except some special data types, such as text and image.
But the number of total combined bytes should not be long and duplicates should be less.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply