not unique on InnoDB table
Based on my reply below, do you recommend I continue to have these indexes ?
-- Forwarded message --
From: Tompkins Neil neil.tompk...@googlemail.com
Date: Wed, Oct 13, 2010 at 8:22 PM
Subject: Re: Primary key not unique on InnoDB table
To: Travis Ard
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
Sorry, the word is counpound instead of composed.
--
João Cândido de Souza Neto
João Cândido de Souza Neto j...@consultorweb.cnt.br 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.
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 neil.tompk...@googlemail.com
wrote:
I've the following table. But why isn't the primary key unique, e.g.
preventing
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 j...@consultorweb.cnt.br
Sorry, the word is counpound instead of composed.
--
João Cândido de Souza Neto
João Cândido de Souza Neto j...@consultorweb.cnt.br
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 neil.tompk...@googlemail.com escreveu na mensagem
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 j...@consultorweb.cnt.br
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
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).
_Krishna
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`
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
--
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
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
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 travis_...@hotmail.com wrote:
I couldn't help but
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 from
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
select *
To: Steffan A. Cline [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Subject: Re: Primary key
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
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
WHERE
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` , `server`
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 'server' used
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,
PRIMARY KEY
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
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 as
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
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
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
- 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 message
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 clients
* 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 and
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
MySQL
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 database
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 table
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
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 BLOB
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
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 add Aa, aA
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,
Victoria Reznichenko wrote:
On Friday 04 April 2003 15:37, Grgoire 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),
Grgoire Dubois wrote:
Victoria Reznichenko wrote:
On Friday 04 April 2003 15:37, Grgoire 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
Bruce Feist wrote:
Grgoire Dubois wrote:
Victoria Reznichenko wrote:
On Friday 04 April 2003 15:37, Grgoire 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
(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 reply.
But I read that a PRIMARY KEY
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,
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 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
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
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 in the
http://iConnect.de
Heesestr. 6, 12169 Berlin (Germany)
Tel: +49 30 7970948-0 Fax: +49 30 7970948-3
- Original Message -
From: Rob Pecherer [EMAIL PROTECTED]
To: Stefan Hinz, iConnect (Berlin) [EMAIL PROTECTED]
Sent: Saturday, January 18, 2003 7:57 PM
Subject: RE: PRIMARY KEY
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 defining a
-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, March 31, 2002 1:33 AM
Subject: Re: primary key
At 0:40
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 help you
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
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 and
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
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 column
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.
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
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:
: 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 columns.
I don't think I am able to do this in mysql. I
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 unique value for two
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
.
- 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 primary key defined
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
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
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 new
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
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
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
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 didn't
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 a
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
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
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]
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
73 matches
Mail list logo
