从3个表中删除只能使其从一个表中删除 [英] Delete from 3 tables only get it to work to delete from one table

问题描述

我有3张桌子

i have 3 tables

EMPLOYEES_TECHNICAL_SKILLS
technical_skill_id(PK/FK)
employee_id(PK/FK)
technical_skill_level_id

TECHNICAL_SKILL_LEVEL
technical_skill_level_id (PK)
skill_level

TECHNICAL_SKILLS
technical_skill_id (PK)
skill_name
skill_type

i我试图删除表中的所有数据

但我只得到它可以删除EMPLOYEES_TECHNICAL_SKILLS列

而在其他表格中我还有数据



我试图在没有任何JOIN的情况下这样做这个。

i am trying to delete all data from the tables
But i only get it to work to delete EMPLOYEES_TECHNICAL_SKILLS Columns
and in the other tables i still have data

I have trying to do it without any JOIN like this.

using (knowitCVdbEntities db = new knowitCVdbEntities())
{
SPWeb theSite = SPControl.GetContextWeb(Context);
SPUser theUser = theSite.CurrentUser;
string strUserName = theUser.LoginName;

var theEmplDatabaseRem = (
from p
in db.EMPLOYEES
where p.username == strUserName
select p).FirstOrDefault();

_emp = theEmplDatabaseRem;

if (_emp != null)
{
LabelPleaseSelectDbListBox.Visible = false;

if (ListBoxDataBase.SelectedItem != null)
{


string vItem = ListBoxDataBase.SelectedItem.Value;
string valueListBox = vItem;
int indexOf = valueListBox.IndexOf("-", StringComparison.Ordinal);
string valueDatabase = valueListBox.Substring(0, indexOf - 1);

EMPLOYEES_TECHNICAL_SKILLS dbRemove = (from p
in db.EMPLOYEES_TECHNICAL_SKILLS
where p.employee_id == _emp.employee_id && p.TECHNICAL_SKILLS.skill_name == valueDatabase
select p).FirstOrDefault();

if (dbRemove != null)
{
_emp.EMPLOYEES_TECHNICAL_SKILLS.Remove(dbRemove);
db.SaveChanges();
ListBoxDataBase.Items.Remove(ListBoxDataBase.SelectedItem);
}

{
LabelPleaseSelectDbListBox.Visible = true;
LabelPleaseSelectDbListBox.Text = "You didn't choose anything";
}

}
// and this result in that i only delete from the first table not the others.

and i have also tried with JOIN like this.
var dbRemove = (from p in db.EMPLOYEES_TECHNICAL_SKILLS.AsEnumerable()
join m in db.TECHNICAL_SKILLS.AsEnumerable() on p.technical_skill_id equals
m.technical_skill_id
join l in db.TECHNICAL_SKILL_LEVEL.AsEnumerable() on p.technical_skill_level_id
equals l.technical_skill_level_id
where p.employee_id == _emp.employee_id && m.skill_name == valueDatabase
select p).FirstOrDefault();

但仍然没有让它工作，我可以错过什么？

and have still not got it to work , what could i have missed?

推荐答案

正如我在评论中写的，要启用级联删除，您需要更改表的定义。 br $> b $ b

As i wrote in my comment, to enable cascade deleting, you need to change a definition of table.

ALTER TABLE [dbo].USER_PHONE  WITH CHECK ADD
CONSTRAINT [FK_Temp_UsersPhone_Users] FOREIGN KEY([USR_ID])
REFERENCES [dbo].[Users] ([USR_ID])
ON DELETE CASCADE
GO

Quote：

ON DELETE {NO ACTION | CASCADE | SET NULL | SET DEFAULT}



指定更改的表中的行发生的操作，如果这些行具有引用关系并且从父表中删除引用的行。默认值为NO ACTION。



无操作



SQL Server数据库引擎引发错误，回滚父表中行的删除操作。

CASCADE



如果该行是，则从引用表中删除相应的行从父表中删除。

ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }

Specifies what action happens to rows in the table that is altered, if those rows have a referential relationship and the referenced row is deleted from the parent table. The default is NO ACTION.

NO ACTION

The SQL Server Database Engine raises an error and the delete action on the row in the parent table is rolled back.
CASCADE

Corresponding rows are deleted from the referencing table if that row is deleted from the parent table.

请参阅： http://msdn.microsoft.com/en-us/library/ms188066.aspx [ ^ ]。



CASCADE DELETE OPTION [ ^ ]

SQL Server中的级联删除 [ ^ ]

See this: http://msdn.microsoft.com/en-us/library/ms188066.aspx[^].

CASCADE DELETE OPTION[^]
Cascading deletes in SQL Server[^]

这篇关于从3个表中删除只能使其从一个表中删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！