SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Check Constraint
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ChrisjBrown
Starting Member

United Kingdom
7 Posts

Posted - 07/26/2014 :  13:55:05  Show Profile  Reply with Quote
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

Sweden
30282 Posts

Posted - 07/26/2014 :  15:09:33  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
		)
	)



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

nathans
Aged Yak Warrior

USA
938 Posts

Posted - 07/26/2014 :  15:27:39  Show Profile  Reply with Quote
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

Edited by - nathans on 07/26/2014 16:12:21
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000