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.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Disallow duplicates, in a multiple senario
 Forum Locked
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

afrika
Flowing Fount of Yak Knowledge

Nigeria
2706 Posts

Posted - 04/04/2005 :  12:17:56  Show Profile
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

USA
95 Posts

Posted - 04/04/2005 :  12:37:36  Show Profile  Visit rfrancisco's Homepage
How about adding a UNIQUE constraint on the phone number column. Will this address your issue?
Go to Top of Page

afrika
Flowing Fount of Yak Knowledge

Nigeria
2706 Posts

Posted - 04/04/2005 :  12:45:01  Show Profile
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

USA
7423 Posts

Posted - 04/04/2005 :  12:50:18  Show Profile  Visit jsmith8858's Homepage
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

USA
49 Posts

Posted - 04/04/2005 :  12:50:36  Show Profile
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
Flowing Fount of Yak Knowledge

Nigeria
2706 Posts

Posted - 04/04/2005 :  12:59:17  Show Profile
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

Edited by - afrika on 04/04/2005 13:00:02
Go to Top of Page

afrika
Flowing Fount of Yak Knowledge

Nigeria
2706 Posts

Posted - 04/04/2005 :  13:01:17  Show Profile
Also PLEASE NOTE ABOVE: username is a FK constraint from the users table
Go to Top of Page

rfrancisco
Yak Posting Veteran

USA
95 Posts

Posted - 04/04/2005 :  13:12:24  Show Profile  Visit rfrancisco's Homepage
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
Flowing Fount of Yak Knowledge

Nigeria
2706 Posts

Posted - 04/04/2005 :  13:38:32  Show Profile
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 ?

Edited by - afrika on 04/04/2005 13:41:51
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 04/04/2005 :  13:52:29  Show Profile  Visit jsmith8858's Homepage
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
Flowing Fount of Yak Knowledge

Nigeria
2706 Posts

Posted - 04/05/2005 :  16:25:44  Show Profile
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
Flowing Fount of Yak Knowledge

Nigeria
2706 Posts

Posted - 04/05/2005 :  16:35:27  Show Profile
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.

Edited by - afrika on 04/05/2005 16:37:07
Go to Top of Page

afrika
Flowing Fount of Yak Knowledge

Nigeria
2706 Posts

Posted - 04/05/2005 :  16:46:04  Show Profile
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

USA
7423 Posts

Posted - 04/05/2005 :  16:50:35  Show Profile  Visit jsmith8858's Homepage
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
Flowing Fount of Yak Knowledge

Nigeria
2706 Posts

Posted - 04/05/2005 :  16:57:06  Show Profile
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
Flowing Fount of Yak Knowledge

Nigeria
2706 Posts

Posted - 04/05/2005 :  16:58:48  Show Profile
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)

USA
7020 Posts

Posted - 04/05/2005 :  18:07:51  Show Profile
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

Australia
1591 Posts

Posted - 04/05/2005 :  18:54:14  Show Profile
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

USA
95 Posts

Posted - 04/05/2005 :  22:36:17  Show Profile  Visit rfrancisco's Homepage
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
Flowing Fount of Yak Knowledge

Nigeria
2706 Posts

Posted - 04/06/2005 :  16:04:24  Show Profile
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

USA
1537 Posts

Posted - 04/06/2005 :  16:29:22  Show Profile  Visit JimL's Homepage
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
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2019 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000