Check Constraint? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Check Constraint?

Author

Topic

scullee
Posting Yak Master

103 Posts

Posted - 2003-05-11 : 21:11:49

I am having a data integrity issue with my system that i am developing and think i need a Contraint to keep the data intact.

I have 2 tables device and vehicle and there is a many to many relationship between them. Eg The one device may have been in many different vehicles and each vehicle may have had many different devices. The problem is that each device can only be "ACTIVE" (Device_Vehicle.Status) in any car at the one time. Because there can be more than one inactive Device_Vehicle entry for each combination (for historical reasons), i couldnt put the Status field as part of the PK to inforce this. The only way i can see to do it is with a check constraint.

I dont have much ie no, experience with check constraints so im not sure how to do this. What i need is something that does the following.

For the New (or edited) Device_ID and Vehicle_ID make sure that there is not another entry where the Vehicle_ID or Device_ID is involved, and the status is "ACTIVE"

Can anyone help?

Edited by - scullee on 05/11/2003 21:15:02

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-05-11 : 23:26:13

You can create a unique constraint with the NOCHECK option, this will create a Unique constraint only for future modified or new values, it will not fail b/c old values violate the constraint. Syntax (assuming you're working w/ an existing table):
ALTER TABLE tblname WITH NOCHECK ADD CONSTRAINT constraintname UNIQUE NONCLUSTERED(col1,col2,col3)

Sarah Berger MCSD

scullee
Posting Yak Master

103 Posts

Posted - 2003-05-11 : 23:49:45

That will work for the current data but we need the ability to add more "INACTIVE" records to the table as devices are moved around between cars when the cars are sold or the devices are faulty.

The constraint only has to work on "ACTIVE" records.

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-05-11 : 23:58:33

scullee,

Just a thought..

Consider a completely new table for your inactive devices...

DavidM

"SQL-3 is an abomination.."

scullee
Posting Yak Master

103 Posts

Posted - 2003-05-12 : 00:11:57

I would prefer not separate them as i would rather not have to look into 2 places to get the history for the device.

But if thats the best way i might have to look at it :(

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-05-12 : 01:06:14

The only other way is to create a UDF and use it in a table level check constraint...

Something like....


CREATE Function IsActiveDeviceUnique
(
@DeviceID INT,
@VehicleID INT
)
RETURNS BIT
AS
BEGIN
DECLARE @BIT BIT
SET @BIT = 0
IF NOT EXISTS(SELECT 1 FROM DeviceVehicle
		WHERE DeviceID = @DeviceID and VehicleID = @VehicleID And Active = 1 
		HAVING COUNT(*) > 1)
	SET @BIT = 1
RETURN @BIt
END
GO

DavidM

"SQL-3 is an abomination.."

Edited by - byrmol on 05/12/2003 01:07:31

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-12 : 09:21:03

Look at this:

create table test

(VeichleID int not null,
DeviceID int not null,
Status varchar(10) not null,
CheckField AS CASE WHEN Status = 'ACTIVE' THEN VeichleID else DeviceID END,

CONSTRAINT PK primary key (VeichleID, DeviceID),
CONSTRAINT PK2 unique (VeichleID, Status, Checkfield))

GO

-- these all work:

insert into test
values (1,1, 'ACTIVE')

insert into test
values (1,2,'INACTIVE')

insert into test
values (2,1,'ACTIVE')

insert into test
values (2,2,'INACTIVE')

insert into test
values (2,3,'INACTIVE')

select * from test

-- These don't work:

insert into test
values (1,3, 'ACTIVE')

insert into test
values (2,4, 'ACTIVE')

select * from test

DROP TABLE test

You guys know I love these kinds of problems ... very fun to work out logically ! And this is without triggers of any kind, just using the RDMS model.

Let me know what you think. I can explain more if it doesn't make sense to anyone. But computed columns (which surprisingly are allowed to participate in constraints) really make stuff like this easy to do without triggers.

Thoughts?

- Jeff

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2003-05-12 : 11:59:59

>>I have 2 tables device and vehicle and there is a many to many relationship between them. <<

Exactly! A check constraint is not the right tool for a relationship among entities. Try this:

CREATE TABLE Devices
(device_id INTEGER NOT NULL PRIMARY KEY,
...);

CREATE TABLE Vehicles
(vin CHAR(17) NOT NULL PRIMARY KEY,
...);

CREATE TABLE DeviceAssignments
(device_id INTEGER NOT NULL
REFERENCES Devices(device_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
vin CHAR(17) NOT NULL
REFERENCES Vehicles(vin)
ON DELETE CASCADE
ON UPDATE CASCADE,
assignment_date DATETIME DEFAULT CURRENT_TIMESTAMP,
..
PRIMARY KEY (device_id, vin));

I assume you wantot use the ISO Standard VIN and have to track other information about each device assignment, like date, authorization, etc.

--CELKO--
Joe Celko, SQL Guru

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-12 : 12:55:19

Joe -- That's definitely the way to go, and I'm pretty sure the way his DB is currently set up ... that doesn't really address his problem, though.

The problem is, within this many-to-many table, only 1 device can be set as ACTIVE, while there can be many INACTIVE devices (or some other status other than Active).

That's what my DDL addresses.

- Jeff

chadmat
The Chadinator

1974 Posts

Posted - 2003-05-12 : 13:16:24

quote:

...there can be more than one inactive Device_Vehicle entry for each combination...

Doesn't this requirement preclude both of those solutions?

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-12 : 14:12:20

Look at my example more closely. From what I gather (and, of course, I could be completely wrong):

1. He already has the exact table Joe demonstrated -- a many-to-many join between Veichles and Devices.

2. BUT -- he wants to make sure that there is only 1 device considered ACTIVE in that many-to-many table for each veichle. Whereas there may be many INACTIVE devices for each veichle in the same table.

Scullee -- helps us out here, clear it up.

But whether or not my interpretation is 100% correct, the concept behind what I demonstrated should be able to be applied. If you haven't cut and pasted it and tried it out, do so and then it might clear some things up. It is a cool way to do a "conditional" constraint on a table using a calculated field w/o needing triggers. I can explain further if needed, just ask.

- Jeff

chadmat
The Chadinator

1974 Posts

Posted - 2003-05-12 : 14:26:45

Maybe I'm missunderstanding, but I was thinking he was saying that the same device can be in the same car (Just not more than 1 active).

That would not work with the composite primary Key. But, I may have missinterpretted.

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-12 : 14:40:41

AAAhh! I see what you're saying now, Chad ... very good point ! We'll have to find out.

- Jeff

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-05-12 : 18:18:58

Jeff, Joe,

The important line in his problem is...

quote:

Because there can be more than one inactive Device_Vehicle entry for each combination (for historical reasons), i couldnt put the Status field as part of the PK to inforce this.

It is a rule that a RDBMS should be able to enforce declaratively. With SQL Server, a User Defined function is the only way I know how..

DavidM

"SQL-3 is an abomination.."

Edited by - byrmol on 05/12/2003 18:19:49

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-12 : 18:36:58

David --

Please paste in my code in check it out. It handles that. look at it closely. there's more than meets the eye. look at the definition of the computed column "CheckField". look at the sample insert statements as well. Try your own insert statements to "break it".

It does EXACTLY what your UDF does, without a UDF or a trigger.

To repeat from one of my posts in this thread: using a computed column as part of a unique constraint allows you to do surprisngly complex constraints WITHOUT the need for triggers.

- Jeff

Edited by - jsmith8858 on 05/12/2003 18:44:28

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-05-12 : 19:01:31

Jeff,

It doesn't work the way I read the question...

Here is my take...

Vehicle "V" Has a Device "D".

A) VehicleDevice "VD" is allowed 1 (And only One) "active" Status.

B) VehicleDevice "VD" is also allowed to have 1 (or many?) "inactive" statuses.

Using your DDL (without the Primary Key), this is what (I think) should happen


--OK for condition A
insert into test 
values (1,1, 'ACTIVE') 

--OK for condition B (Your code denies this entry)
insert into test 
values (1,1, 'INACTIVE') 

--OK for condition B??
insert into test 
values (1,1, 'INACTIVE') 

--NOT OK for condition A
insert into test 
values (1,1, 'ACTIVE')

Does that make sense or am I barking up the wrong tree?

DavidM

"SQL-3 is an abomination.."

Edited by - byrmol on 05/12/2003 20:03:25

scullee
Posting Yak Master

103 Posts

Posted - 2003-05-12 : 20:11:42

Gee, i go away for a nights sleep and come back to this. Thanks for the discussion.

The Rule is

We have a Table of Devices and a Table of Vehicles.
There is a relationship between the two done through the Device_Vehicle table.

Say vehicle "ABC" and Device "123"

Say a Device is installed in a Vehicle, one entry is created in the table to link the 2.

Eg
Device Vehicle Status
123 ABC ACTIVE

Then say the Device goes dodgy and has to be replaced. The first entry is marked as "INACTIVE" and a new device 789 is installed. The data will look like this.

Device Vehicle Status
123 ABC INACTIVE
789 ABC ACTIVE

Then the original device is repaired and goes back in the car

Device Vehicle Status
123 ABC INACTIVE
789 ABC INACTIVE
123 ABC ACTIVE

I need the system to make sure that at any time, device 123 cant be ACTIVE in more than one vehicle. So if someone tries to insert device 123 into Vehicle ZYX the system will return an error. But if device 789 is inserted into another vehicle it will be o.k.

I hope this clarifies the situation for you all.

My current thinking is to maybe use a trigger to reject the insert if the test fails.

Edited by - scullee on 05/12/2003 20:52:13

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-05-12 : 20:34:34

sculle,

There are way too many mistakes in your last post.. Have another read and edit it.

DavidM

"SQL-3 is an abomination.."

scullee
Posting Yak Master

103 Posts

Posted - 2003-05-12 : 20:53:27

You were right, there were a few errors there. Damn phone rang before i could proof read.

I have fixed the post so it should be right.

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-05-12 : 22:43:56

scullee,

That is a better explanation!

Jeff had the right idea but couldn't make sense of your requirements.

So, only a slight modification of his original code is needed.

In the "CASE" expression of the computed column, swap VehicleID and DeviceID. Then change the Unique contraint from VehicleID to DeviceID...

DavidM

"SQL-3 is an abomination.."

scullee
Posting Yak Master

103 Posts

Posted - 2003-05-13 : 00:08:16

I was sitting there puzzled at how this works because it didnt seem complete.

It works for most of the cases but where it falls down is when there a device is removed and replaced in a car twice. Eg a Second Inactive value for the same car is needed. So the rules are as Follows

A device can be INACTIVE Any number of times in any combination of Device and Vehicle but it cannot be Active in 2 vehicles at the same time.

so this is o.k.
Device Vehicle Status
123 ABC INACTIVE
789 ABC INACTIVE
123 ABC ACTIVE

This is fine too
Device Vehicle Status
123 ABC INACTIVE
789 ABC INACTIVE
123 ABC INACTIVE
789 ABC INACTIVE
123 ABC ACTIVE

But this is not
Device Vehicle Status
123 ABC INACTIVE
789 ABC INACTIVE
123 ABC INACTIVE
123 ZYX ACTIVE
123 ABC ACTIVE

I know half of the problem here is me trying to explaining it but its damn complex and only a small part of a big system.

This does happen with the devices in the real world, expecially with the testing and development units that are in and out of the development and test fleet vehicles. These must be tracked correctly or it causes me pain in other parts of the system.

I think im back to the UDF like this

CREATE Function IsActiveDeviceUnique
(
@DeviceID INT,
@VehicleID INT
)
RETURNS BIT
AS
BEGIN
DECLARE @BIT BIT
SET @BIT = 0
IF NOT EXISTS(SELECT 1 FROM DeviceVehicle WHERE DeviceID = @DeviceID and Active = 1)
SET @BIT = 1
RETURN @BIt
END
GO

So before each record is inserted make sure that there is no other record where this device is active then then reject the insert.

Or as a last resort rip the inactive values out into anothing table for history :(

Edited by - scullee on 05/13/2003 00:15:46

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources