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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

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

Starting Member

United Kingdom
8 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?

Patron Saint of Lost Yaks

30421 Posts

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

Go to Top of Page

Aged Yak Warrior

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

	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
						join	Hauls h on 
							t.Id = h.TrainId
						join	inserted i on 
							t.Id = i.TrainId
					        by	t.Id
					) d(TrainId, IsExceeded)
				where	IsExceeded = 1
		raiserror('A train in this batch exceeds max', 16, 1);
	end else
		insert into Hauls
			select TrainId, ItemWeight from inserted

Edited by - nathans on 07/26/2014 16:12:21
Go to Top of Page
  Previous Topic Topic Next 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.02 seconds. Powered By: Snitz Forums 2000