Disallow duplicates, in a multiple senario

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

Old Forums

CLOSED - General SQL Server

Disallow duplicates, in a multiple senario

Author

Topic

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-04-04 : 12:17:56

hello,
i have a phonebook, that has multiple userids. Which are unique.

However the phone entries should be unique for each userid, TAKING into consideration that two or more people could have the same contact number.

how could i implement it such that each users contact is not duplicated.

I could easily do this in a trigger, or the SP that does the insert, but is this a good and efficient way ?

Afrika

rfrancisco
Yak Posting Veteran

95 Posts

Posted - 2005-04-04 : 12:37:36

How about adding a UNIQUE constraint on the phone number column. Will this address your issue?

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-04-04 : 12:45:01

I dont think that woudl work.

Because if i add a unique constraint on the phone number column.

A user afrika could have a phone number 1234567890
and another users e.g rfrancisco could know this same individual and have the same number 1234567890.

A unique constraint would through the duplicate number out.

Afrika

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-04-04 : 12:50:18

I think we need some more information about the columns you are storing in your contacts table and how it is structured. Also, a few sample rows that you consider "OK" and a few that need to be considered duplicates would be very handy.

Most likely, you will end up with a multi-column primary key and/or unique constraint on your contacts table, but we cannot give you specific details unless you can provide them for us first.

- Jeff

jon3k
Starting Member

49 Posts

Posted - 2005-04-04 : 12:50:36

Just apply a unique constraint to whichever field should be unique.

What do you mean by "phone entry", if contact number isn't phone entry, what *IS* a "phone entry" ?

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-04-04 : 12:59:17

ok my table is such

username is for the user who entered the phone contact
phone number and nickname are the only compulsory fields

Please NOTE: two different users can have the same phone numbers


CREATE TABLE [dbo].[sms_phonebook] (
	[phoneID] [int] IDENTITY (1, 1) NOT NULL ,
	[username] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Nickname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Phone_number] [varchar] (55) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[First_Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Last_Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[country] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
) ON [PRIMARY]
GO

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-04-04 : 13:01:17

Also PLEASE NOTE ABOVE: username is a FK constraint from the users table

rfrancisco
Yak Posting Veteran

95 Posts

Posted - 2005-04-04 : 13:12:24

Given this table, what then do you mean by "the phone entries should be unique for each userid"? Can a user have multiple phone numbers in the table and you want to make sure that the phone number is unique for the user? If this is the case, then a unique constraint on the username and phone number should do the trick.

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-04-04 : 13:38:32

I have a unique constraint on the username already.

If i put a unique constraint on the phone number,...
... I dont think that would work, because different userIDs could have the same phone number. Ie there would be duplicate phone numbers in the phone table ie

UserID : afrika has phone number : 1234567890 as brother
UserID : rfrancisco has phone number : 1234567890 as colleague

If i put a unique constraint ,this would conflict for the phone number column, wouldnt it ?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-04-04 : 13:52:29

Some sample data of what is ok and what is not, as I mentioned, would make this process go a lot quicker.

Did you see this comment from my first post:

>>Most likely, you will end up with a multi-column primary key and/or unique constraint on your contacts table, but we cannot give you specific details unless you can provide them for us first.

Does that make sense? Are you aware that a table can have more than 1 column as the primary key? That means all rows are uniquely identified by the COMBINATION of two (or more) columns.

It sounds like you might want to set your PK of this table to be a combination of UserName/FirstName/LastName or perhaps UserName/NickName/Phone_number or something like that. You need to specify to us what you want and what combinations should be unique.

Does this make sense?

- Jeff

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-04-05 : 16:25:44

thanks Jeff,
I just got back into town, sorry about the delay.

I have seen some demo tables with 2PK on them, but never implemented such.

quote:
It sounds like you might want to set your PK of this table to be a combination of UserName/FirstName/LastName or perhaps UserName/NickName/Phone_number or something like that. You need to specify to us what you want and what combinations should be unique.

I think the latter option woudl be better

ie username/nickname/phone_number

sample data, pls hold...

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-04-05 : 16:35:27

CREATE TABLE [dbo].[sms_phonebook] (
[phoneID] [int] IDENTITY (1, 1) NOT NULL ,
[username] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Nickname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Phone_number] [varchar] (55) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[First_Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Last_Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[country] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
) ON [PRIMARY]
GO

sample data would be as:

phoneID      Username        Nickname        Phone_number      first_name    last_name
123          afrika          myself         2348033123250        null          null
124          afrika          dad            2348031235323        null           null
125          afrika         benin           2348033124423        Osa            Bay
244          pearl          Emma            2348032342322        null           null
324          pearl          afrika          2348033123250        null           null
234          gozo4b         yibo            2348033250278        chima          null
144          pearl          mom             2348033124232        null           null
232          afrika         lakeside        2348033001191        null           null
239          pearl          boss            2348938457345        null           null

Note the user afrika and pearl both share the same phone numbers for the nickname myself for afrika and afrika for pearl, phone numbers.

the other columns first_name, last_name and country can be null.

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-04-05 : 16:46:04

In the above data, the username is a Fk and Unique. The phoneID is a PK is unique, howver the nicknames are unique only to the usernames, and the phone numbers too should be unique to the usernames.

Thats what i am really trying to achieve.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-04-05 : 16:50:35

quote:

I think the latter option woudl be better

ie username/nickname/phone_number

Then that's your primary key. Try it. If you are working with databases, try to get familiar with tables with more than 1 column as a PK. It is very important to implement if you want to design good databases.

Way too many developers and DBA's out there just blindly add identities as their PK's to all tables and they never have accurate data.

Consider a table that relates SalesPeople to Clients, and a client can have many salespeople and a salesPerson can have many clients. It would have a primary key of: ClientID/SalesPersonID -- the combination of the two columns makes each row unique, and the PK won't let you list the same client twice for the same salesperson and have duplicate data. Believe it or not, you don't even need an identity on that table.

- Jeff

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-04-05 : 16:57:06

I do agree with you Jeff,
learning is paramount, thats why i am asking questions here :-)

how do i implement multiple PKs ?
Am searching thru BOL now

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-04-05 : 16:58:48

I know about unique constraints, and making a duplicate key. Is that what you mean ?

however when i do this, the key doesnt show, unless i check the unique constraints properties

pls advice
afrika

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-04-05 : 18:07:51

It doesn't seem correct that "the username is a Fk and Unique", since in the sample data you have a number of duplicates on username.

Also, if username is unique then you would have no problem, since you only have one phone number column.

quote:
Originally posted by afrika

In the above data, the username is a Fk and Unique. The phoneID is a PK is unique, howver the nicknames are unique only to the usernames, and the phone numbers too should be unique to the usernames.

Thats what i am really trying to achieve.

CODO ERGO SUM

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2005-04-05 : 18:54:14

afrika,

Slow down and take a breath.

1) Identify the rules:
a) A User has many knicknames
b) A User has many phonenumbers
c) A phone number must be unique per user.
d) A User-KnickName has a PhoneNumber

2) Identify dependancies
a) User -> Knickname
b) User -> PhoneNumber
c) PhoneNumber -> User
d) User-KnickName -> PhoneNumber

**** Notice "b" and "c" ********
This should raise a flag saying lets check these rules again before move on to key identification...

3) Identify keys
a) User
b) User
c) PhoneNumber
d) User-KnickName

4) Normalised
a) User (UserID PK)
b) UserKnickName (UserID, KnickName, PhoneNumber PK(UserID, KnickName)
c) UserPhone (UserID , PhoneNumber PK1(PhoneNumber) PK2 (UserID, PhoneNUnber)

*** Notice 2 candidate (Primary) keys in UserPhone caused by rules b & c above*************

RI (Foriegn key) chain = User -> UserPhone -> UserKnickName

DavidM

A front-end is something that tries to violate a back-end.

rfrancisco
Yak Posting Veteran

95 Posts

Posted - 2005-04-05 : 22:36:17

quote:
howver the nicknames are unique only to the usernames, and the phone numbers too should be unique to the usernames.

Based on your requirement, I don't think you are just looking at just a primary key made up of multiple columns. I think you are looking at a primary key with multiple columns and a unique constraint for another group of columns.

The first one will be a primary key using the username and phone number. That should address the requirement of "phone numbers should be unique to the username". Then add a unique constraint using the username and nickname to address the requirement of "nicknames are unique to usernames".

quote:
I think the latter option woudl be better

ie username/nickname/phone_number

If you just use a primary key or a unique constraint with the username, nickname and phone number, then a user can easily add a new record for the same phone number but with a different nickname. Also, a user can easily add a new record for the same nickname but with a different phone number.

As to which pair should be the primary key and which one should be the unique constraint depends on which ones you use to access data, is it by username and nickname or username and phone number.

Hope this helps.

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-04-06 : 16:04:24

Sorry Guys am out of the office for 2days,

Been reading through your advice, quite brilliant I must say, would be back tomorrow

So expect my reply

On the interim GRACIA MON AMI
Afrika

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2005-04-06 : 16:29:22

Myself I Mainly use Identity fields as PK. as int fields are faster links than Char fields.

For your example I would use phoneID PK and [username]+ [phone number] as a unique constraint.

Jim
Users <> Logic

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources