One Constraint for Multiple Columns
One Constraint for Multiple Columns
(OP)
I'm working on something for the Inspection area at work. They get these containers of small plastic spheres that get inspected. They measure the diameter with a set of calipers and record it on a paper report.
I'm working on a database for them. They always measure 10 and average them out. So I thought a good table would be:
(where 'unit' is inches or millimeters, and 'Caliper' is the ID of the caliper used).
I also figured it would be a good idea to put a constraint on the diameter columns:
I don't really mind copying and pasting for all 10 columns but I was wondering if there was a way to apply a single type of constraint to multiple columns - something like:
I'm working on a database for them. They always measure 10 and average them out. So I thought a good table would be:
CODE --> SQL
CREATE TABLE ball_dia ( bdia1 NUMERIC(5,3), bdia2 NUMERIC(5,3), bdia3 NUMERIC(5,3), bdia4 NUMERIC(5,3), bdia5 NUMERIC(5,3), bdia6 NUMERIC(5,3), bdia7 NUMERIC(5,3), bdia8 NUMERIC(5,3), bdia9 NUMERIC(5,3), bdia10 NUMERIC(5,3), unit VARCHAR(3), Caliper VARCHAR(10) );
I also figured it would be a good idea to put a constraint on the diameter columns:
CODE --> SQL
bdia1 NUMERIC(5,3) CONSTRAINT bdia1_valid CHECK (bdia1 > 0),
CODE --> SQL
CONSTRAINT LIKE 'bdia%' CHECK ('bdia%' > 0) 
Talk To Other Members
RE: One Constraint for Multiple Columns
Calipers table:
CaliperID
Caliper
Unit
and Balls table:
BallID
CaliperID
BallDiameter
And your application takes care of 10 records in Balls table per CaliperID
Bonus with this approach:
Select AVG(BallDiameter) As MyAvg
From Balls
Where CaliperID = 1234
The 'always' statement in this situation ALWAYS raises red flag for me. No matter what Management says, (almost always
---- Andy
"Hmm...they have the internet on computers now"--Homer Simpson
RE: One Constraint for Multiple Columns
Good point...