PK with more than one field?

Absolutely. It's known as a composite key:

Here's the syntax based on Bob Denver and some memorable
Dumb and Dumber
goodness:

CREATE TABLE ThatBobDenverIsFullOfS__t (
	FilingDate char(8) NOT NULL, 
	Year char(4) NOT NULL,
	[Name] varchar(20) NOT NULL,
	OtherStuff varchar(200),
	blah int 
	CONSTRAINT PK_BobDenverTable PRIMARY KEY CLUSTERED
(FilingDate,Year,Name) ON [PRIMARY]
)
GO

Just make sure that each of your composite domains (columns)
is NOT NULL,
and you'll be fine. 

You can even specify different sort orders for each column
when you create
the PK.... So, say you wanted the table sorted by DESC date,
the constraint
would be:

CONSTRAINT PK_BobDenverTable PRIMARY KEY CLUSTERED
(FilingDate DESC, year,
name)... 

hth, 

--Mike


-----Original Message-----
From: eric van buren [mailto:ericvanburenmsn.com] 
Sent: Thursday, August 24, 2006 9:39 AM
To: aspnet-databasesaspadvice.com
Subject: [aspnet-databases] PK with more than one field?

Is it possible to create a ?key? in SQL Server 2000 that is
a combination of
three fields such that a record cannot be created if all
three of those
fields would be duplicated in the table? 

For instance, if there is a record with? 

FilingDate = 20060808
Year = 2005
Name = Bob Denver 

?the table would not accept another record if it had those
three values in
those three fields?




Need SQL Advice? http://sqladvice.com
Need RegEx Advice? http://regexadvice.com
Need XML Advice? http://xmladvice.com


Need SQL Advice? http://sqladvice.com
Need RegEx Advice? http://regexadvice.com
Need XML Advice? http://xmladvice.com