Add Check Constraint in Two Columns
-
Hi All,
I have a database called EmployeeDB and now in this database I have two tables (dbo.employeeDetails) and dbo.couresDetails.
Now In course details table I have only columns Emp_id and IsEmployee. In Emp_id column there is a foreign key and the primary is located on employeedetails table. And IsEmployee column contains only 2 values either true or False.
Now I am trying to insert the data into dbo.couresDetails table. I want to add a check constraint on Isemploee column. As Emp_id has a foreign column the value should be multiple. Where the value of isdelted will be TRUE it should be inserted and where the value is False, only 1 time it should be inserted for a id. Output should be like the following.
Empid Isdeleted
1True -------it should be inserted
1Flase ------- it should be inserted
1 False-- when trying to insert Falsevalue for 1 it should stopped--error
1True -------it should be inserted
---Similarly for for other ids
2True -------it should be inserted
2Flase ------- it should be inserted
2 False-- when trying to insert Falsevalue for 1 it should stopped--error
2True -------it should be inserted
How do I apply check constraint here? Will I use some other function like triggers?
Please help!!
Thanks in advance
-
Please post full table definitions. It is very hard to follow your written explanation.
For example, I find it difficult to understand which table has the IsDeleted column - or if you really meant IsEmployee when you said IsDeleted.
/SG
-
Yes you are right.
Sorry for misguiding.........
-
As Stefan has pointed out, your spec is full of errors and is very difficult to understand. What I suggest you do is to show with examples what you mean by this nonsensical statement: "Where the value of isdelted will be TRUE it should be inserted and where the value is False, only 1 time it should be inserted for a id."
Tables may have rows updated as well as inserted so account for this too. Something like this:
Table dbo.couresDetails contains no rows for EmpID = 1;
Can insert EmpID = 1, IsEmployee = 'False' OR EmpID = 1, IsEmployee = 'True'
Table dbo.couresDetails contains 1 row for EmpID = 1 and IsEmployee = 'False';
Can insert...
Can update this row to...
Table dbo.couresDetails contains 1 row for EmpID = 1 and IsEmployee = 'True';
Can insert...
Can update this row to...
Table dbo.couresDetails contains two rows for EmpID = 1
Where IsEmployee = 'False';
Can insert...
Can update this row to...
Where IsEmployee = 'True';
Can insert...
Can update this row to...
Remember - half a spec, badly written and thought out, can only give you half an answer, badly written and thought out.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
Hi All,
Sorry for misguiding. My requirement is that when emp_id will be 1 and isdeleted = true then we will be able to insert multiple entries. when emp_id will be 1 and isdeleted = false then we will be able to insert only 1 entry. if i am going to insert emp_id will be 1 and isdeleted = false for the 2 time it shows me the error.
Similarly it will be for other ids. (2,3,4...etc)
Please help!
-
people here are help you please mention the table details you used
(i.e)
columns you used in first table and columns you used in second table
and its data type etc...
Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url] -
It is still very hard to understand what you want, but I will take a guess.
I assume the following tables:
use tempdbgo
create table EmployeeDetails (
EmpID int not null,
IsDeleted bit not null
)
go
create table CourseDetails (
CourseID int PRIMARY KEY,
EmpID int
)
And I also assume that these are the rules you want to enforce:
-- Constraint: for every EmpID in EmployeeDetails only one row with IsDeleted=0 is allowed
-- Constraint: CourseDetails must refer to an EmpID with IsDeleted=0
This can not be implemented using standard primary keys and foreign keys. You have to use triggers. Like this:
create trigger tr_EmployeeDetailsCheckPK on EmployeeDetails
for insert, update, delete
as
-- Only one row with IsDeleted is allowed for each Employee
if exists(
select *
from EmployeeDetails
where IsDeleted=0 and EmpId in (select EmpID from inserted)
group by EmpId
having count(*)>1
)
begin
rollback tran
raiserror ('Virtual primary key violation',16,1)
end
-- If deleting the last row for a certain employee, there must be no references from CourseDetails
if exists(
select *
from CourseDetails cd
where EmpId in (select EmpId from deleted)
and not exists(
select *
from EmployeeDetails ed
where IsDeleted=0 and cd.EmpID = ed.EmpID
)
)
begin
rollback tran
raiserror ('Virtual foreign key violation on delete from EmployeeDetails',16,2)
end
go
-- Constraint: CourseDetails must refer to an EmpID with IsDeleted=0
create trigger tr_CourseDetailsCheckPK on CourseDetails
for insert, update, delete
as
if not exists(
select *
from EmployeeDetails
where IsDeleted=0 and EmpId in (select EmpID from inserted)
)
begin
rollback tran
raiserror ('Virtual foreign key violation on insert into CourseDetails',16,1)
end
go
Some test code:
-- All these should be allowedinsert into EmployeeDetails (EmpID, IsDeleted) values (1,0)
insert into EmployeeDetails (EmpID, IsDeleted) values (1,1)
insert into EmployeeDetails (EmpID, IsDeleted) values (1,1)
insert into EmployeeDetails (EmpID, IsDeleted) values (2,0)
insert into EmployeeDetails (EmpID, IsDeleted) values (3,0)
insert into EmployeeDetails (EmpID, IsDeleted) values (3,1)
insert into CourseDetails (CourseID, EmpID) values (100, 1)
insert into CourseDetails (CourseID, EmpID) values (200, 1)
insert into CourseDetails (CourseID, EmpID) values (300, 2)
go
-- All of these should be forbidden
-- Create several Employees with the same id where IsDeleted=0
update EmployeeDetails set IsDeleted=0 where EmpID=3
go
insert into EmployeeDetails (EmpID, IsDeleted) values (3,0)
go
-- Create dangling reference
delete from EmployeeDetails where EmpID=2
go
update EmployeeDetails set IsDeleted=1 where EmpID=2
go
-- Insert dangling reference
insert into CourseDetails (CourseID, EmpID) values (400,4)
go
select * from EmployeeDetails
select * from CourseDetails
Hope this helps
/SG
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply