Re: why NOT NULL in PRIMARY key??

At 15:20 +0200 4/26/05, Jigal van Hemert wrote: From: Jay Blanchard Since NULL is the absence of a value and PRIMARY keys must have a value a NULL column cannot be included as a portion of a PRIMARY key. AFAIK this is the case with every RDBMS out there. Asking the development team might get

Re: why NOT NULL in PRIMARY key??

In article [EMAIL PROTECTED], Jigal van Hemert [EMAIL PROTECTED] writes: From: Martijn Tonies Ehm... it might be me - but what sense does it make to have a NULL in a PK? If you need this, then your primary key probably isn't a primary key. Care to explain why and how you're designing your

Re: why NOT NULL in PRIMARY key??

From: Paul DuBois Hi Paul, A primary key absolutely forbids duplicate values. Indexes created with the UNIQUE keyword do not allow duplicates, except for the special case that multiple NULL values are allowed. I realise that it may (and is) defined in such a way, but it still does

Re: why NOT NULL in PRIMARY key??

(if data already exists), and checks each time data is added with an insert or update. If there is a duplicate key value or if more than one row contains a null value, the command is aborted and an error message giving the duplicate is printed. An unique index is not a primary key constraint

Re: why NOT NULL in PRIMARY key??

From: Harald Fuchs id INT(11) - accountID name VARCHAR(32) - parameter name value INT(11) - parameter value Other tables contain string, datetime, etc. parameters. Since most searches are made for a value (or range) of one or more parameters, a usable primary key is: name-value-id

Re: why NOT NULL in PRIMARY key??

On 4/26/05, Jigal van Hemert [EMAIL PROTECTED] wrote: And http://dev.mysql.com/doc/mysql/en/create-table.html tells me that A PRIMARY KEY is a unique KEY where all key columns must be defined as NOT NULL. If they are not explicitly declared as NOT NULL, MySQL declares them so implicitly

Re: why NOT NULL in PRIMARY key??

A primary key absolutely forbids duplicate values. Indexes created with the UNIQUE keyword do not allow duplicates, except for the special case that multiple NULL values are allowed. I realise that it may (and is) defined in such a way, but it still does not explain *why* part

Re: why NOT NULL in PRIMARY key??

From: Jochem van Dieten Why is this? Because the SQL standard says so. A true observation, but still no explanation or reason why ;-P MySQL doesn't follow the standard in every situation, so that's not an excuse... (no offense!) There must be a good reason other than because our ancestors

Re: why NOT NULL in PRIMARY key??

At 16:25 +0200 4/26/05, Jigal van Hemert wrote: From: Paul DuBois Hi Paul, A primary key absolutely forbids duplicate values. Indexes created with the UNIQUE keyword do not allow duplicates, except for the special case that multiple NULL values are allowed. I realise that it may

Re: why NOT NULL in PRIMARY key??

From: Dawid Kuroczko It can't have anything to do with the 'uniqueness' of the data, since I can have a lot of 'zero'-values in the column, as long as the combination of columns in the PRIMARY key results in unique values. Because it is a PRIMARY KEY. I mean phrase 'PRIMARY KEY' means

Re: why NOT NULL in PRIMARY key??

From: Paul DuBois I realise that it may (and is) defined in such a way, but it still does not explain *why* part of a PRIMARY key might not be NULL. If the combination of parts in the PRIMARY key is such that it can uniquely identify a record it would be sufficient for a primary key IMHO

Re: why NOT NULL in PRIMARY key??

At 16:47 +0200 4/26/05, Jigal van Hemert wrote: From: Dawid Kuroczko It can't have anything to do with the 'uniqueness' of the data, since I can have a lot of 'zero'-values in the column, as long as the combination of columns in the PRIMARY key results in unique values. Because

Re: why NOT NULL in PRIMARY key??

It can't have anything to do with the 'uniqueness' of the data, since I can have a lot of 'zero'-values in the column, as long as the combination of columns in the PRIMARY key results in unique values. Because it is a PRIMARY KEY. I mean phrase 'PRIMARY KEY' means a key

Re: why NOT NULL in PRIMARY key??

(or range) of one or more parameters, a usable primary key is: name-value-id That's a horrible denormalization. If one named parameter can hold only one INT value for one account id, then (id, name) could be a primary key; otherwise, you'd need a surrogate primary key. And what

Re: why NOT NULL in PRIMARY key??

At 16:56 +0200 4/26/05, Jigal van Hemert wrote: From: Paul DuBois I realise that it may (and is) defined in such a way, but it still does not explain *why* part of a PRIMARY key might not be NULL. If the combination of parts in the PRIMARY key is such that it can uniquely identify a record

Re: why NOT NULL in PRIMARY key??

Jigal, create table YourTable ( id INT(11), name VARCHAR(32), value INT(11), PRIMARY KEY(id,name,value) ) let's assume that PRIMARY KEY works like you want (accept NULLs) and we have a row in your table: (id,name,value) = (1,NULL,12) Then you insert a new row: insert into YourTable (id,name

Re: why NOT NULL in PRIMARY key??

From: Paul DuBois I would understand it if it would mean that the key as a whole could not be NULL, but the restriction that each column that is part of a PRIMARY KEY must have the NOT NULL constraint is not logical. Sure it is. If any part could be NULL, then it could contain duplicate

Re: why NOT NULL in PRIMARY key??

Jigal, I would define the key as: parameter_name-value-account_id. InnoDB is very fast if you use the primary key and a lot slower if you use secudary key(s), so queries can get considerably faster if you use a primary key. One reason the PK is faster is that the engine needn't handle

RE: why NOT NULL in PRIMARY key??

[snip] The same is true for any other value... Now that the columns have a NOT NULL constraint the records that previously contained NULL now hold '0'. x y x 0 x z x 0 Now, how do you uniquely identify the 2nd and 4th rows? [/snip] The database would have thrown an error when you tried to

Re: why NOT NULL in PRIMARY key??

(or range) of one or more parameters, a usable primary key is: name-value-id That's a horrible denormalization. If one named parameter can hold only one INT value for one account id, then (id, name) could be a primary key; otherwise, you'd need a surrogate primary key. How would *you

Re: why NOT NULL in PRIMARY key??

I would understand it if it would mean that the key as a whole could not be NULL, but the restriction that each column that is part of a PRIMARY KEY must have the NOT NULL constraint is not logical. Sure it is. If any part could be NULL, then it could contain duplicate NULL values

Re: why NOT NULL in PRIMARY key??

primary key). Just speculation of course :) Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: why NOT NULL in PRIMARY key??

; mysql@lists.mysql.com Subject: Re: why NOT NULL in PRIMARY key?? Not every DBMS... MSSQL: Create Unique Index Microsoft(r) SQL Server(tm) checks for duplicate values when the index is created (if data already exists) and checks each time data is added with an INSERT or UPDATE statement

Re: Collecting the primary key using MAX during an insert

keys based on some letters + an incrementing value. That is a very user-friendly method but does not lend itself well to MySQL. What you CAN do with mysql is to split your primary key into two columns, one text the other an auto_increment-ed numeric. Then, when you insert the new row of data you

Collecting the primary key using MAX during an insert

Hi there, I was wondering how its possible to get the MAX of a primary key of a table during an insert. I basically want to create a ticket number, but use the primary key as part of the ticket number ie FAULT-001 or FAULT-0002 . I tried during a sub query on an insert but obviouslly

Re: Collecting the primary key using MAX during an insert

Dan Rossi [EMAIL PROTECTED] wrote on 04/20/2005 12:55:45 AM: Hi there, I was wondering how its possible to get the MAX of a primary key of a table during an insert. I basically want to create a ticket number, but use the primary key as part of the ticket number ie FAULT-001 or FAULT

Re: Collecting the primary key using MAX during an insert

