How do I impliment this? - 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 2000 Forums

SQL Server Development (2000)

How do I impliment this?

Author

Topic

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-07-23 : 12:06:29

I have the following table (yes yes, Identities are bad)



CREATE TABLE [dbo].[Attendee] (
	[AttendeeID] [int] IDENTITY (1, 1) NOT NULL ,
	[UserID] [int] NOT NULL ,
	[AttendeeName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[PIN] [int] NOT NULL ,
	[IsUser] [bit] NOT NULL ,
	[IsArchived] [bit] NOT NULL ,
	[Cre_Date] [datetime] NOT NULL ,
	[Cng_Date] [datetime] NOT NULL

UserID is a FK to a Users table. A User can have N Attendees.
Another table (Members) has a FK to AttendeeID.

My business rule is that I can not have an Attendee with the same UserID / PIN / IsArchived combination.

This is legal:
UserA / 1234 / 0
UserA / 1234 / 1

This is not:
UserA / 1234 / 0
UserA / 1234 / 0

I need to make this work (to maintain referential integrity)
UserA / 1234 / 1
UserA / 1234 / 0
UserA / 1234 / 0

I could either do this, or give the users some way to "unarchive" a particular Attendee. Is that the better way?

Thoughts?
Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-07-23 : 12:16:38

can you explain this:

quote:

I need to make this work (to maintain referential integrity)
UserA / 1234 / 1
UserA / 1234 / 0
UserA / 1234 / 0

a little more? When you say "make it work", you mean the table needs to allow it? how is that maintaining integrity if it breaks your business rule?

or, is the question -- how to set a multi-column primary key?

- Jeff

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-07-23 : 13:29:17

I think I need to explain just a bit further Jeff.

My PK is AttendeeID.
I need to make something like this "allowable"

 
AttendeeID   UserID    PIN   IsArchived
1            UserA     1234  1 
2            UserA     1234  0 
3            UserA     1234  0

but disallow

 
AttendeeID   UserID    PIN   IsArchived
1            UserA     1234  1 
2            UserA     1234  1

I can not have two attendee's for the same user with the same pin that are not archived.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-23 : 13:38:47

A unique constaint perhaps?

A trigger?

Brett

8-)

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-07-23 : 13:45:15

I think I'm just going to have to put a unique index on the table, and force the user to unarchive a old PIN to use it again.
Thanks for you help anyway guys!

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-23 : 13:52:37

Well,

Can't a user archive over and over the same values?

It's a history kind of thing, no?

Is this one table or 2?

I'd make 2, one current, 1 history, and have a trigger fire to catch updates and deletes...

MOO

Brett

8-)

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-07-23 : 13:56:38

The more I think about this Brett, I don't think I'll ever really run into it. I'll only run into it when PIN's collide, and I've gotta right some special code to make sure that doesn't happen anyway.

I'm going to go down a different path with this, but thanks for everyone's help!

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-07-23 : 14:34:57

Michael --

in case you are curious, check out this thread:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=26104

i show an example on how to create a constraint on a computed column, which allows you to handle fairly complex cases. Might be useful to you.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources