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.
| Author |
Topic |
|
AlanS
Starting Member
28 Posts |
 Posted - 2004-08-24 : 14:30:21
|
| I'm building import scripts as part of moving to a new database. The following code:INSERT INTO PersonAddress(AddressID, PersonID)SELECT Person.PersonID AS PersonID, Address.AddressID AS AddressIDFROM Person INNER JOINAddress ON Person.SocialSecurityNumber = Address.NonIntegerImportPrimaryKeyID AND Person.NonIntegerExternalSystemID1 = 'Client' AND Address.NonIntegerExternalSystemID1 = 'Client'always produces this error:Server: Msg 547, Level 16, State 1, Line 11751INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'Person_PersonAddress_FK1'. The conflict occurred in database 'CAPS', table 'Person', column 'PersonID'.The statement has been terminated.I would think this is impossible - the FROM clause specifies an inner join between the Address and Person tables, so any Person.PersonID in the resulting recordset would have to already exist in the Person table. Person.PersonID and Address.AddressID are the primary keys.HELP! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-24 : 15:16:11
|
| It's the order of your columns. You've got AddressID, PersonID in the INSERT INTO part, then you've got PersonID, AddressID in the SELECT. So you are trying to put addresses in the PersonID column and persons in the AddressID column.Tara |
 |
|
|
AlanS
Starting Member
28 Posts |
Posted - 2004-08-24 : 15:53:56
|
| Thanks for your help. Changing the order did the trick. I suppose I'm too much in the grips of my Visual Basic background, where the whole point (it seems) of naming parameters is that you can then supply them in any order. Live and learn. |
|
|
|
|
|
|
seemingly impossible foreign key error