creating FK contraint with cascading deletes 1

[1DMF]

why am I unable to create an FK constraint that cascades deletes?

I just get this message

Could not create constraint. See previous errors. Cascading foreign key 'Fk_Contacts' cannot be created where the referencing column 'Contacts.ContactID' is an identity column

I have a homeadd table, it is a 1-1 relationship.

If I delete from contacts I want it to delete the HomeAdd record also.

Yes the link bewteen the two is from Contacts PK to the HomeAdd FK.

So what's the problem?

Thanks,
1DMF.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Electronic Dance Music Downloads

 

[Qik3Coder]

From the sounds of your error, you're adding the constraint to the HomeAddress table, not the Contacts table.

I haven't had to do this before, but it should be when deleting the parent record, all children are taken out as well.

Could you paste the constraint you're trying to add?

Lodlaiden

You've got questions and source code. We want both!

 

[1DMF]

Yes I'm adding the FK to the homeadd table, as I understand things the parent deletes from the child, and the child is the one with the FK definition.

Is this not correct?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Electronic Dance Music Downloads

 

[1DMF]

I'm going round in circles here?

I have removed the auto increment from homeadd so the PK is also the FK to the contacts table creating a 1-1 relationship.

But I cannot create the FK in homeadd I just get error...

]The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "Fk_HomeAdd". The conflict occurred in database "HLP_FSA_DB", table "dbo.Contacts", column 'ContactID'.

Is this because there isn't always a homeadd record for every contactid in the contacts table?

This correct, only if the progress through membership does the homeadd get added, just because there is a 1-1 link between the tables doesn't mean there has to be a record in both tables does it?

How do I create an FK from homeadd to contacts with cascading delete only if there is a homeadd record?

I don't want there to have to be a homeadd record for every contacts record.

just delete a homeadd record if one exists when the main contacts record is deleted.

what am I doing wrong?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Electronic Dance Music Downloads

 

[1DMF]

OK, worked it out!

I needed to ensure when creating the FK that I didin't ask it to check the data on creation!

But i would still like clarification as to which table I put the FK in to ensure which is parent and which is child and which way the cascade works!

If I get this wrong we could be in big trouble!

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Electronic Dance Music Downloads

 

[1DMF]

nope I'm still going round in circles!

I have another table a duplicate of HomeAdd called CompAdd , trying to create and exact duplicate FK to contacts and it just errors

Unable to create relationship 'FK_CompAdd_Contacts'.
Cascading foreign key 'FK_CompAdd_Contacts' cannot be created where the referencing column 'CompAdd.ContactID' is an identity column.
Could not create constraint. See previous errors.

why? how come two identical tables one suddenly lets me create the FK and now the other wont?

Is there something wrong with MS SQL 2008 R2?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Electronic Dance Music Downloads

 

[1DMF]

I'm stumped, I found this link

http://msdn.microsoft.com/en-us/library/aa937521(v=sql.80).aspx

I've ran the check there are no additonal records in the child table that do not match the parent table.


Yes not every parent record has a child record, but that is how it is meant to be.

And yes I have told it to ignore exisitng data. (Check existing data on creation )

It still won't create the relationship?



"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Electronic Dance Music Downloads

 

[gmmastros]

You should have the ContactId in the Contact table be an identity.

You should then add a foreign key to the HomeAdd table like this...

Alter 
Table   HomeAdd 
Add     Constraint FK_Contact 
        Foreign Key (PersonId) 
        References Contact(PersonId) 
        ON Delete Cascade

I needed to ensure when creating the FK that I didin't ask it to check the data on creation!

This is probably a mistake. I mean... if the foreign key fails to create that indicates that there is already a problem with your data. Instead, you should clean up the problem and then create the foreign key.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

damn , what a fool I have been!

How on earth was 'insert identity' set on the child table?

That is screwy, man no wonder I couldn't understand why it wouldn't work.

I think I need to go to bed early tonight and come at this with a clear head tomorrow!

Now that iIam starting to put together a proper DB schema , I am finding a lot of bugs and errors that hadn't been picked up when the guy came in and originally ported our access db to SQL!

I was missinterpreting the message 'is an identity column' , of course it is the identiy column (the primary key) , what I didn't realise it meant was it had 'auto identity insert' set when it should not be, this is a 1-1 link and all records in the child table will have the PK provided by the parent!

Once again, much obliged for your support George!






"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Electronic Dance Music Downloads

 

[gmmastros]

Can you run this and let me know if it returns any rows?

SELECT name
FROM   sys.foreign_keys; 
Where  is_not_trusted = 1

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

and this...

SELECT name
FROM   sys.foreign_keys; 
Where  is_disabled = 1

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

apart from your syntax errors ;-)

Both returned 0 rows!

I have just done a test with a test record in the main contacts and now the homeadd / compadd cascades deletes as desired and everything is linking well.

Helps when you realise why it wasn't working.

Though this DbSchema program from sun microsystems I'm using is a bit quirky and has some screen refresh issues!

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Electronic Dance Music Downloads

 

[gmmastros]

Sounds like you got a handle on this now. Sorry about the syntax errors. You know how it is, right???


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

Sorry about the syntax errors.

No problem George, it was a tounge in cheek comment, hence the winky ;-)

You know how it is, right???

I sure do , that's why I'm here asking stupid questions

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Electronic Dance Music Downloads