Constraining a column based on another column

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.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Constraining a column based on another column

Author

Topic

mrhodes
Starting Member

2 Posts

Posted - 2007-03-26 : 12:10:48

I am attempting to create a database - I wanted to allow multiple phone numbers, emails, and addresses for each family - so I am attempting to set it up so that there are tables for each and they attach back to a family via a foriegn key (Family ID). I was to set one as the primary (i.e. primary email address - not primary key) so I have a column allowing a bit input for primary.

Is there a way to constrain the table so that only one of each set of rows with the same family IDs can have primary set to 1 (true). I know I could do this on the front end by running a query and checking to make sure that only 1 record is returned - and if not equal to one generating an error message to the user that they can only mark one of the addreses as primary - but I don't know how to do it in the design of the table itself.

MS SQL 2000 or MS SQL 2005
(note: I have read through some of the posts and replies - and If all you want to do is bash me for my ignorance as you infer, but not prove, supierior knowledge - please don't bother to reply. Thanks.)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-26 : 12:18:23

SELECT FamilyID, SUM(IsPrimary) AS Primaries
FROM Table1
GROUP BY FamilyID
HAVING SUM(IsPrimary) <> 1

This gets you all families NOT having a primary email at all, and the families with 2 or more primaries.

Peter Larsson
Helsingborg, Sweden

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-26 : 12:44:53

I think he is looking for a trigger here...but if you have sproc only access to the data, you could do it there

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-26 : 12:47:26

You'll need to use a trigger. Here's a very simple example,

create trigger IsPrimaryTrigger on yourtable
for insert, update
as
if exists (select * 
	from yourtable 
	inner join inserted on inserted.FamilyID = yourtable.FamilyID and inserted.yourtableID > yourtable.yourtableID 
	where inserted.IsPrimary = 1 and yourtable.IsPrimary = 1)
begin
	rollback
end

mrhodes
Starting Member

2 Posts

Posted - 2007-03-26 : 15:54:23

Thanks - not sure I understand but I will research triggers.

Basically I want it to make the first address that is added the primary - whenever a user edits or adds (inserts or updates) an address they can click a check box that indicates if it is primary - if they do that - I want the SQL table to know to uncheck any addresses that are under the same FamilyID. Also if they enter an address an do not check the box - but it is the only address they have then the atabase would still mark it as primary. I also want to insure that if they delete the priamry address they are asked to choose another address as the primary (if multiple addresses exist - or if only one address is left it is automatically set as primary - and if they are trying to delete the last address it wont allow them.

I am creating this with a web interface (coldfusion) - so I could have the front-in handle all of this - but I was hoping there was a better way to do it on the table itself in SQL (less likely to mess up - I would think) - I just purchased SQL 2005 (currently have SQL 2000) - so if SQL 2005 is required, I can go to that - probably will either way.

(note: As a "newbie", I have read through some of the posts and replies and have found many helpful explanations- However, if all you want to do is bash me for my ignorance as you infer, but not prove, supierior knowledge - please don't bother to reply. Thanks.)

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-26 : 16:59:02

I'd say that you should go with a trigger to enforce the data integrity, ie. make sure you never get two primary addresses. As far as everything else goes, that's application logic, which you could do in the front-end, or in a stored procedure.

(note: people here don't bash others for ignorance, they do however bash them when they do not provide enough information about their problem, even after repeated requests. You haven't done that, so you won't be bashed

)

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-27 : 09:42:41

quote:
Originally posted by mrhodesHowever, if all you want to do is bash me for my ignorance as you infer, but not prove, supierior knowledge - please don't bother to reply. Thanks.)

What are you talking about?

CREATE TRIGGER myTrigger99 ON myTable99
FOR INSERT, UPDATE
......

Want more?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-27 : 09:52:56

And then put some code like

IF EXISTS (SELECT FamilyID FROM Table1 GROUP BY FamilyID HAVING SUM(IsPrimary) <> 1)
  -- Something is wrong!
ELSE
  -- All is ok!

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources