Primary Key selection - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Primary Key selection

Author

Topic

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-09-12 : 15:53:19

DDL Below...

Question. I've got two tables ConferenceParam and ConferenceParamValue.

ConferenceParam holds the default settings for a Customer's coference.
ConferenceParamValue holds the "overriding" settings for a particular conference.

I've got a composite primary key of ParamName and CustomerID. Is this a good idea, or should I go with an arbitray key like an Identity? I've read all of the articles on the site about choosing them, but I still have not found a "rule" that I can apply that tells me when to do what.

Composite Key or Abitrary key? What should I do?
Thanks all!


CREATE TABLE [dbo].[ConferenceParam] (
	[ParamName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[CustomerID] [int] NOT NULL ,
	[DefaultValue] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Cre_Date] [datetime] NOT NULL ,
	[Cng_Date] [datetime] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ConferenceParamValue] (
	[ParamName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[CustomerID] [int] NOT NULL ,
	[ParamValue] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Cre_Date] [datetime] NOT NULL ,
	[Cng_Date] [datetime] NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ConferenceParam] WITH NOCHECK ADD 
	CONSTRAINT [PK_ConferenceParam] PRIMARY KEY  CLUSTERED 
	(
		[ParamName],
		[CustomerID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[ConferenceParamValue] WITH NOCHECK ADD 
	CONSTRAINT [PK_ConferenceParamValue] PRIMARY KEY  CLUSTERED 
	(
		[ParamName],
		[CustomerID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[ConferenceParam] WITH NOCHECK ADD 
	CONSTRAINT [DF_ConferenceParam_Cre_Date] DEFAULT (getdate()) FOR [Cre_Date],
	CONSTRAINT [DF_ConferenceParam_Cng_Date] DEFAULT (getdate()) FOR [Cng_Date]
GO

ALTER TABLE [dbo].[ConferenceParam] ADD 
	CONSTRAINT [FK_ConferenceParam_ConferenceParamValue] FOREIGN KEY 
	(
		[ParamName],
		[CustomerID]
	) REFERENCES [dbo].[ConferenceParamValue] (
		[ParamName],
		[CustomerID]
	),
	CONSTRAINT [FK_ConferenceParam_Customer] FOREIGN KEY 
	(
		[CustomerID]
	) REFERENCES [dbo].[Customer] (
		[CustomerID]
	)
GO

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

r937
Posting Yak Master

112 Posts

Posted - 2002-09-12 : 16:40:28

if CP is the default and CPV contains the override, i don't understand why CP has a foreign key to CPV -- shouldn't it be the other way around?

your clustered indexes -- and i hasten to say i'm not a dba, i don't know clustering all that well -- might make more sense if they were defined [CustomerID],[ParamName] rather than [ParamName],[CustomerID] so that customer rows are located together

as for natural versus surrogate key, i don't see the benefit that a surrogate key would give here

you wouldn't reveal the key values in either case, right?

rudy
http://rudy.ca/

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-09-12 : 16:44:32

Yeah I think you are right. I changed a few things around, but I do need to look at the clustered index. i think it makes mroe sense to put the Customers params together.

I ran a few test queries with the composite primary key, and they were very good. The Subtree cost was 0.0128, but that's with very little test data.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources