Inspiring

Question

MySQL, Keys, and the use of UNIQUE

Forum|Forum|18 years ago
May 22, 2007
13 replies
614 views

I have built a MySQL table containing three fields:

1. RecordNum (Primary Key)
2. Keyword (TEXT)
3. Definition (TEXT)

This table contains data already. I now find that I neglected to consider
what would happen if someone tries to enter a second instance of the same
keyword!

What's the best way to extricate myself from this? 8)

I guess I need to re-index my table on unique keywords, but am not sure of
how to go about that, give that the table already contains data. I suppose
I could export the data, flush the table, and restructure it with the
keyword as the primary index?

--
Murray --- ICQ 71997575
Adobe Community Expert
(If you *MUST* email me, don't LAUGH when you do so!)
==================
http://www.dreamweavermx-templates.com - Template Triage!
http://www.projectseven.com/go - DW FAQs, Tutorials & Resources
http://www.dwfaq.com - DW FAQs, Tutorials & Resources
http://www.macromedia.com/support/search/ - Macromedia (MM) Technotes
==================

Server side applications

This topic has been closed for replies.

Newsgroup_UserAuthor

Inspiring

"Murray *ACE*" <forums@HAHAgreat-web-sights.com> wrote in message
news:f2uovl$1f9$1@forums.macromedia.com...
>> It sounds like your "RecordNum" is redundant. If the keyword is the key
>> for the table, why did you not declare it that way?
>
> <head hanging in shame>
>
> The current table has no dupes - I have removed them.
>
> The question is - how do I fix this before more info is added.

I would
ALTER TABLE DROP COLUMN RecordNum
ALTER TABLE ADD PRIMARY KEY (Keyword)
GO

Whatever works for you, though. ;)

Trapping the key violations (or unique constraint violations) can be handled
with the normal exception-trapping functions available in your application,
whether that's a try...catch or VBScript's On Error Resume Next or whatever
it is that normally 'sees' exceptions.

Newsgroup_UserAuthor

Inspiring

Murray *ACE* wrote:
> So, if that *did* work, then how would I trap the resulting errors so that
> the user could be informed what is happening? Would that use the "or die"
> part of the INSERT command?

Create a recordset called checkDup like this:

SELECT COUNT(*) FROM tableName WHERE keyword = formVariable

Use the value of $totalRows_getDup to control whether the Insert Record
server behavior is allowed to execute.

if (!$totalRows_getDup) {
// Insert Record SB code goes here
$result = $_POST['keyword']. ' entered in database. Thank you.';
}
else {
$result = $_POST['keyword']. ' is already registered. Insert abandoned.';
}

Display $result in the page.

--
David Powers, Adobe Community Expert
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
Author, "PHP Solutions" (friends of ED)
http://foundationphp.com/

Newsgroup_UserAuthor

Inspiring

Pat Shaw wrote:
> Not totally sure with MySQL but give this a try:
>
> ALTER TABLE [tbl_name]
>
> ADD CONSTRAINT
> UNIQUE INDEX [column_name]

The MySQL syntax is:

ALTER TABLE tableName ADD UNIQUE (columnName)

--
David Powers, Adobe Community Expert
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
Author, "PHP Solutions" (friends of ED)
http://foundationphp.com/

Newsgroup_UserAuthor

Inspiring

In SQL Server I would expect @@ERROR to pick this up in the Stored Procedure
and then send a suitable Return Code back to the front-end, but not being a
MySQL kinda guy I don't know. This is the point where a knowledgable MySQL
specific person comes on board and gives you the answer.

While we're waiting for that person to arrive I'll try my best to find an
answer (without getting fired in the process). :-)

"Murray *ACE*" <forums@HAHAgreat-web-sights.com> wrote in message
news:f2uqof$3o3$1@forums.macromedia.com...
> So, if that *did* work, then how would I trap the resulting errors so that
> the user could be informed what is happening? Would that use the "or die"
> part of the INSERT command?
>
> --
> Murray --- ICQ 71997575
> Adobe Community Expert
> (If you *MUST* email me, don't LAUGH when you do so!)
> ==================
> http://www.dreamweavermx-templates.com - Template Triage!
> http://www.projectseven.com/go - DW FAQs, Tutorials & Resources
> http://www.dwfaq.com - DW FAQs, Tutorials & Resources
> http://www.macromedia.com/support/search/ - Macromedia (MM) Technotes
> ==================
>
>
> "Pat Shaw" <pat@nomail.com> wrote in message
> news:f2uq8o$32g$1@forums.macromedia.com...
>> Not totally sure with MySQL but give this a try:
>>
>> ALTER TABLE [tbl_name]
>>
>> ADD CONSTRAINT
>> UNIQUE INDEX [column_name]
>>
>>
>>
>>
>> "Murray *ACE*" <forums@HAHAgreat-web-sights.com> wrote in message
>> news:f2uovl$1f9$1@forums.macromedia.com...
>>>> It sounds like your "RecordNum" is redundant. If the keyword is the
>>>> key for the table, why did you not declare it that way?
>>>
>>> <head hanging in shame>
>>>
>>> The current table has no dupes - I have removed them.
>>>
>>> The question is - how do I fix this before more info is added.
>>>
>>> --
>>> Murray --- ICQ 71997575
>>> Adobe Community Expert
>>> (If you *MUST* email me, don't LAUGH when you do so!)
>>> ==================
>>> http://www.dreamweavermx-templates.com - Template Triage!
>>> http://www.projectseven.com/go - DW FAQs, Tutorials & Resources
>>> http://www.dwfaq.com - DW FAQs, Tutorials & Resources
>>> http://www.macromedia.com/support/search/ - Macromedia (MM) Technotes
>>> ==================
>>>
>>>
>>> "Lionstone" <HIDElionstone@HIDEhushmail.com> wrote in message
>>> news:f2unvc$3o$1@forums.macromedia.com...
>>>> "Murray *ACE*" <forums@HAHAgreat-web-sights.com> wrote in message
>>>> news:f2uktu$pml$1@forums.macromedia.com...
>>>>>I have built a MySQL table containing three fields:
>>>>> This table contains data already. I now find that I neglected to
>>>>> consider what would happen if someone tries to enter a second instance
>>>>> of the same keyword!
>>>>>
>>>>> What's the best way to extricate myself from this? 8)
>>>>
>>> Most DBMSes will allow you
>>>> to alter a table with data. If the data has duplicates, then you'll
>>>> have to select distinct into a temporary table, drop the existing,
>>>> create the new, then copy the data from the temporary table to the new
>>>> table and drop it.
>>>>
>>>
>>>
>>
>>
>
>

Newsgroup_UserAuthor

Inspiring

So, if that *did* work, then how would I trap the resulting errors so that
the user could be informed what is happening? Would that use the "or die"
part of the INSERT command?

--
Murray --- ICQ 71997575
Adobe Community Expert
(If you *MUST* email me, don't LAUGH when you do so!)
==================
http://www.dreamweavermx-templates.com - Template Triage!
http://www.projectseven.com/go - DW FAQs, Tutorials & Resources
http://www.dwfaq.com - DW FAQs, Tutorials & Resources
http://www.macromedia.com/support/search/ - Macromedia (MM) Technotes
==================

"Pat Shaw" <pat@nomail.com> wrote in message
news:f2uq8o$32g$1@forums.macromedia.com...
> Not totally sure with MySQL but give this a try:
>
> ALTER TABLE [tbl_name]
>
> ADD CONSTRAINT
> UNIQUE INDEX [column_name]
>
>
>
>
> "Murray *ACE*" <forums@HAHAgreat-web-sights.com> wrote in message
> news:f2uovl$1f9$1@forums.macromedia.com...
>>> It sounds like your "RecordNum" is redundant. If the keyword is the key
>>> for the table, why did you not declare it that way?
>>
>> <head hanging in shame>
>>
>> The current table has no dupes - I have removed them.
>>
>> The question is - how do I fix this before more info is added.
>>
>> --
>> Murray --- ICQ 71997575
>> Adobe Community Expert
>> (If you *MUST* email me, don't LAUGH when you do so!)
>> ==================
>> http://www.dreamweavermx-templates.com - Template Triage!
>> http://www.projectseven.com/go - DW FAQs, Tutorials & Resources
>> http://www.dwfaq.com - DW FAQs, Tutorials & Resources
>> http://www.macromedia.com/support/search/ - Macromedia (MM) Technotes
>> ==================
>>
>>
>> "Lionstone" <HIDElionstone@HIDEhushmail.com> wrote in message
>> news:f2unvc$3o$1@forums.macromedia.com...
>>> "Murray *ACE*" <forums@HAHAgreat-web-sights.com> wrote in message
>>> news:f2uktu$pml$1@forums.macromedia.com...
>>>>I have built a MySQL table containing three fields:
>>>> This table contains data already. I now find that I neglected to
>>>> consider what would happen if someone tries to enter a second instance
>>>> of the same keyword!
>>>>
>>>> What's the best way to extricate myself from this? 8)
>>>
>> Most DBMSes will allow you
>>> to alter a table with data. If the data has duplicates, then you'll
>>> have to select distinct into a temporary table, drop the existing,
>>> create the new, then copy the data from the temporary table to the new
>>> table and drop it.
>>>
>>
>>
>
>

Newsgroup_UserAuthor

Inspiring

Not totally sure with MySQL but give this a try:

ALTER TABLE [tbl_name]

ADD CONSTRAINT
UNIQUE INDEX [column_name]

"Murray *ACE*" <forums@HAHAgreat-web-sights.com> wrote in message
news:f2uovl$1f9$1@forums.macromedia.com...
>> It sounds like your "RecordNum" is redundant. If the keyword is the key
>> for the table, why did you not declare it that way?
>
> <head hanging in shame>
>
> The current table has no dupes - I have removed them.
>
> The question is - how do I fix this before more info is added.
>
> --
> Murray --- ICQ 71997575
> Adobe Community Expert
> (If you *MUST* email me, don't LAUGH when you do so!)
> ==================
> http://www.dreamweavermx-templates.com - Template Triage!
> http://www.projectseven.com/go - DW FAQs, Tutorials & Resources
> http://www.dwfaq.com - DW FAQs, Tutorials & Resources
> http://www.macromedia.com/support/search/ - Macromedia (MM) Technotes
> ==================
>
>
> "Lionstone" <HIDElionstone@HIDEhushmail.com> wrote in message
> news:f2unvc$3o$1@forums.macromedia.com...
>> "Murray *ACE*" <forums@HAHAgreat-web-sights.com> wrote in message
>> news:f2uktu$pml$1@forums.macromedia.com...
>>>I have built a MySQL table containing three fields:
>>> This table contains data already. I now find that I neglected to
>>> consider what would happen if someone tries to enter a second instance
>>> of the same keyword!
>>>
>>> What's the best way to extricate myself from this? 8)
>>
> Most DBMSes will allow you
>> to alter a table with data. If the data has duplicates, then you'll have
>> to select distinct into a temporary table, drop the existing, create the
>> new, then copy the data from the temporary table to the new table and
>> drop it.
>>
>
>

Newsgroup_UserAuthor

Inspiring

That's what I was thinking would be the best approach - how do I do that?

--
Murray --- ICQ 71997575
Adobe Community Expert
(If you *MUST* email me, don't LAUGH when you do so!)
==================
http://www.dreamweavermx-templates.com - Template Triage!
http://www.projectseven.com/go - DW FAQs, Tutorials & Resources
http://www.dwfaq.com - DW FAQs, Tutorials & Resources
http://www.macromedia.com/support/search/ - Macromedia (MM) Technotes
==================

"Pat Shaw" <pat@nomail.com> wrote in message
news:f2umgh$rkl$1@forums.macromedia.com...
> Hi Murray,
>
> Add a Unique Constraint to that column. This will prevent duplicate values
> altogether. I am 99.9% sure that MySQL supports Unique Constraints.
>
> Pat.
>
>
> "Murray *ACE*" <forums@HAHAgreat-web-sights.com> wrote in message
> news:f2uktu$pml$1@forums.macromedia.com...
>>I have built a MySQL table containing three fields:
>>
>> 1. RecordNum (Primary Key)
>> 2. Keyword (TEXT)
>> 3. Definition (TEXT)
>>
>> This table contains data already. I now find that I neglected to
>> consider what would happen if someone tries to enter a second instance of
>> the same keyword!
>>
>> What's the best way to extricate myself from this? 8)
>>
>> I guess I need to re-index my table on unique keywords, but am not sure
>> of how to go about that, give that the table already contains data. I
>> suppose I could export the data, flush the table, and restructure it with
>> the keyword as the primary index?
>>
>> --
>> Murray --- ICQ 71997575
>> Adobe Community Expert
>> (If you *MUST* email me, don't LAUGH when you do so!)
>> ==================
>> http://www.dreamweavermx-templates.com - Template Triage!
>> http://www.projectseven.com/go - DW FAQs, Tutorials & Resources
>> http://www.dwfaq.com - DW FAQs, Tutorials & Resources
>> http://www.macromedia.com/support/search/ - Macromedia (MM) Technotes
>> ==================
>>
>>
>>
>
>

Newsgroup_UserAuthor

Inspiring

> It sounds like your "RecordNum" is redundant. If the keyword is the key
> for the table, why did you not declare it that way?

<head hanging in shame>

The current table has no dupes - I have removed them.

The question is - how do I fix this before more info is added.

--
Murray --- ICQ 71997575
Adobe Community Expert
(If you *MUST* email me, don't LAUGH when you do so!)
==================
http://www.dreamweavermx-templates.com - Template Triage!
http://www.projectseven.com/go - DW FAQs, Tutorials & Resources
http://www.dwfaq.com - DW FAQs, Tutorials & Resources
http://www.macromedia.com/support/search/ - Macromedia (MM) Technotes
==================

"Lionstone" <HIDElionstone@HIDEhushmail.com> wrote in message
news:f2unvc$3o$1@forums.macromedia.com...
> "Murray *ACE*" <forums@HAHAgreat-web-sights.com> wrote in message
> news:f2uktu$pml$1@forums.macromedia.com...
>>I have built a MySQL table containing three fields:
>> This table contains data already. I now find that I neglected to
>> consider what would happen if someone tries to enter a second instance of
>> the same keyword!
>>
>> What's the best way to extricate myself from this? 8)
>
Most DBMSes will allow you
> to alter a table with data. If the data has duplicates, then you'll have
> to select distinct into a temporary table, drop the existing, create the
> new, then copy the data from the temporary table to the new table and drop
> it.
>

Newsgroup_UserAuthor

Inspiring

"Murray *ACE*" <forums@HAHAgreat-web-sights.com> wrote in message
news:f2uktu$pml$1@forums.macromedia.com...
>I have built a MySQL table containing three fields:
> This table contains data already. I now find that I neglected to consider
> what would happen if someone tries to enter a second instance of the same
> keyword!
>
> What's the best way to extricate myself from this? 8)

It sounds like your "RecordNum" is redundant. If the keyword is the key for
the table, why did you not declare it that way? Most DBMSes will allow you
to alter a table with data. If the data has duplicates, then you'll have to
select distinct into a temporary table, drop the existing, create the new,
then copy the data from the temporary table to the new table and drop it.

Newsgroup_UserAuthor

Inspiring

On Tue, 22 May 2007 13:08:49 +0100, "Pat Shaw" <pat@nomail.com> wrote:

>Add a Unique Constraint to that column. This will prevent duplicate values
>altogether. I am 99.9% sure that MySQL supports Unique Constraints.

Yes, it does.
--
Steve Fleischer
steve at flyingtigerwebdesign dot com
Hong Kong

Show more replies

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded