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.
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 |
|
|
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_CheckWeighton Haulsinstead of insertasbegin 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 endend |
|
|
|
|
|
|
|