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
 General SQL Server Forums
 New to SQL Server Programming
 Check Constraint

Author  Topic 

ChrisjBrown
Starting Member

8 Posts

Posted - 2014-07-26 : 13:55:05
I am relatively new to SQL and as a project I have been asked to create the SQL for a simple database to record train details. I want to implement a check constraint which will prevent data from being inserted into a table if the weight of the train is more than the maximum towing weight of the locomotive. FOr instance, I need to add the unladen weight and maximum capacity of each wagon (located in the wagon type table) and compare it against the locomotive maximum pulling weight (the locomotive class table). I have the following SQL but it will not work:

check((select SUM(fwt.unladen_weight+fwt.maximum_payload) from
hauls as h,freight_wagon as fw,freight_wagon_type as fwt,train as t where
h.freight_wagon_serial_number = fw.freight_wagon_serial_number and
fw.freight_wagon_type = fwt.freight_wagon_type and
h.train_number = t.train_number) <
(select lc.maximum_towing_weight from locomotive_class as lc,locomotive as l,train as t where
lc.locomotive_class = l.locomotive_class and l.locomotive_serial_number = t.locomotive_serial_number))

The hauls table is where the constraint has been placed and is the intermediary table between train and freight wagon.

I may not have explained this very well; but in short, i need to compare the sum of two values in one table against a values located in another table...At present I keep gettign a message telling me the sub query cannot return more than one row.

Can someone please help or point out to me where I am going wrong?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-07-26 : 15:09:33
[code]CHECK (
(
SELECT SUM(fwt.unladen_weight+fwt.maximum_payload)
FROM hauls as h,
freight_wagon as fw,
freight_wagon_type as fwt,
train as t
WHERE h.freight_wagon_serial_number = fw.freight_wagon_serial_number
AND fw.freight_wagon_type = fwt.freight_wagon_type
AND h.train_number = t.train_number
)
<
(
SELECT SUM(lc.maximum_towing_weight)
from locomotive_class as lc,
locomotive as l,
train as t
WHERE lc.locomotive_class = l.locomotive_class
AND l.locomotive_serial_number = t.locomotive_serial_number
)
)[/code]


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

nathans
Aged Yak Warrior

938 Posts

Posted - 2014-07-26 : 15:27:39
I think we will have to wrap that check in a function to be able to reference in the constraint... or did mssql grow up and finally add subquery support? :)

Maybe a trigger would work well here? Heres a grossly over simplified example:


create trigger tr_CheckWeight
on Hauls
instead of insert
as
begin

if exists ( select 1
from ( select t.Id, case when isnull(sum(h.ItemWeight), 0) + sum(i.ItemWeight) > max(MaxWeight) then 1 else 0 end
from Train t
left
join Hauls h on
t.Id = h.TrainId
join inserted i on
t.Id = i.TrainId
group
by t.Id
) d(TrainId, IsExceeded)
where IsExceeded = 1
)
begin
raiserror('A train in this batch exceeds max', 16, 1);
end else
begin

insert into Hauls
select TrainId, ItemWeight from inserted
end
end
Go to Top of Page
   

- Advertisement -