Foreign key on an IDENTITY column

Question

Foreign key on an IDENTITY column

derek.colley

SSCrazy Eights

Points: 8040
More actions
January 17, 2012 at 8:09 am

#249825

Maybe I can't see the obvious solution but...
I'm trying to create a table with a column that has a foreign key constraint on a column in another table. That column is a primary key, IDENTITY column. The purpose being to create a query (or range of queries in SPs) that can JOIN the information from my first table, containing transactions, to the categories in my second table, containing categories, and present the results.
Here is the CREATE TABLE syntax I'm using:
CREATE TABLE categories (
surrogateID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
categoryName VARCHAR(255) NOT NULL,
fk_surrogateID INT NOT NULL FOREIGN KEY REFERENCES statementHistory(surrogateID)
)
The statementHistory table is defined as:
CREATE TABLE statementHistory (
surrogateID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
txDate DATETIME NOT NULL,
txDescription VARCHAR(255) NOT NULL,
txDeposit VARCHAR(10),
txWithdrawal VARCHAR(10)
)
Would have thought that was OK but then I get a surprise error message:
Msg 1770, Level 16, State 0, Line 54
Foreign key 'FK__categorie__fk_su__300424B4' references invalid column 'surrogateID' in referenced table 'statementHistory'.
Msg 1750, Level 16, State 0, Line 54
Could not create constraint. See previous errors.
My column names are definitely right. What am I doing wrong? Is it because my PKs are IDENTITY columns?
---
Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:
Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

Viewing 10 posts - 1 through 9 (of 9 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Can't see anything wrong with that. Does the statementHistory table definitely exist when you are trying to create the categories table?

John

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 2

Yes, definitely there. I created the table and can actively query it. It's using the dbo schema, I've tried substituting dbo.statementHistory with no luck.

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

I'm stumped, then. The only thing I can think of for you to try is create the table without the foreign key, then use ALTER TABLE to add it separately. That's good practice anyway since it means you can give the constraint a meaningful name.

John

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 4

Not sure, but the problem may be that the surrogateId column is defined as an IDENTITY in both tables.

I would only expect the parent table to be defined as an IDENTITY, not the child table.

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 5

I've just done that John and it's working fine. I'm totally bemused by this. Really strange behaviour. Thanks for your help!

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

nigel. SSChampion Points: 11636 More actions · Answer 6

I see from your error message that the error is on Line 54, maybe there's something causing the issue in the other 40+ lines of your script? Can you post the whole script?

Nigel
How to post questions

brendan woulfe Hall of Fame Points: 3888 More actions · Answer 7

Nigel may be on to something. If you run those two statements they complete successfully, as long as the order is correct of course. Post the entire script you're working with.

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 8

The problem is resolved now, whatever it was. I ran them in the right order so not sure why it borked. There isn't a complete script - just defining some tables, that's all - what I posted is what I had at the time.

Thanks very much for all your replies.

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

nigel. SSChampion Points: 11636 More actions · Answer 9

derek.colley (1/17/2012)
The problem is resolved now, whatever it was. I ran them in the right order so not sure why it borked. There isn't a complete script - just defining some tables, that's all - what I posted is what I had at the time.
Thanks very much for all your replies.

Ok, still a mystery then!

Nigel
How to post questions