no primary or candidate keys in the referenced table

[daughtery]

I have the following .sql script creating 2 tables but I'm getting an error stating:
"There are no primary or candidate keys in the referenced table 'dbo.FilingMethodProfilesRules' that match the referencing column list in the foreign key 'FK__FilingMet__RuleI__2106348D'."

I have the primary key set in the referenced table which is also created in this same statement. My script is as follows:

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FilingMethodProfilesRules]') and OBJECTPROPERTY(id, N'IsTable') = 1)
	drop TABLE [dbo].[FilingMethodProfilesRules]
GO

Create Table dbo.FilingMethodProfilesRules
(
RuleID 		int	NOT NULL IDENTITY(1,1),
RuleDesc	varchar(30)
)

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FilingMethodProfiles]') and OBJECTPROPERTY(id, N'IsTable') = 1)
	drop TABLE [dbo].[FilingMethodProfiles]
GO

Create Table dbo.FilingMethodProfiles
(
ProfileID	int	NOT NULL IDENTITY(1,1),
CatalogID	int	NOT NULL FOREIGN KEY (CatalogID) REFERENCES dbo.Catalog(CatalogID),
[Description]   varchar(10),
RuleID		int	NOT NULL FOREIGN KEY (RuleID) REFERENCES dbo.FilingMethodProfilesRules(RuleID)
)

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

I'm not sure why I'm getting this error.

 

[r937]

you are getting the error because FilingMethodProfilesRules has no primary or unique key

r937.com | rudy.ca

 

[daughtery]

Oh ok, I thought that the IDENTITY keyword took care of that. I guess I'll just use PRIMARY instead.

Thanks r937

 

[gmmastros]

identity and primary key are not the same thing. It sounds like you want both. While you're at it, you should make the primary key clustered, so...

Create Table dbo.FilingMethodProfilesRules
(
RuleID    [!]int IDENTITY(1,1) Primary Key Clustered[/!],
RuleDesc    varchar(30)
)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[daughtery]

Oops I meant use
IDENTITY(1,1) PRIMARY KEY

 

[daughtery]

I stand corrected again. Thx George.

 

[r937]

not corrected, improved

r937.com | rudy.ca