Dan Rossi wrote: Hi there, I was wondering how its possible to get the MAX of a primary key of a table during an insert. I basically want to create a ticket number, but use the primary key as part of the ticket number ie FAULT-001 or FAULT-0002 . I tried during a sub query on an insert

Re: Collecting the primary key using MAX during an insert

). For example, CREATE TABLE yourtable ( id INT(7) ZEROFILL UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, type ENUM('FAULT', 'BUG', 'CRASH', 'REQUEST'), other columns... ); or, if you want a separate sequence for each type (MyISAM), CREATE TABLE yourtable ( id INT(7) ZEROFILL UNSIGNED

Re: Collecting the primary key using MAX during an insert

In article [EMAIL PROTECTED], [EMAIL PROTECTED] writes: Dan Rossi [EMAIL PROTECTED] wrote on 04/20/2005 12:55:45 AM: Hi there, I was wondering how its possible to get the MAX of a primary key of a table during an insert. I basically want to create a ticket number, but use the primary key

using MyAdmin to build the MySql-Having trouble setting auto-increment for primary key

wanted to set it where Type 1 users got a primary key UserID that was even, while Type 2 users got a UserID that was odd. I have set the auto-increment value in both registration forms to 2 but when I set the default in the type 1's to 2 and the type2's to 1, it does not work and they are both getting

Re: using MyAdmin to build the MySql-Having trouble setting auto-increment for primary key

of the database, I wanted to set it where Type 1 users got a primary key UserID that was even, while Type 2 users got a UserID that was odd. Add another column for user type. Encoding special meaning to certain values of an otherwise unrelated column is a bad idea. I have set the auto-increment value

Adding fields to db table (primary key and other type)

Hi all, I am using MySQL Command Line and have created a table called dtd_test. It has two varchar fields at the moment. How can I add more fields? I want to add a primary key column which autoincrements, how can I do that? Thanks a lot

Re: Adding fields to db table (primary key and other type)

. How can I add more fields? I want to add a primary key column which autoincrements, how can I do that? Thanks a lot -- Eric Bergen [EMAIL PROTECTED] http://www.ebergen.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http

Re: Adding fields to db table (primary key and other type)

http://dev.mysql.com/doc/mysql/en/alter-table.html ALTER TABLE dtd_test ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (id); On Wed, 16 Mar 2005 14:56:59 -0500, Ed [EMAIL PROTECTED] wrote: Hi all, I am using MySQL Command Line and have created a table called dtd_test. It has

Fw: Adding fields to db table (primary key and other type)

Thanks for the replies, works fine, I checked out the alter table syntax and added a new field. How can you add two new fields I tried with ALTER TABLE DTD_Test add template_header varchar(255), template_footer varchar(255); but i get an error. Cheers

Re: Fw: Adding fields to db table (primary key and other type)

See my original post: ALTER TABLE dtd_test ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (id); Primary key could've just as easily been another column. You have to include another ADD command after the comma. On Wed, 16 Mar 2005 15:25:14 -0800, Scott Klarenbach [EMAIL PROTECTED

RE: How to specify autoincrement primary key value

and only_ root category)? -Original Message- From: Martijn Tonies [mailto:[EMAIL PROTECTED] Sent: Thursday, February 10, 2005 2:31 PM To: Denis Gerasimov; [EMAIL PROTECTED]; 'MySQL General List' Subject: Re: How to specify autoincrement primary key value One simple question

How to specify autoincrement primary key value

Hello, One simple question... AFAIK I can specify value for an autoincrement primary key (int) when inserting a record like this: INSERT INTO `tablename` (`id`, `name`) VALUES (1, 'test') But it doesn't work for id = 0. Why? I would like to use some primary key values for special purpose, e.g

Re: How to specify autoincrement primary key value

Denis Gerasimov [EMAIL PROTECTED] wrote on 10/02/2005 10:59:11: Hello, One simple question... AFAIK I can specify value for an autoincrement primary key (int) when inserting a record like this: INSERT INTO `tablename` (`id`, `name`) VALUES (1, 'test') But it doesn't work for id = 0

RE: How to specify autoincrement primary key value

Hello, One simple question... AFAIK I can specify value for an autoincrement primary key (int) when inserting a record like this: INSERT INTO `tablename` (`id`, `name`) VALUES (1, 'test') But it doesn't work for id = 0. Why? I would like to use some primary key values

Re: How to specify autoincrement primary key value

One simple question... AFAIK I can specify value for an autoincrement primary key (int) when inserting a record like this: INSERT INTO `tablename` (`id`, `name`) VALUES (1, 'test') But it doesn't work for id = 0. Why? I would like to use some primary key values for special

add auto-increment field to fix table with no primary key - help

I have a table with no primary key. I would like to add a new auto-increment column field to each record - that would be the easiest way to remedy this. Eg. 1,2,3,4tagged onto each record successively. Is this possible with SQL? I also have PHP to use as well if needed. Thanks -- MySQL

RE: add auto-increment field to fix table with no primary key - h elp

[snip] I have a table with no primary key. I would like to add a new auto-increment column field to each record - that would be the easiest way to remedy this. Eg. 1,2,3,4tagged onto each record successively. [/snip] ALTER TABLE t ADD id INT UNSIGNED NOT NULL auto_increment PRIMARY KEY FIRST

Re: add auto-increment field to fix table with no primary key - help

ALTER TABLE yourtable ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY; See the manual for details http://dev.mysql.com/doc/mysql/en/alter-table.html. Michael leegold wrote: I have a table with no primary key. I would like to add a new auto-increment column field to each record

Re: primary key performance

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

Re: primary key performance

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

primary key performance

I have a pretty standard database schema here with the primary key prod_id being my most often used join column in select queries. Categories, rankings .. just about anything having to do with products uses prod_id in a join, and user access on these queries is pretty heavy. I wonder whether

How to determine a field is part of the primary key in a table?

Hi list, I m writing a perl program and would like to use it (wiht mysql command) to determine whether a field name is (or is part of) a primary key of a table. If you know how to handle this, please drop me a line. Thanks and Happy New Year. Sam -- MySQL General Mailing List For list archives

Re: How to determine a field is part of the primary key in a table?

Sam For columns in a statement $sth, DBD:mysql maintains a boolean array named is_pri_key. PB - - Original Message - From: sam wun To: mysql@lists.mysql.com Sent: Saturday, January 01, 2005 7:58 AM Subject: How to determine a field is part of the primary key in a table

Re: unique key - primary key

I have two tables, seemigly very similar setup; the primary key is the combination of two columns. With mysqldump, however, the table definition of the two tables looks different. Mysqldump on table 1 says ... UNIQUE KEY HONstid (HONstid,HONname) whereas on table 2

Re: unique key - primary key

Hello, RE: And columns in primary keys must be NOT NULL. Columns in unique keys can be NULL (if they are NOT NULL, then the unique key is functionally the same as a primary key). OK, thanks guys for the explanation. Then the result of mysqldump table definition part: UNIQUE KEY

unique key - primary key

Hi, I have two tables, seemigly very similar setup; the primary key is the combination of two columns. With mysqldump, however, the table definition of the two tables looks different. Mysqldump on table 1 says ... UNIQUE KEY HONstid (HONstid,HONname) whereas on table 2 it says

Re: unique key - primary key

Hello, I have two tables, seemigly very similar setup; the primary key is the combination of two columns. With mysqldump, however, the table definition of the two tables looks different. Mysqldump on table 1 says ... UNIQUE KEY HONstid (HONstid,HONname) whereas on table 2 it says

Re: unique key - primary key

At 22:27 +0100 12/28/04, Martijn Tonies wrote: Hello, I have two tables, seemigly very similar setup; the primary key is the combination of two columns. With mysqldump, however, the table definition of the two tables looks different. Mysqldump on table 1 says ... UNIQUE KEY HONstid

Re: unique key - primary key

Hi, RE: And columns in primary keys must be NOT NULL. Columns in unique keys can be NULL (if they are NOT NULL, then the unique key is functionally the same as a primary key). OK, thanks guys for the explanation. Then the result of mysqldump table definition part: UNIQUE KEY HONstid

Re: Help needed in creating primary key ,foreign key on a varchar datatype colum

Dayakar, - Original Message - From: Dayakar [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Sunday, November 21, 2004 7:39 AM Subject: Help needed in creating primary key ,foreign key on a varchar datatype colum --=_NextPart_000_000B_01C4CFBA.91C9BA80 Content-Type: text

Primary key error

to the table in question for the primary keys, one for each row entry? Zen_products does not have a primary key. Updates to this table will be done using the following pseudo statement: UPDATE zen_products SET ModifiedFieldsAndValues Where “AllFieldsAndOldValues” Updates to a record in this table may

Re: Help needed in creating primary key ,foreign key on a varchar datatype colum

, that use the INNODB engine: drop table if exists dept; create table dept( deptno char(3) not null, deptname varchar(36) not null, mgrno char(6), primary key(deptno) ) Type=InnoDB; drop table if exists emp; create table emp( empno char(6) not null, firstnme char(12) not null, midinit char(1

Re: Primary key error

- 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

Help needed in creating primary key ,foreign key on a varchar datatype colum

Hello, I am converting my database from oracle to mysql4.1 and i want the same structure as it is oracle like primary key and foreign key references etc.. In oracle i have created my primary key and foreign key references on a varchar datatype column, so can any one help me in doing the same

Re: How to make 1 primary key work for 2 columns????

) BINARY NOT NULL, PRIMARY KEY (id), UNIQUE KEY (email) ); CREATE TABLE yourtable ( -- other columns email1 INT UNSIGNED NULL, email2 INT UNSIGNED NULL, KEY (email1), FOREIGN KEY (email1) REFERENCES emails, KEY (email2), FOREIGN KEY (email2) REFERENCES emails ); -- MySQL General

RE: How to make 1 primary key work for 2 columns????

table at the same time, so no email addresses will be unique. The same would goes for creating a new table too because of a few reasons. 1) The 2nd person would be unable to update the email if the primary key is set. 2) I would have to use the application code to remove a row if the customer

