ct: Fwd: Primary key not unique on InnoDB table
Based on my reply below, do you recommend I continue to have these indexes ?
-- Forwarded message --
From: Tompkins Neil
Date: Wed, Oct 13, 2010 at 8:22 PM
Subject: Re: Primary key not unique on InnoDB table
To: Travis Ard
Cc: &q
Hi Travis,
Thanks for your response. The fields which have indexes on, can be used on
every other search, which is why I thought about creating them. Would you
recommend against this ?
Cheers
Neil
On Wed, Oct 13, 2010 at 6:48 PM, Travis Ard wrote:
> I couldn't help but notice you have indivi
I couldn't help but notice you have individual indexes on nearly all the
fields of your table. If you won't be using these fields exclusively as a
join or filter condition in a query, you are unlikely to benefit from the
extra indexes and, in fact, they could slow down your inserts and add to
your
Shawn it is fine. I thought my primary key was just 1 field.
On Wed, Oct 13, 2010 at 4:44 PM, Shawn Green (MySQL) <
shawn.l.gr...@oracle.com> wrote:
> On 10/13/2010 11:37 AM, Tompkins Neil wrote:
>
>> Shawn, sorry my error, I didn't realise I had two fields as the primary
>> key
>>
>>
> That's
On 10/13/2010 11:37 AM, Tompkins Neil wrote:
Shawn, sorry my error, I didn't realise I had two fields as the primary key
That's misinformation. You can have multiple fields as a primary key.
Show us what you think is duplicate data and I may be able to help you
fix your definition
--
Shaw
On 10/13/2010 10:37 AM, Tompkins Neil wrote:
I've the following table. But why isn't the primary key unique, e.g.
preventing duplicates if entered ?
CREATE TABLE `players_master` (
`players_id` bigint(20) NOT NULL AUTO_INCREMENT,
`default_teams_id` bigint(20) NOT NULL,
`first_name` va
Of course, sorry totally stupid should I recognised that.
Thanks
Neil
On Wed, Oct 13, 2010 at 3:46 PM, Krishna Chandra Prajapati <
prajapat...@gmail.com> wrote:
> Hi Neil,
>
> Yes, primary key is always unique.
>
> In your case, you are using composite key (players_id,default_teams_id).
>
> _Kri
I see what you mean. Infact this is wrong and I will be dropping the second
field in the primary key.
2010/10/13 João Cândido de Souza Neto
> A primary key with an auto_increment is ok, but I cant think about a
> primary
> key with two fiels where one of them is autoincrement. Am I completely
A primary key with an auto_increment is ok, but I cant think about a primary
key with two fiels where one of them is autoincrement. Am I completely
wrong?
--
João Cândido de Souza Neto
"Tompkins Neil" escreveu na mensagem
news:aanlkti=xnjcaiq7bmoxg-q+4nowdhv8uaj9dcqrol...@mail.gmail.com...
Sorry Joao, I thought that was pretty standard to have a primary key with
auto_increment ??
2010/10/13 João Cândido de Souza Neto
> Sorry, the word is counpound instead of composed.
>
> --
> João Cândido de Souza Neto
>
> ""João Cândido de Souza Neto"" escreveu na
> mensagem news:2010101
Hi Neil,
Yes, primary key is always unique.
In your case, you are using composite key (players_id,default_teams_id).
_Krishna
On Wed, Oct 13, 2010 at 8:07 PM, Tompkins Neil wrote:
> I've the following table. But why isn't the primary key unique, e.g.
> preventing duplicates if entered ?
>
>
Sorry, the word is counpound instead of composed.
--
João Cândido de Souza Neto
""João Cândido de Souza Neto"" escreveu na
mensagem news:20101013144314.9787.qm...@lists.mysql.com...
> I´d never seen before a composed primary key that has an auto_increment
> field on it.
>
> May be I can
I´d never seen before a composed primary key that has an auto_increment
field on it.
May be I can be wrong but I think it wont work properly.
As far as I know, if you have an auto_increment field it must be your single
primary key. Am I wrong?
--
João Cândido de Souza Neto
"Tompkins Neil"
Hello Steve,
> Ok, I'm a little new a this, so be gentle!! :)
>
> I was looking into the InnoDB engine for some tables I have, and would
like
> to use the PK/FK on some of the data.
>
> It appears that the PK/FK is mainly used for updating/deleting data,
> correct? I can't use it to retreive data
On Sun, Mar 16, 2008 at 5:48 AM, Waynn Lue <[EMAIL PROTECTED]> wrote:
> Say I have this schema
>
> CREATE TABLE temp (
> EntityId BIGINT AUTO_INCREMENT PRIMARY KEY,
> CreationTime DEFAULT NOW()
> );
>
> Now let's say I want to find all rows created within the last 24 hours. If
> I do
>
>
---
> From: Dan Nelson <[EMAIL PROTECTED]>
> Date: Fri, 12 Jan 2007 01:00:03 -0600
> To: "Steffan A. Cline" <[EMAIL PROTECTED]>
> Cc:
> Subject: Re: Primary key
>
> In the last episode
In the last episode (Jan 11), Steffan A. Cline said:
> Is there anyway to do a SELECT or DESCRIBE or SHOW statement which can
> return the primary key field of a specified table?
>
> Pseudo code: select primary_key_field_name from mytable.
SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHER
Hi there,
> Im trying to set up a primary key of server(text), date (date), hour
> (small int) but when i try to include the server field in the key it
> replies with
>
> ALTER TABLE `exim` DROP PRIMARY KEY ,
> ADD PRIMARY KEY ( `date` , `hour` , `server` )
>
> #1170 - BLOB/TEXT column 'serve
Ronan <[EMAIL PROTECTED]> wrote on 16/02/2006 11:56:18:
> Im trying to set up a primary key of server(text), date (date), hour
> (small int) but when i try to include the server field in the key it
> replies with
>
> ALTER TABLE `exim` DROP PRIMARY KEY ,
> ADD PRIMARY KEY ( `date` , `hour` , `s
so if I do want 'name' to be unique I must not make it primary, just
simply unique, since my primary key is for id and name simultaneously.
[EMAIL PROTECTED] wrote:
your primary key is based on your (auto-increment) id and the name,
PRIMARY KEY (`id`,`name`)
so your two entries would be
"Haisam K. Ido" <[EMAIL PROTECTED]> wrote on 01/07/2005 15:04:01:
>
> I've created the following table (server 4.1 in win2k)
>
> CREATE TABLE `os` (
>`id` tinyint(10) NOT NULL auto_increment,
>`name` varchar(255) NOT NULL default '',
>`description` varchar(255) default NULL,
>PRI
Hendro,
In SQL an empty string is not null.
PB
Hendro Suryawan wrote:
Hi all,
I have table with primary key on field PO,BrgId, NOSP but when i try
insert several new reccord with field NOSP = '', mysql will accept the
new reccord without complaint error.
Is this normal behavior? My perception
Hendro Suryawan <[EMAIL PROTECTED]> wrote on 06/16/2005 06:53:31 PM:
> Hi all,
> I have table with primary key on field PO,BrgId, NOSP but when i try
> insert several new reccord with field NOSP = '', mysql will accept the
> new reccord without complaint error.
> Is this normal behavior?
As long
A varchar will take up less disk space than a char. A char is padded to
fill it's length, so a index on char will be much larger than a
varchar, depending on content.
Numbers work differently. An index on a number column should be faster
than the same sized char or varchar column. First a forem
Hi,
- 10 products in both cases. One time the column is a MediumInt, the
other time a BigInt. I know there is a difference in disk space usage, but
is there also one in performance at all ?
I'm not sure, this apply to your case. I had set a unique index on a
char(50) and it was 2x slower than
- Original Message -
From: "DBS" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Sunday, November 21, 2004 7:37 AM
Subject: Primary key error
> Hi list,
>
> MySQL newbie here and am using Navicat to learn how to manage a database
for
> a test OS shopping cart. I got the below error m
* Ronan Lucio
> Is the Primary Key Column mandatory?
>
> Supposing:
> If I have two tables: Clients and Cars, and a third table Clients_R_Cars,
> that is a relationship between Clients and Cars.
>
> I only need to know what cars the clients have.
> So, I just need to two columns "CliCar_ClientsID"
On Mon, 10 May 2004 11:15:25 -0300
"Ronan Lucio" <[EMAIL PROTECTED]> wrote:
> Is the Primary Key Column mandatory?
>
> Supposing:
> If I have two tables: Clients and Cars, and a third table
> Clients_R_Cars, that is a relationship between Clients and Cars.
>
> I only need to know what cars the c
At 15:02 -0300 3/17/04, Geilson Coutinho Figueiredo wrote:
Hi,
I would like to know what happen when I create an table without a
Primary Key. Does MySQL create an "hide" primary key?
For InnoDB and BDB, yes.
Otherwise, no.
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQ
David Johnston <[EMAIL PROTECTED]> wrote:
> I am starting to design a database in sql, to replace a flat file db.
>
> The database holds records, currenlty there is a new database for each
> record, so each record for each project starts with a id and increments.
>
> I want to create a sql datab
Hi,
> I saw an example of creating tables (see below). I
> wonder what the primary key (user_name, role_name) in
> the table user_roles means? Does it mean that both
> user_name and role_name are the primary key of the
> user_roles table? How does a table have two primary
> keys?
>
> create tab
Lemasson,
When you refer to a column as a primary key, I tend to think that this key
will be your main "relationship" point to the data in your table. Now, when
you combine the primary key concept with a BLOB (binary large object), it
seems to me that you are almost defeating the purpose of a prim
"Lemasson Sylvain" <[EMAIL PROTECTED]> wrote:
> I try to add a primary key on a BLOB column and I have got an error (ERROR 1170:
> BLOB column 'value5' used in key specification without a key length).
> May be the answer is in the documentation but I did not find it. So how could I make
> my BLO
Hi,
>May be the answer is in the documentation but I did not find it. So
>how could I make my BLOB column a primary key?
My guess is you cannot.
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscen
"Jacques Buitendag" <[EMAIL PROTECTED]> wrote:
>
> I have recently discovered that when you place a primary key constraint on a column
> of type CHAR(2) the constraint causes the following to happen when I add a new row
> to the table:
>
> if a key ("AA") is already in the table then I can not
(snip)
Does creating a primary key
on an int immediatly involve this one to be not null, and to
auto-increment?
>>>
>>>
>>>
>>> NOT NULL - yes, but if you want to have AUTO_INCREMENT column you
>>> should declare it as AUTO_INCREMENT.
>>
>>
>> Thank you very much for your rep
Bruce Feist wrote:
Grégoire Dubois wrote:
Victoria Reznichenko wrote:
On Friday 04 April 2003 15:37, Grégoire Dubois wrote:
In the following table, I declare ID as a PRIMARY KEY. Is it then
necessary to add the parameters NOT NULL AUTO_INCREMENT?
CREATE TABLE company (
ID INT NOT NULL AUTO
Grégoire Dubois wrote:
Victoria Reznichenko wrote:
On Friday 04 April 2003 15:37, Grégoire Dubois wrote:
In the following table, I declare ID as a PRIMARY KEY. Is it then
necessary to add the parameters NOT NULL AUTO_INCREMENT?
CREATE TABLE company (
ID INT NOT NULL AUTO_INCREMENT,
name
Victoria Reznichenko wrote:
On Friday 04 April 2003 15:37, Grégoire Dubois wrote:
In the following table, I declare ID as a PRIMARY KEY. Is it then
necessary to add the parameters NOT NULL AUTO_INCREMENT?
CREATE TABLE company (
ID INT NOT NULL AUTO_INCREMENT,
name VARCHAR(30),
admi
On Friday 04 April 2003 15:37, Grégoire Dubois wrote:
> In the following table, I declare ID as a PRIMARY KEY. Is it then
> necessary to add the parameters NOT NULL AUTO_INCREMENT?
>
> CREATE TABLE company (
> ID INT NOT NULL AUTO_INCREMENT,
>
> name VARCHAR(30),
>
> admin_ID INT,
>
thanks for the advice Stephen. I'll admit though I am somewhat loathe to
adding an artifical row in the other tables, but it may not be a bad way
to go. In the past, I've written triggers to do this kind of check, but
mysql doesn't yet support triggers.
what I ended up doing is carefully rethinkin
Jeff,
We faced a similar challenge in an application: Each child record must have
a parent in one of two tables, TabA or TabB, but not both. We "solved" it
by adding a foreign-key field for each possible parent in the child
table. Each column can have the FK constraint. We were using Sybase,
Thanks,
but I think the lik you provided won't help. I know how to create pk/fk
contraints, and do in our schema, when the foreign key is completely
specified. for example, if my original table was instead:
create table Example (
id int not null auto_increment primary key,
fk_id int
Jeff,
> I'm wondering if its somehow possible to create a pk/fk constraint for
> the table below
> create table Example (
> id int not null auto_increment primary key,
> table_name enum('TabA','TabB') not null,
> table_id int not null
> ) type = InnoDB;
> if table_name is
PROTECTED]>
To: "Stefan Hinz, iConnect (Berlin)" <[EMAIL PROTECTED]>
Sent: Saturday, January 18, 2003 7:57 PM
Subject: RE: PRIMARY KEY
> Greetings Stefan, and thanks.
>
> I'm not questioning what MySQL does, only how. MySQL does not "act"
> without a r
Rob,
> "If you don't have a PRIMARY KEY and some applications ask for the
> PRIMARY KEY in your tables, MySQL will return the first UNIQUE key,
> How does any application "ask for the PRIMARY KEY"? Is this an
> ODBC call, or are their command line and built-in functions? I
> couldn't find this
On Tue, 17 Dec 2002 19:15:08 +0100, Serrand Patrice wrote:
>Does MySQL automatically create index on primary key ?
Yes. See http://www.mysql.com/doc/en/CREATE_TABLE.html
- Steve Yates
- Antonym: The opposite of the word you're searching for.
~ Taglines by Taglinator - www.srtware.com ~
---
On Monday 16 December 2002 18:12, tmb wrote:
> I understood that MySQL didn't internally keep up with
> the relationships between tables... like MS Access...
>
> And that it was up to the programmer to referential
> integrity...
>
> But I noticed in phpMyAdmin that the offer the option
> of definin
x: 651-482-1391
ICQ: 3969599
Owner of the 3D-Unlimited Network:
http://www.3d-unlimited.com
Send News:
[EMAIL PROTECTED]
- Original Message -
From: "Paul DuBois" <[EMAIL PROTECTED]>
To: "Alex Behrens" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Sunday,
At 0:40 -0600 3/31/02, Alex Behrens wrote:
>Hey Guys,
>
>Is it possible to remove a primary key from a mysql table that has already
>been created without whipping out the table?
I suppose that depends on what "without whipping out the table" means.
I confess I have no idea.
ALTER TABLE might hel
In the last episode (Jan 09), D Woods said:
> I'm a novice at setting up a mysql database and don't know the answer to
> this. I usually set up my primary keys as autoincrementing ID fields. Now I
> need to have a primary key that isn't an autoincrementing field as I want to
> store the CFTOKEN an
yes, you can
-Original Message-
From: D Woods [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 09, 2002 4:02 PM
To: [EMAIL PROTECTED]
Subject: primary key that doesn't autoincrement
I'm a novice at setting up a mysql database and don't know the answer to
this. I usually set up my pri
corrected.
- Jonathan
-Original Message-
From: Bill Adams [mailto:[EMAIL PROTECTED]]
Sent: Monday, November 12, 2001 11:05 AM
To: Brendin
Cc: [EMAIL PROTECTED]
Subject: Re: primary key based on unique value for two columns
Brendin wrote:
> I would like to have a table that has a primar
sure you can
CREATE TABLE xx (field1 INT NOT NULL,field2 INT NOT NULL,PRIMARY KEY
(field1,field2))
and you have table xx with unique key in two fields
hand
primoz
- Original Message -
From: "Brendin" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, November 12, 2001 7:37 PM
Su
This will work thanks... That's what I want a unique key based on
two columns.
-Original Message-
From: Jonathan Hilgeman [mailto:[EMAIL PROTECTED]]
Sent: Monday, November 12, 2001 12:09 PM
To: 'Brendin'; [EMAIL PROTECTED]
Subject: RE: primary key based on uniqu
2:05 PM
To: Brendin
Cc: [EMAIL PROTECTED]
Subject: Re: primary key based on unique value for two columns
Brendin wrote:
> I would like to have a table that has a primary key defined on a
> combination of two columns in the table. In other words a unique key
> based upon the values in two c
You can't use a primary key for that, but you CAN make a unique two-column
key:
ALTER TABLE MyTable ADD UNIQUE MyNewIndex (Column1,Column2)
- Jonathan
-Original Message-
From: Brendin [mailto:[EMAIL PROTECTED]]
Sent: Monday, November 12, 2001 10:37 AM
To: [EMAIL PROTECTED]
Subject: prim
> I would like to have a table that has a primary key defined on a
> combination of two columns in the table. In other words a unique key
> based upon the values in two columns.
>
> I don't think I am able to do this in mysql. I think you can only have
> a primary key on one column and not on a
Hi
You are wrong.
Just try for example:
create table test(
pk1 int not null,
pk2 int not null,
primary key(pk1,pk2)
);
Regards
Daniel £a
e-direct Polska sp. z o.o.
WW
Brendin wrote:
> I would like to have a table that has a primary key defined on a
> combination of two columns in the table. In other words a unique key
> based upon the values in two columns.
>
> I don't think I am able to do this in mysql. I think you can only have
> a primary key on one colu
Yes, you can have multi-column keys. see the manual, para. 6.5.3 CREATE
TABLE Syntax
-Original Message-
From: Brendin [mailto:[EMAIL PROTECTED]]
Sent: Monday, November 12, 2001 12:37 PM
To: [EMAIL PROTECTED]
Subject: primary key based on unique value for two columns
I would like to hav
On 29-Aug-01, Paul DuBois wrote:
> Create a single PRIMARY KEY that consists of the two columns chapter and
> section, and make section an AUTO_INCREMENT column. When you insert
> rows, set chapter to the proper chapter number and section to NULL.
> This will cause MySQL to generate independent
At 10:37 AM -0400 8/29/01, Ferrara, Joseph C wrote:
>I want to create a table with two Primary Keys. The first key is a category
>field, and the second is auto-incremented. Example: Key 1=Chapter name, Key
>2 =section number. For example, (Chapter1, 1) (Chapter1, 2) (Chapter1, 3)
>then with a n
> I want to create a table with two Primary Keys. The first key is
> a category
> field, and the second is auto-incremented. Example: Key
> 1=Chapter name, Key
> 2 =section number. For example, (Chapter1, 1) (Chapter1, 2) (Chapter1, 3)
> then with a new Cheaper, I want to restart the auto-incre
Alex,
check by other queries whether it is the table which contains a
duplicate row, or if the SELECT gives a wrong answer.
Check what
SELECT * FROM ... WHERE symbole='1rPFTE';
returns.
Regards,
Heikki
At 12:04 PM 4/25/01 +0200, you wrote:
>Bonjour,
>
> Mysql-3.23.37 on Solaris 2.8.
>
>
>I have a primary key listed as ID
>
>When I first added about 10 records and then deleted a few records and
>then added more records it didn't replace the missing records.
>
>When I select all records to view I now get:
>
>1
>2 why didn't it go to 3, 4 and 5 after 2?
>6
>7
>8
>
>
>
>When I add a
How I learned to love number gaps:
I have a database of colleges and universities. Every degree listing
as a numbered id. This used to be auto-incremented. After several
deletes and additions, I found it advantageous to have gaps between
schools to add new degree listings, so that I didn't get
> How would you actually overcome that? Wouldn't it be good if
> MySQL would be adapted to actually do this for you?
I think that not reusing deleted numbers is easier (i.e. more efficient).
IIRC, earlier versions of MySQL in fact reused the numbers.
There are 2 ways to overcome this:
1) Check
No, what is meant here is that:
He has record 1 2 3 and 4. Now deletes 2 and 3. Now create 2 new records. MySQL
creates them as 5 and 6, instead of 2 and 3 again...
--Kobus
>>> Lindsay Adams <[EMAIL PROTECTED]> 2001-04-08 19:42:12 >>>
I can understand not wanting to have holes, but..
If it is
How would you actually overcome that? Wouldn't it be good if MySQL would be adapted to
actually do this for you?
-- Kobus
>>> "Jens Vonderheide" <[EMAIL PROTECTED]> 2001-04-08 19:25:48 >>>
> When I first added about 10 records and then deleted a few records and
> then added more records it did
I can understand not wanting to have holes, but..
If it is a primary key, how are you going to handle updating tables that
rely on that key id?
If your id numbers were to shift, like you want, and you had a related sub
table (one to many) and this was say, an shopping cart, then all of a sudden
> When I first added about 10 records and then deleted a few records and
> then added more records it didn't replace the missing records.
That's MySQL's usual behaviour. auto_increment only garantees to create
unique keys. "Holes" left by deleting data are not filled.
Jens
Yes, you can but you probably need a real good reason to. As Tilghman
pointed out a PK is a unique key. If you have another unique key (a
candidate key) then you need revisit your datamodel and make sure you have a
good reason. Candidate keys to exist in the real world but they are rare.
Cal
htt
No. A primary key is a unique key, by definition.
--
"There cannot be a crisis today. My schedule is already full."
--Henry Kissinger
> -Original Message-
> From: Jacob Friis Larsen [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, February 07, 2001 09:58
> To: [EMAIL PROTECTED]
> Su
Hi,
Asaf Maruf wrote:
>
> How can i specify two columns together as primary key for a table.
>
> Using create table test
> (increment int , id int not null primary key, date not null primary key, name
>char(20) );
>
> doesn't work.
You should rather say
create table test (
increment int ,
Asaf Maruf wrote:
>
> Dear all on list
>
> How can i specify two columns together as primary key for a table.
>
> Using create table test
> (increment int , id int not null primary key, date not null primary key, name
>char(20) );
>
> doesn't work.
>
> Is it possible that one column is tex
76 matches
Mail list logo
