SQL Server constraint on delete
1
I created this table:
CREATE TABLE OfficialEmployee
(
EID Integer not null foreign key references Employee(EID),
StartWorkingDate date not null ,
Degree char(20) not null,
Department char(50) not null,
DID Integer not null foreign key references Department(DID)
);
which references the table Employee by the DID:
CREATE TABLE Employee
(
EID Integer not null PRIMARY KEY,
FirstName char(30) not null,
LastName char(30) not null,
BirthDate date not null,
CellPhoneNumber Integer not null,
City char(30) not null,
StreetName char(30) not null,
Number Integer not null,
Door Integer not null
);
CREATE TABLE Department
(
DID Integer not null PRIMARY KEY,
Name char(30) not null,
Description char(200) not null,
Manage Integer not null FOREIGN KEY REFERENCES OfficialEmployee(EID)
);
and I want to make a constraint that when OfficialEmployee is deleted, the record of his in Employee will be deleted too only if he is not a manager (in the Department table) else it will (using cascade).
How can I do that?
(I'm using SQL Server)
sql
sql-server foreign-keys edited Nov 25 '18 at 7:29
marc_s
573k12811071255
asked Nov 24 '18 at 22:30
user111434user111434
113
add a comment |
1
I created this table:
CREATE TABLE OfficialEmployee
(
EID Integer not null foreign key references Employee(EID),
StartWorkingDate date not null ,
Degree char(20) not null,
Department char(50) not null,
DID Integer not null foreign key references Department(DID)
);
which references the table Employee by the DID:
CREATE TABLE Employee
(
EID Integer not null PRIMARY KEY,
FirstName char(30) not null,
LastName char(30) not null,
BirthDate date not null,
CellPhoneNumber Integer not null,
City char(30) not null,
StreetName char(30) not null,
Number Integer not null,
Door Integer not null
);
CREATE TABLE Department
(
DID Integer not null PRIMARY KEY,
Name char(30) not null,
Description char(200) not null,
Manage Integer not null FOREIGN KEY REFERENCES OfficialEmployee(EID)
);
and I want to make a constraint that when OfficialEmployee is deleted, the record of his in Employee will be deleted too only if he is not a manager (in the Department table) else it will (using cascade).
How can I do that?
(I'm using SQL Server)
sql
sql-server foreign-keys edited Nov 25 '18 at 7:29
marc_s
573k12811071255
asked Nov 24 '18 at 22:30
user111434user111434
113
You haven't said which SQL you are using, but you could have a look at: How to delete rows in tables that contain foreign keys to other tables
– lurker
Nov 24 '18 at 22:36
But in that post i,t shows how to delete any time a record is deleted,I want to delete "only if" something
– user111434
Nov 24 '18 at 22:42
2
What you're trying to accomplish isn't going to work with constraints, but is possible with table triggers.
– Eric Brandt
Nov 24 '18 at 23:15
Side note: I'd strongly recommend not to usechar(30)etc. datatypes -char(n)will always pad the column data with spaces, to the defined length - so you always have 30 (or 50, or 200) characters in a column like this - even if your value is only 3 characters long. You should use varchar(n) instead
– marc_s
Nov 25 '18 at 7:58
add a comment |
1
1
1
I created this table:
CREATE TABLE OfficialEmployee
(
EID Integer not null foreign key references Employee(EID),
StartWorkingDate date not null ,
Degree char(20) not null,
Department char(50) not null,
DID Integer not null foreign key references Department(DID)
);
which references the table Employee by the DID:
CREATE TABLE Employee
(
EID Integer not null PRIMARY KEY,
FirstName char(30) not null,
LastName char(30) not null,
BirthDate date not null,
CellPhoneNumber Integer not null,
City char(30) not null,
StreetName char(30) not null,
Number Integer not null,
Door Integer not null
);
CREATE TABLE Department
(
DID Integer not null PRIMARY KEY,
Name char(30) not null,
Description char(200) not null,
Manage Integer not null FOREIGN KEY REFERENCES OfficialEmployee(EID)
);
and I want to make a constraint that when OfficialEmployee is deleted, the record of his in Employee will be deleted too only if he is not a manager (in the Department table) else it will (using cascade).
How can I do that?
(I'm using SQL Server)
sql
sql-server foreign-keys edited Nov 25 '18 at 7:29
marc_s
573k12811071255
asked Nov 24 '18 at 22:30
user111434user111434
113
I created this table:
CREATE TABLE OfficialEmployee
(
EID Integer not null foreign key references Employee(EID),
StartWorkingDate date not null ,
Degree char(20) not null,
Department char(50) not null,
DID Integer not null foreign key references Department(DID)
);
which references the table Employee by the DID:
CREATE TABLE Employee
(
EID Integer not null PRIMARY KEY,
FirstName char(30) not null,
LastName char(30) not null,
BirthDate date not null,
CellPhoneNumber Integer not null,
City char(30) not null,
StreetName char(30) not null,
Number Integer not null,
Door Integer not null
);
CREATE TABLE Department
(
DID Integer not null PRIMARY KEY,
Name char(30) not null,
Description char(200) not null,
Manage Integer not null FOREIGN KEY REFERENCES OfficialEmployee(EID)
);
and I want to make a constraint that when OfficialEmployee is deleted, the record of his in Employee will be deleted too only if he is not a manager (in the Department table) else it will (using cascade).
How can I do that?
(I'm using SQL Server)
sql
sql-server foreign-keys sql
sql-server foreign-keys edited Nov 25 '18 at 7:29
marc_s
573k12811071255
asked Nov 24 '18 at 22:30
user111434user111434
113
edited Nov 25 '18 at 7:29
marc_s
573k12811071255
asked Nov 24 '18 at 22:30
user111434user111434
113
edited Nov 25 '18 at 7:29
marc_s
573k12811071255
edited Nov 25 '18 at 7:29
marc_s
573k12811071255
edited Nov 25 '18 at 7:29
marc_s
573k12811071255
573k12811071255
asked Nov 24 '18 at 22:30
user111434user111434
113
asked Nov 24 '18 at 22:30
user111434user111434
113
asked Nov 24 '18 at 22:30
user111434user111434
113
113
You haven't said which SQL you are using, but you could have a look at: How to delete rows in tables that contain foreign keys to other tables
– lurker
Nov 24 '18 at 22:36
But in that post i,t shows how to delete any time a record is deleted,I want to delete "only if" something
– user111434
Nov 24 '18 at 22:42
2
What you're trying to accomplish isn't going to work with constraints, but is possible with table triggers.
– Eric Brandt
Nov 24 '18 at 23:15
Side note: I'd strongly recommend not to usechar(30)etc. datatypes -char(n)will always pad the column data with spaces, to the defined length - so you always have 30 (or 50, or 200) characters in a column like this - even if your value is only 3 characters long. You should use varchar(n) instead
– marc_s
Nov 25 '18 at 7:58
add a comment |
You haven't said which SQL you are using, but you could have a look at: How to delete rows in tables that contain foreign keys to other tables
– lurker
Nov 24 '18 at 22:36
But in that post i,t shows how to delete any time a record is deleted,I want to delete "only if" something
– user111434
Nov 24 '18 at 22:42
2
What you're trying to accomplish isn't going to work with constraints, but is possible with table triggers.
– Eric Brandt
Nov 24 '18 at 23:15
Side note: I'd strongly recommend not to usechar(30)etc. datatypes -char(n)will always pad the column data with spaces, to the defined length - so you always have 30 (or 50, or 200) characters in a column like this - even if your value is only 3 characters long. You should use varchar(n) instead
– marc_s
Nov 25 '18 at 7:58
You haven't said which SQL you are using, but you could have a look at: How to delete rows in tables that contain foreign keys to other tables
– lurker
Nov 24 '18 at 22:36
You haven't said which SQL you are using, but you could have a look at: How to delete rows in tables that contain foreign keys to other tables
– lurker
Nov 24 '18 at 22:36
But in that post i,t shows how to delete any time a record is deleted,I want to delete "only if" something
– user111434
Nov 24 '18 at 22:42
But in that post i,t shows how to delete any time a record is deleted,I want to delete "only if" something
– user111434
Nov 24 '18 at 22:42
2
2
What you're trying to accomplish isn't going to work with constraints, but is possible with table triggers.
– Eric Brandt
Nov 24 '18 at 23:15
What you're trying to accomplish isn't going to work with constraints, but is possible with table triggers.
– Eric Brandt
Nov 24 '18 at 23:15
Side note: I'd strongly recommend not to use
char(30) etc. datatypes - char(n) will always pad the column data with spaces, to the defined length - so you always have 30 (or 50, or 200) characters in a column like this - even if your value is only 3 characters long. You should use varchar(n) instead– marc_s
Nov 25 '18 at 7:58
Side note: I'd strongly recommend not to use
char(30) etc. datatypes - char(n) will always pad the column data with spaces, to the defined length - so you always have 30 (or 50, or 200) characters in a column like this - even if your value is only 3 characters long. You should use varchar(n) instead– marc_s
Nov 25 '18 at 7:58
add a comment |
2 Answers
2
active
oldest
votes
0
In SQL Server, you have to remember that deleted (and inserted can have more than one record.
So, do this using JOIN:
CREATE TRIGGER deleteEmployeeTrigger
ON dbo.OfficialEmployee
FOR DELETE AS
BEGIN
DELETE e
FROM dbo.Employee e JOIN
dbo.deleted dd
ON e.EID = dd.EID LEFT JOIN
dbo.department d
ON d.manage = dd.EID
WHERE d.manage IS NULL;
END;
answered Nov 25 '18 at 13:17
Gordon LinoffGordon Linoff
766k35297401
add a comment |
0
Assuming that the description you want to avoid is 'Manager', here is a simple trigger.
CREATE TRIGGER deleteEmployeeTrigger
ON dbo.OfficialEmployee
FOR DELETE
AS
DELETE FROM dbo.Employee
WHERE EID IN (SELECT deleted.EID FROM deleted)
AND NOT EXISTS (SELECT * FROM Department
WHERE Manage = deleted.EID AND Description = 'Manager')
answered Nov 25 '18 at 2:59
Tina SebastianTina Sebastian
362
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53462920%2fsql-server-constraint-on-delete%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
In SQL Server, you have to remember that deleted (and inserted can have more than one record.
So, do this using JOIN:
CREATE TRIGGER deleteEmployeeTrigger
ON dbo.OfficialEmployee
FOR DELETE AS
BEGIN
DELETE e
FROM dbo.Employee e JOIN
dbo.deleted dd
ON e.EID = dd.EID LEFT JOIN
dbo.department d
ON d.manage = dd.EID
WHERE d.manage IS NULL;
END;
answered Nov 25 '18 at 13:17
Gordon LinoffGordon Linoff
766k35297401
add a comment |
0
In SQL Server, you have to remember that deleted (and inserted can have more than one record.
So, do this using JOIN:
CREATE TRIGGER deleteEmployeeTrigger
ON dbo.OfficialEmployee
FOR DELETE AS
BEGIN
DELETE e
FROM dbo.Employee e JOIN
dbo.deleted dd
ON e.EID = dd.EID LEFT JOIN
dbo.department d
ON d.manage = dd.EID
WHERE d.manage IS NULL;
END;
answered Nov 25 '18 at 13:17
Gordon LinoffGordon Linoff
766k35297401
add a comment |
0
0
0
In SQL Server, you have to remember that deleted (and inserted can have more than one record.
So, do this using JOIN:
CREATE TRIGGER deleteEmployeeTrigger
ON dbo.OfficialEmployee
FOR DELETE AS
BEGIN
DELETE e
FROM dbo.Employee e JOIN
dbo.deleted dd
ON e.EID = dd.EID LEFT JOIN
dbo.department d
ON d.manage = dd.EID
WHERE d.manage IS NULL;
END;
answered Nov 25 '18 at 13:17
Gordon LinoffGordon Linoff
766k35297401
In SQL Server, you have to remember that deleted (and inserted can have more than one record.
So, do this using JOIN:
CREATE TRIGGER deleteEmployeeTrigger
ON dbo.OfficialEmployee
FOR DELETE AS
BEGIN
DELETE e
FROM dbo.Employee e JOIN
dbo.deleted dd
ON e.EID = dd.EID LEFT JOIN
dbo.department d
ON d.manage = dd.EID
WHERE d.manage IS NULL;
END;
answered Nov 25 '18 at 13:17
Gordon LinoffGordon Linoff
766k35297401
answered Nov 25 '18 at 13:17
Gordon LinoffGordon Linoff
766k35297401
answered Nov 25 '18 at 13:17
Gordon LinoffGordon Linoff
766k35297401
answered Nov 25 '18 at 13:17
Gordon LinoffGordon Linoff
766k35297401
766k35297401
add a comment |
add a comment |
0
Assuming that the description you want to avoid is 'Manager', here is a simple trigger.
CREATE TRIGGER deleteEmployeeTrigger
ON dbo.OfficialEmployee
FOR DELETE
AS
DELETE FROM dbo.Employee
WHERE EID IN (SELECT deleted.EID FROM deleted)
AND NOT EXISTS (SELECT * FROM Department
WHERE Manage = deleted.EID AND Description = 'Manager')
answered Nov 25 '18 at 2:59
Tina SebastianTina Sebastian
362
add a comment |
0
Assuming that the description you want to avoid is 'Manager', here is a simple trigger.
CREATE TRIGGER deleteEmployeeTrigger
ON dbo.OfficialEmployee
FOR DELETE
AS
DELETE FROM dbo.Employee
WHERE EID IN (SELECT deleted.EID FROM deleted)
AND NOT EXISTS (SELECT * FROM Department
WHERE Manage = deleted.EID AND Description = 'Manager')
answered Nov 25 '18 at 2:59
Tina SebastianTina Sebastian
362
add a comment |
0
0
0
Assuming that the description you want to avoid is 'Manager', here is a simple trigger.
CREATE TRIGGER deleteEmployeeTrigger
ON dbo.OfficialEmployee
FOR DELETE
AS
DELETE FROM dbo.Employee
WHERE EID IN (SELECT deleted.EID FROM deleted)
AND NOT EXISTS (SELECT * FROM Department
WHERE Manage = deleted.EID AND Description = 'Manager')
answered Nov 25 '18 at 2:59
Tina SebastianTina Sebastian
362
Assuming that the description you want to avoid is 'Manager', here is a simple trigger.
CREATE TRIGGER deleteEmployeeTrigger
ON dbo.OfficialEmployee
FOR DELETE
AS
DELETE FROM dbo.Employee
WHERE EID IN (SELECT deleted.EID FROM deleted)
AND NOT EXISTS (SELECT * FROM Department
WHERE Manage = deleted.EID AND Description = 'Manager')
answered Nov 25 '18 at 2:59
Tina SebastianTina Sebastian
362
edited Nov 25 '18 at 15:26
answered Nov 25 '18 at 2:59
Tina SebastianTina Sebastian
362
answered Nov 25 '18 at 2:59
Tina SebastianTina Sebastian
362
answered Nov 25 '18 at 2:59
Tina SebastianTina Sebastian
362
362
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53462920%2fsql-server-constraint-on-delete%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
You haven't said which SQL you are using, but you could have a look at: How to delete rows in tables that contain foreign keys to other tables
– lurker
Nov 24 '18 at 22:36
But in that post i,t shows how to delete any time a record is deleted,I want to delete "only if" something
– user111434
Nov 24 '18 at 22:42
2
What you're trying to accomplish isn't going to work with constraints, but is possible with table triggers.
– Eric Brandt
Nov 24 '18 at 23:15
Side note: I'd strongly recommend not to use
char(30)etc. datatypes -char(n)will always pad the column data with spaces, to the defined length - so you always have 30 (or 50, or 200) characters in a column like this - even if your value is only 3 characters long. You should use varchar(n) instead– marc_s
Nov 25 '18 at 7:58