How delete foriegn key in dbms who exist in other table?

Question

child table m sy record kaisy delete karty hy jis ki foriegn key dosry table m ho, Uski query hi bta dy?

Pankaj Kumar Choudhary · Answer

Hello @Bilawal i create Employee and Department table according your diagram and deleted data from department table successfully my query is : ALTER TABLE EMPLOYEE NOCHECK CONSTRAINT FK_Employee_Department DELETE FROM Department WHERE DeptID=1 ALTER TABLE EMPLOYEE CHECK CONSTRAINT FK_Employee_Department instead of FK_Employee_Department write the name of your constraint that can you find here Please try this

Pankaj Kumar Choudhary · Answer

hello @Bilawal you can use this stored procedure CREATE PROCEDURE usp_DELETE_DEPARTMENT ( @EMP_IID INT ) AS BEGIN ALTER TABLE EMPLOYEE NOCHECK CONSTRAINT FK_Employee_Department DELETE FROM Department WHERE DeptID=@EMP_IID ALTER TABLE EMPLOYEE CHECK CONSTRAINT FK_Employee_Department END

Bilawal Niaz · Answer

it's run. thnx

Pankaj Kumar Choudhary · Answer

Hello @Bilawal try thi query Select Gender, Count (Gender) from Employee Group By Gender This query will work for you if it show any error then tell me i will post again.........

Bilawal Niaz · Answer

pehly khud try ki hy, nhe samjh ae to yaha py post kya hy question. agr jwab hi nhe dena to phr ye website hi ku bnai?

Kiran Roy · Answer

Select COUNT(Case When Gender='Male' Then 1 End) as Total_Male, COUNT(Case When Gender='Female' Then 1 End) as Total_Female From Employee

Bilawal Niaz · Answer

Thnx Bro, Can You Give Me Your Face Book ID; For Further Information?

Jaipal Reddy · Answer

Simply insert that query into - Create procedure usp_proc_name ( -- IF any parameters ) As Begin -- here the query End

Bilawal Niaz · Answer

OOP'S Thank's Brother It Really Work. Thank's For Solve My Problem. One More Thing Is That I Want This Querry To Convert Into "Stored Procedure" How?

Pankaj Kumar Choudhary · Answer

hello @bilwal please try this method 1. First Go to your Employee table and Right click on Design Option 2. Now select the column that contain foreign key and right click on that column and select option of Relationship Now select your Foreign_Key Constraint After that goto INSERT and UPDATE Specification here you will find No Action option for both Delete and Update rule now change these rules with cascade option this cascade option means that if you delete/update the record from parent table then related data will be automatically delete update from child table you can use another option as per your requirement Delete Rule Specify what happens if a user tries to delete a row with data that is involved in a foreign key relationship: No Action An error message tells the user that the deletion is not allowed and the DELETE is rolled back. Cascade Deletes all rows containing data involved in the foreign key relationship. Do not specify CASCADE if the table will be included in a merge publication that uses logical records. For more information about logical records, see Grouping Changes to Related Rows with Logical Records . Set Null Sets the value to null if all foreign key columns for the table can accept null values. Does not apply SQL Server 2000. Set Default Sets the value to the default value defined for the column if all foreign key columns for the table have defaults defined for them. Does not apply SQL Server 2000.. Update Rule Specify what occurs if a user tries to update a row with data that is involved in a foreign key relationship: No Action An error message tells the user that the update is not allowed and the UPDATE is rolled back. Cascade Updates all rows that contain data involved in the foreign key relationship. Do not specify CASCADE if the table will be included in a merge publication that uses logical records. For more information about logical records, see Grouping Changes to Related Rows with Logical Records . Set Null Sets the value to null if all foreign key columns for the table can accept null values. Does not apply SQL Server 2000. Set Default Sets the value to the default value that is defined for the column if all foreign key columns for the table have defaults defined for them. Does not apply SQL Server 2000 If this solution is not work then please send structure of both table i will try another solution please inform me......... each update and

Pankaj Kumar Choudhary · Answer

hello @Bilawal The error means that you have data in other tables that references the data you are trying to delete. You have two real choices here, you can disable constraints on the table. This usually not a great idea as you can end up with a bad data condition if you're messing with data that relates to other tables, but not know the full extent of your schema and it may suit your purposes: ALTER TABLE [workdemo.no].[dbo].[M06Persons] NOCHECK CONSTRAINT [FK_M02ArticlePersons_M06Persons] Remember to turn the constraint back on after the delete with ALTER TABLE [workdemo.no].[dbo].[M06Persons] WITH CHECK CHECK CONSTRAINT [FK_M02ArticlePersons_M06Persons] The second choice would be to drop and re-add the constraint with the ON DELETE CASCADE option using: ALTER TABLE [workdemo.no].[dbo].[M06Persons] DROP CONSTRAINT [FK_M02ArticlePersons_M06Persons]

Bilawal Niaz · Answer

Brother I Have To Resolve This Problem Kindly Help Me; The DELETE statement conflicted with the REFERENCE constraint "FK_Employee_Department". The conflict occurred in database "Employee", table "dbo.Employee", column 'DeptId'.

Bilawal Niaz · Answer

Plz Give Me Answer Quickly....!

Bilawal Niaz · Answer

Aap bta do gy to ap ky knowledge m kami to nhe ho jae gi? yar phr aap ko khud nhe ata saaf saaf keh do, itna lecture jharny ki kya zrorat hy?

Kiran Roy · Answer

Try to find out yourself. Then you can learn something .

Bilawal Niaz · Answer

m chahta ho k wo male or fe-male don count kar ky btae at a time....!

Bilawal Niaz · Answer

querry run ho gae hy or male count kar k bata rhi hy, magr fe-male nhe count kar rhi? why?

Bilawal Niaz · Answer

"Employee" ky Table my sy employee kitny hy "Gender" ky lihaaz sy? M Ny Ye Pocha Hy, Aap to Pta Nhe Kis Ki queery Bta Rhy Ho

Bilawal Niaz · Answer

Assalam-o-Alaikum.....! koi esi query bta sakta hy jo btae ky, "Employee" ky Table my sy employee kitny hy "Gender" ky lihaaz sy? Like: Male=45 Fe-Male=25 Help Me....!

Bilawal Niaz · Answer

Nothing Happened Sir G......! This Is My Data Base Diagrame; In which Parent Table Is Employee And Child Table Is Department, Now I want To Delete Department Table Record Through "DeptId" Whose Foriegn Key Exist In Employee Table "DeptId", Now Tell Me How I Delete Department Record?

Bilawal Niaz · Answer

sorry Brother I don't Understand That What you Tell Me. Plz Give Me A Breive Answer...! Or Query To Delete Record, We Have Two Tables Employee Or Department, I Want To Delete Department Table Record Whose Foreign Key Exist In Employee Table But Sql We Have No Permission To Delete Department Record. Why? Plz Help Me...!

Pankaj Kumar Choudhary · Answer

hello @Bilawal you can use "A foreign key with cascade delete Option" A foreign key with cascade delete means that if a record in the parent table is deleted, then the corresponding records in the child table will automatically be deleted. This is called a cascade delete in SQL Server. The syntax for creating a foreign key with cascade delete using an ALTER TABLE statement in SQL Server (Transact-SQL) is: ALTER TABLE child_table ADD CONSTRAINT fk_name FOREIGN KEY (child_name) REFERENCES parent_table (parent_col1) ON DELETE CASCADE; after this query when you delete data from parent table then data from child table will be automatically delete. if this is not answer of you query then please write what is actually you want with an suitable exmaple.....

How delete foriegn key in dbms who exist in other table?

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (22)