Constraint vs Trigger? - SQL Server Forums

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

SQL Server 2005 Forums

SQL Server Administration (2005)

Constraint vs Trigger?

Author

Topic

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2007-08-08 : 01:11:06

Hello,

I have a table that allows a user (from another table) to belong to multiple organizations. Part of this table's schema is a column that will be used to allow the user to indicate which organization that they belong to will be the primary one. A user can only have one PRIMARY organization. I was going to handle via an Insert or Update Trigger...checking to see if the userid already a Primary. However, now I am wondering if a Constraint would be the way to go? I admit though, I don't know enough about Constraints to know how this would work.

The table's schema is like:
UserOrg
UserOrgID (an identity column)
UserID (foreign key to the user's table)
Org (the name of the org)
PrimaryOrg (bit column)

Thanks for any help.
- Will.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-08-08 : 05:39:26

i'd use a trigger for this. if you set any org as the primary one the trigger can set others to non primary.
constraint only tells you that there must be one and not which one it is.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

pootle_flump

1064 Posts

Posted - 2007-08-08 : 08:05:28

Personally I would consider splitting this off into another table. I find triggers and check constraints a bit obscure sometimes and try to use them only when totally necessary. This would also be more efficient than searching UserOrg on every insert\ update.

CREATE TABLE dbo.UserOrgPrimary
	(
		UserID			INT				NOT NULL
		, Org			VARCHAR(20)		NOT NULL
		, CONSTRAINT pk_UserOrgPrimary					PRIMARY KEY CLUSTERED (UserID) WITH (FILLFACTOR = 90)
		, CONSTRAINT fk_UserOrgPrimary_UserOrg			FOREIGN KEY (UserID, Org) REFERENCES UserOrg (UserID, Org) ON DELETE CASCADE
	)
GO

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2007-08-08 : 11:26:29

Thanks for the help and advice. I am leaning towards using the Trigger, though the separate table for storing the primary org is interesting too.

- will

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources