×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

One Constraint for Multiple Columns

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:

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)
); 
(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:

CODE --> SQL

bdia1 NUMERIC(5,3) CONSTRAINT bdia1_valid CHECK (bdia1 > 0), 
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:

CODE --> SQL

CONSTRAINT LIKE 'bdia%' CHECK ('bdia%' > 0) 

RE: One Constraint for Multiple Columns

One way to do it would be to have 2 tables:

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:

Quote (AbidingDude)

They always measure 10 and average them out.
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 smile) they change their mind: "We measure 10 balls, but that's not enough. From now on we need to measure 12, or 15 of them" or "to save time and money, from now on we will measure only 8 balls instead."

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: One Constraint for Multiple Columns

(OP)

Quote (Andrzejek)

The 'always' statement in this situation ALWAYS raises red flag for me. No matter what Management says, (almost always :)) they change their mind...

Good point...

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close