Re: Re: perl (mysql) question...

'nick' is not a primary key (in the main table 'members'). The primary key is an autocrement number, which is used as a foreign key in several other related tables. However, I do need the username to be unique in the table 'members' so that I do not have a situation where two members share the same name. Can I have username as the primary key and still use it as a foreign key in the related tables?

As this is my first foray into databases, I'm basing my gut feelings on decisions like that. So please pardon my question if it sounds silly :)

Comment on Re: Re: perl (mysql) question...

Replies are listed 'Best First'.
Re: Re: Re: perl (mysql) question... by Zaxo (Archbishop) on Dec 08, 2003 at 18:36 UTC
Yes, you can give your 'nick' column the UNIQUE attribute. That will make INSERT throw an error if a duplicate is attempted. Since you will be searching the column by nick, it may be worthwhile to make it an INDEX, too. The combination makes a column behave as as if it were a primary key. After Compline, Zaxo	[reply]
Re: Re: Re: perl (mysql) question... by CountZero (Bishop) on Dec 08, 2003 at 23:24 UTC
There are good reasons to have as your primary key an autoincrement value rather than the "nick". Think of what would happen when a user changes his "nick". All foreign links to this nick would need to be changed as well if "nick" was the primary key. Far better to define "nick" as UNIQUE (to disallow duplicates) and INDEX it (to speed up searches and access). CountZero "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law	[reply]