August 13, 2008 at 9:33 am
HELP!
I must be doing something wrong, but I can't figure out what it is.
I have a table (Table A) of 11 columns, all ints, and about 6 of the columns are NOT NULL and have Foreign Key constraints on them. I need to add a new Foreign Key constraint to an existing column, but am getting the error "Number of referencing columns in foreign key differs from number of referenced columns" and I don't know why.
The foreign key does not currently exist, it is referencing a single primary key of my DimTime table. I've dropped my Primary Key on Table A which references all the Foreign Keys. Now I'm adding the foreign key constraint.
Alter Table TableA
Add Constraint FK_TableA_TimeKey Foreign Key References dbo.DimTime (TimeKey);
Simple enough code, I thought. But apparently I'm missing something. I've tried putting in an "Alter Column TimeKey Set" in place of the "Add", but that doesn't work. Neither does the above without the SET keyword.
I cannot drop the column and re-add it. I also cannot drop the table and recreate it. And lastly, I cannot use "With NoCheck".
Any thoughts as to what simple solution I've missed?
August 13, 2008 at 10:07 am
I may be miles off, but you haven't said which column to put the fk on. Maybe this?
Alter Table TableA
Add Constraint FK_TableA_TimeKey Foreign Key (TimeKey) References dbo.DimTime (TimeKey);
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 13, 2008 at 10:11 am
You're not miles off. That is EXACTLY what I forgot. DOH!
I looked and looked in BOL, but it doesn't have this as a code reference. I even googled the stupid thing and couldn't find a reference. This is what happens when you don't have to use a piece of code for over a year then find out you need to write it up.
Thanks. I appreciate the answer. @=)
August 13, 2008 at 10:21 am
Brandie Tarvin (8/13/2008)
I looked and looked in BOL, but it doesn't have this as a code reference.
There is, it's just a little hidden. Right at the bottom of the page on ALTER TABLE (T-SQL), at least it's there in the April 2006 version.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 13, 2008 at 10:34 am
Curiousier and Curiousier... It is NOT there in the July 2006 version. The only code examples I see are:
A) with the Alter Table Alter column to set an identity
B) Adding a default value to a column
C) Dropping a Default
I wonder why they removed it.
August 13, 2008 at 1:19 pm
Brandie Tarvin (8/13/2008)
Curiousier and Curiousier... It is NOT there in the July 2006 version. The only code examples I see are:A) with the Alter Table Alter column to set an identity
B) Adding a default value to a column
C) Dropping a Default
I wonder why they removed it.
I don't think they did. Check the title of that page. I'll bet you coffee it reads "ALTER TABLE (SQL Server Mobile)"
It catches me just about every time. The default when you navigate from BoL's index frequently is the page for SQL Mobile edition, rather than full SQL Server. There's a tab at the bottom titled "Index Results for ..." and the T-SQL is usually the 2nd option in that list.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 14, 2008 at 3:45 am
Huh. Looks like I owe you coffee. @=)
I never even noticed the Mobile thing. That explains a lot. It's also incredibly annoying. But I think it shows up first because of the Location being done in Alpha order. Location says "SQL Server 2005 Mobile Edition..." then "Transact-SQL Reference" for all the others.
They need a better priority system than alphabetical. They need to be able to let you choose an option on how to set what default comes up. Like if you always want Transact-SQL Reference to come up first (as opposed to Local, then Online or Online then Local).
So, you coming to Orlando SQL Saturday? If so, I can hook you up with coffee then. @=)
August 14, 2008 at 4:41 am
Brandie Tarvin (8/14/2008)
They need a better priority system than alphabetical. They need to be able to let you choose an option on how to set what default comes up. Like if you always want Transact-SQL Reference to come up first (as opposed to Local, then Online or Online then Local).
It is irritating. I recall reading a way to remove stuff from the index, but is wasn't simple or straightforward, and I can't remember where.
It appears that it's changed in SQL 2008's BoL. I used the index to Alter Table and it went straight to Alter Table (Transact-SQL)
So, you coming to Orlando SQL Saturday? If so, I can hook you up with coffee then. @=)
No. It's a little far to travel. I live in South Africa.
PASS this year? Otherwise whenever. 😎
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply