Delete Query

Hi,

I have two tables

  dbo.CMC INNER JOIN dbo.CMD ON dbo.CMC.CMID = dbo.CMD.CMID
 
  Where CMID is PK of CMD
 
  Is there a way when delete a record in CMD and all the records in CMC that associate with CMD will also be deleted? thanks
mcrmgAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented:
I do this with a trigger:
create trigger [dbo].[CMD_del] on [dbo].[CMD]
For Delete
as
begin 
	delete from CMC where CMID in
	(select CMID from deleted)
end
GO

Open in new window

Kelvin SparksCommented:
If there is a foreign key between the two tables, you can set the ON DELETE property of the foreign key to CASCADE, and these child records will be deleted every time the parent record is deleted.


Kelvin
mcrmgAuthor Commented:
where can I set ON DELETE property? thanks
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

Kelvin SparksCommented:
Open the foreign Key (using modify), scroll to the bottom of the properties and  select the drop down for the ON DELETE property.
mcrmgAuthor Commented:
huh....right click and design the table?  (sorry)
Kelvin SparksCommented:
If you get into the table design, right click and choose relationships. You need to find the correct foreign key (you need to be in the design of the child table).

Select that foreign key and modify the properties.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mcrmgAuthor Commented:
even here say the same thing.

https://docs.microsoft.com/en-us/sql/relational-databases/tables/view-foreign-key-properties

But I just cannot find it...I will try again.
Kelvin SparksCommented:
If of course you don't have a foreign key in place, then you won't find it.
mcrmgAuthor Commented:
Okay, I found it.

Are you referring to "INSERT and UPDATE Spec"?

Delete Rule and Update Rule all show "No Action"   thanks
Kelvin SparksCommented:
Change DELETE RULE to CASCADE
mcrmgAuthor Commented:
I cannot change it. It is gray out.  thanks
Kelvin SparksCommented:
Sounds like you do not have the rights to amend this.
mcrmgAuthor Commented:
hmm, very strange, I have sa passowrd.
Kelvin SparksCommented:
What version of SQL Server is this. I've not seen this before - Can you drop and recreate the key?
mcrmgAuthor Commented:
yes, I tried that. It is still gray out..
Kelvin SparksCommented:
What if create using code i.e
ALTER TABLE child_table
ADD CONSTRAINT fk_name
    FOREIGN KEY (child_col1, child_col2, ... child_col_n)
    REFERENCES parent_table (parent_col1, parent_col2, ... parent_col_n)
    ON DELETE CASCADE;
mcrmgAuthor Commented:
this is what I have

Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "fk_CMID". The conflict occurred in database mydb", table "dbo.Contactl", column 'CMID'.
Kelvin SparksCommented:
What did you code that you ran look like - can you copy it here?
mcrmgAuthor Commented:
ALTER TABLE ContactManagementContact
ADD CONSTRAINT fk_CMID
    FOREIGN KEY (CMID)
    REFERENCES ContactManagementDetail (CMID)
    ON DELETE CASCADE;


If I understand it correctly, ContactManagementContact's pk is ContactManagementDetail's fk
Kelvin SparksCommented:
I think this is back to front. A FK references the primary key table, not the other way around.
mcrmgAuthor Commented:
okay, this is my structure

Table ContactManagementDetail
CMID(PK)
F1
F2


Table ContactManagementContact
CMCID(PK)
CMID(FK)
F1
F2

This might be easier for you to help me. thanks
Kelvin SparksCommented:
You need a FK on table ContactManagementContact using CMID that references the other table - probably already there. You just need to to use cascade delete.
mcrmgAuthor Commented:
I reboot the server, and it works now...Not sure why...thank you
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.