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?
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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" ?
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

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 ?
Go to Top of Page

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
Go to Top of Page

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...
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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

Go to Top of Page

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
Go to Top of Page
    Next Page

- Advertisement -