Foreign key build

Question

Foreign key build

brockaf

Newbie

Points: 2
More actions
March 23, 2010 at 8:58 am

#231113

Hello;
I use a GUID as the Primary Key in all of my tables. I want to Link the "Tax Code" field in the Rate table to the "Code" field in the Tax Table.
The Code in the Tax Table would be something Like "TX1","NOTAX","VAT" ,etc.
Table definitions below
Can I do this?
Thanks in Advance
Allan
Rate Table: Tax Code Link to Tax Table
USE [YourTime]
GO
/****** Object: Table [dbo].[RateTable] Script Date: 03/23/2010 11:56:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RateTable](
[OID] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF_RateTable_OID] DEFAULT (newsequentialid()),
[Code] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Description] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[UnitRate] [float] NULL CONSTRAINT [DF_RateTable_UnitPrice] DEFAULT ((0.0)),
[DailyRate] [float] NULL CONSTRAINT [DF_RateTable_DailyRate] DEFAULT ((0.0)),
[DistCode] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TaxCode] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LastMod] [timestamp] NULL,
CONSTRAINT [PK_RateTable] PRIMARY KEY CLUSTERED
(
[OID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Tax Table : Target of Foreign Key
USE [YourTime]
GO
/****** Object: Table [dbo].[TaxTable] Script Date: 03/23/2010 11:57:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TaxTable](
[OID] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF_TaxTable_OID] DEFAULT (newsequentialid()),
[Code] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Description] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Rate] [float] NULL CONSTRAINT [DF_TaxTable_Rate] DEFAULT ((0.0)),
[TaxAccount] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LastMod] [timestamp] NULL,
CONSTRAINT [PK_TaxTable] PRIMARY KEY CLUSTERED
(
[OID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Luke L SSC-Dedicated Points: 35547 More actions · Answer 1

First, I'd like to point out that you might want to revisit you idea to have all of your tables PK's as GUIDs. PArticularly if they are Clustered primary keys... HEre's Kimberly Tripp going into much more detail than I have time for on why this might be a bad idea. http://www.sqlskills.com/BLOGS/KIMBERLY/post/GUIDs-as-PRIMARY-KEYs-andor-the-clustering-key.aspx I understand you are using newsequentialID() to generate them and that helps to alleviate some on your problems, but...

Secondly, if you didn't use a GUID as your primary key and your Tax codes were what made the rows in that table Unique, they would be a very good candidate to use as your Primary Key column. then your whole problem is solved.

However I guess the biggest question I have is, Did you test making your FK's a column that was not the Primary key? What was the result?

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 2

You can create a FK that references a column that has a unique constraint that is not the primary key. If the Code column in TaxTable is unique and not null, then just add a unique constraint and create the foreign key.

One additional item: Most of your columns are defined as nullable. It’s a bad practice to use that as the default. In most cases, columns should be not null.

Luke L SSC-Dedicated Points: 35547 More actions · Answer 3

Michael Valentine Jones (3/23/2010)
You can create a FK that references a column that has a unique constraint that is not the primary key.

Yes, this is true and is pretty well alluded to in the error message he would get when attempting to add a FK relationship on these two tables. "The columns in TaxTable do not match and existing primary key or UNIQUE contraint." Hence the question as to if he'd tried it yet.

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]

brockaf Newbie Points: 2 More actions · Answer 4

Hi again

I like to use GUID because I Know it is (almost) absolutely Unique. I never use an auto integer value as the Primary key because of seeding issues if the data is to be rebuilt.

Which leaves me the problem.

Now I know that setting "code" to be UNIQUE seems to solve the problem

Process of setting the above is a little confusing using the SQL Server management Studio Express UI

Thanks for the insights

Allan