How to make 1 primary key work for 2 columns????

primary key that treated the 2 columns as one column with unique email address. Let's this example below --snip- | First_Email | Second_Email | | [EMAIL PROTECTED] | [EMAIL PROTECTED

Re: How to make 1 primary key work for 2 columns????

) ) The way I defined this primary key means that for any company, an email address can appear only once. However, that email can be used for multiple companies (a common sales person shared between smaller businesses, for example). Do you want it so that an email is always specific to only one company

RE: How to make 1 primary key work for 2 columns????

Aw Man! I forgot about the null value... Since there will be some null values, so I can't use the primary key. So, I now know it is not possible. Thanks for reminding me that. So, seem that the workaround for me is to retrieve all of the emails from the primary contact and secondary contact

RE: How to make 1 primary key work for 2 columns????

From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 20, 2004 3:28 PM To: Scott Fletcher Cc: [EMAIL PROTECTED] Subject: Re: How to make 1 primary key work for 2 columns I couldn't understand exactly what you were asking for. Did you want no duplicates between

RE: How to make 1 primary key work for 2 columns????

that with with DB2 but MySQL is different. Thanks, Scott - Original Message - From: Scott Fletcher [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, October 20, 2004 3:54 PM Subject: How to make 1 primary key work for 2 columns Hi! I'm trying to figure out how to make

RE: How to make 1 primary key work for 2 columns????

But rhino, your constraints don't cover the case of : Primary Secondary --- [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] This passes all of your constraints (the primary key and both unique keys) but I think it fails his

Re: How to make 1 primary key work for 2 columns????

: RE: How to make 1 primary key work for 2 columns But rhino, your constraints don't cover the case of : Primary Secondary --- [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] This passes all of your constraints

A Table with a timestamp as transaction date and primary key...

be done by insert and that the timestamp not be altered with the SQL update of any sort. I also want the TIMESTAMP to be a primary key. So, what is my options? Thanks...

Re: A Table with a timestamp as transaction date and primary key...

to be the transaction date which can be done by insert and that the timestamp not be altered with the SQL update of any sort. Then you need 2 timestamps. Both get updated on insert, The first gets updated on updates. I also want the TIMESTAMP to be a primary key. Sorry. This would allow only one insert per

Re: A Table with a timestamp as transaction date and primary key...

TIMESTAMP has a resolution of only 1 second. Not good enough as a primary key for most applications. You may want an auto_incrementing int field (or bigint depending on the size of your data) and make that your primary key. Only the first TIMESTAMP column is automatically updated with the update

RE: Endless primary key build

You're right - it is a data related issue. I finally made it work with the live data on high speed but in a way that puzzles me. Do you know the explanation ? (I'm using mySQL 4.1.1a-max-nt on Win2K) The index of the primary key is using the same data set (content and size) for both the test

Endless primary key build

Hello people, I'm having a strange behavior when rebuilding and querying a 58million row table. Probably because I'm new to mysql. I'm using it through java btw. My table has three columns: 2 mediumint and 1 smallint. The first two columns are the primary key. My first test was to write

Alter table primary key and foreign keys

I am using mysql 4.0.12 max-nt on Windows XP. I have a master table with an int column as a primary key (bom_id) and a second table that has a foreign key reference to the master column and uses it as part of a composite key (bom_id, fc_date). Example: ** bom_mstr

RE: Alter table primary key and foreign keys

the child table? -Original Message- From: Rich Schramm To: [EMAIL PROTECTED] Sent: 5/18/04 12:43 PM Subject: Alter table primary key and foreign keys I am using mysql 4.0.12 max-nt on Windows XP. I have a master table with an int column as a primary key (bom_id) and a second table that has

RE: Alter table primary key and foreign keys

'; '[EMAIL PROTECTED] ' Subject: RE: Alter table primary key and foreign keys I would first see if an upgrade to a later version of InnoDB tables is possible. What is being written to the error log? The ALTER TABLE statement subtly creates a new table, with new contraint names that the child table

RE: Alter table primary key and foreign keys

Can you mysqldump the table then rebuild the table from the dump file? -Original Message- From: Rich Schramm To: 'Victor Pendleton'; [EMAIL PROTECTED] Sent: 5/18/04 2:04 PM Subject: RE: Alter table primary key and foreign keys The error log shows nothing when the binary dies. I can't

Re: Alter table primary key and foreign keys

Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.0.12-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql create table bom_mstr(bom_id int not null, primary key (bom_id)) type = i nnodb; Query OK, 0 rows affected (0.04 sec

Primary Key

Hi, 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 CliCar_CarsID

Re: Primary Key

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

Re: Primary Key

* 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

RE: Compound Primary Key question

have a lookup table 'ItemDescription' which contains a list of description fields for items, it would make sense to make the table (ItemID, Description) with ItemID being an autoincrement primary key. However, in some other cases, a compound key will make more sense - for instance if you have a 'glue

Compound Primary Key question

Quick question. In general, is it better to create compound primary keys or use an auto increment field to uniquely identify each record? --T __ Do you Yahoo!? Yahoo! Photos: High-quality 4x6 digital prints for 25¢

Re: Compound Primary Key question

On Fri, Apr 23, 2004 at 03:40:43PM -0700, Emmett Bishop wrote: Quick question. In general, is it better to create compound primary keys or use an auto increment field to uniquely identify each record? Yes. It depends on your application and your data. Jeremy -- Jeremy D. Zawodny |

Re: Do I specify a primary key to be primary, unique and index ?

From the MySQL documentation: * A PRIMARY KEY is a unique KEY where all key columns must be defined as NOT NULL. KEY is a synonym for INDEX. So, specifying PRIMARY KEY implies UNIQUE and INDEX.. You don't have to specify them yourself. At 01:11 am 4/11/2004, you wrote: I learned

RE: Do I specify a primary key to be primary, unique and index ?

As I discovered recently, thanks to another user on this list, there is at least one situation where you WILL need to also create a KEY index on a PRIMARY KEY column - If you have a composite primary key such as (col1, col2) and you wish to place a foreign key on col2, you will ALSO have to add

Do I specify a primary key to be primary, unique and index ?

I learned that there are three types of indexes (PRIMARY, UNIQUE, and INDEX). Now assuming I create a performance-critical PRIMARY key, will I better have to specify UNIQUE and INDEX for this column also !? It should be obvious that a primary key is unique anyway, and an index as well, shouldnt

PRIMARY KEY in mysql 4.0.18

Hi, I would like to know what happen when I create an table without a Primary Key. Does MySQL create an hide primary key? Regards, Geilson Coutinho Figueiredo. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL

Re: PRIMARY KEY in mysql 4.0.18

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

Changing the primary key

Hi I´'m new with mysql and have following question: I have a table, that has a primary key with two columns and want to add a third column to this primary key. Is this possible and when yes: Do I have to delete all tables, that reference to this table? Best regards Michael R.

Re: Changing the primary key

Hello Michael, Wednesday, March 3, 2004, 1:40:00 PM, you wrote: RM I have a table, that has a primary key with two columns and want to add a RM third column to this primary key. RM Is this possible and when yes: Do I have to delete all tables, that RM reference to this table? No, you don't

AW: Changing the primary key

Hi Richard At first, thank you for your answer. I've tried your solution, but when I try to drop the PRIMARY KEY I get following error: [localhost] ERROR 1025: Error on rename of '.\austro\#sql-280_110' to '.\austro\fluege' (errno: 150) Best regards Michael R. -- MySQL General Mailing List

RE: Changing the primary key

preserved body, but rather a skid in broadside, thoroughly used, totally worn, and loudly proclaiming: WOW! What a ride! -Original Message- From: Richard Davey [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 03, 2004 7:53 AM To: [EMAIL PROTECTED] Subject: Re: Changing the primary key Hello

AW: Changing the primary key

Hi! At first thanks for your help. Now I can change the PRIMARY KEY of the table, but now I have a new question: How can I change the FOREIGN KEYS? Best regards Michael R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com

Re: Changing the primary key

Ranetbauer, Michael [EMAIL PROTECTED] wrote: Hi Richard At first, thank you for your answer. I've tried your solution, but when I try to drop the PRIMARY KEY I get following error: [localhost] ERROR 1025: Error on rename of '.\austro\#sql-280_110' to '.\austro\fluege' (errno: 150

Re: Changing the primary key

Ranetbauer, Michael [EMAIL PROTECTED] wrote on 03/03/2004 13:40:00: I have a table, that has a primary key with two columns and want to add a third column to this primary key. Is this possible and when yes: Do I have to delete all tables, that It is possible, and you do not have

Re: Changing the primary key

Ranetbauer, Michael [EMAIL PROTECTED] wrote: Hi! At first thanks for your help. Now I can change the PRIMARY KEY of the table, but now I have a new question: How can I change the FOREIGN KEYS? You can drop old FOREIGN KEY with ALTER TABLE .. DROP FOREIGN KEY statement: http

Re: Changing the primary key

You can ALTER TABLE http://www.mysql.com/doc/en/ALTER_TABLE.html I´'m new with mysql and have following question: I have a table, that has a primary key with two columns and want to add a third column to this primary key. Is this possible and when yes: Do I have to delete all tables

HOWTO add Primary Key to Existing Table

How can I add an auto-incrementing primary key to an existing table? MySQL version 4.0 Thank You -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

<    1   2   3   4   5   6   >