Re: why NOT NULL in PRIMARY key??
create table a ( b int not null, c int null primary_key(b,c) ); With values: 1 null 1 null Logically these are unique records under the standard proviso that null != null. Yet how could I uniquely identify the first row to delete that row? First of all NULL is neither equal nor not-equal to NULL. The outcome of comparing NULL to NULL is unknown AKA 'NULL' ;-) Secondly, the previous line is only true for comparing values within queries. If it comes to UNIQUE indexes than most of the time NULL != NULL, but in the case of BDB tables NULL = NULL (only a single NULL entry can be present in the UNIQUE index of a BDB table). But an unique INDEX is not a Primary Key constraint. So actually that has nothing to do with it :-) While BDB tables are not the role model for other table types I used this example to indicate that MySQL is capable of handling NULLs this way. Thirdly, if NULL=NULL for such an index then the secod row could not have been inserted in the first place. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server Upscene Productions http://www.upscene.com -- 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??
From: [EMAIL PROTECTED] create table a ( b int not null, c int null primary_key(b,c) ); With values: 1 null 1 null Logically these are unique records under the standard proviso that null != null. Yet how could I uniquely identify the first row to delete that row? First of all NULL is neither equal nor not-equal to NULL. The outcome of comparing NULL to NULL is unknown AKA 'NULL' ;-) Secondly, the previous line is only true for comparing values within queries. If it comes to UNIQUE indexes than most of the time NULL != NULL, but in the case of BDB tables NULL = NULL (only a single NULL entry can be present in the UNIQUE index of a BDB table). While BDB tables are not the role model for other table types I used this example to indicate that MySQL is capable of handling NULLs this way. Thirdly, if NULL=NULL for such an index then the secod row could not have been inserted in the first place. Regards, Jigal. -- 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??
Jigal van Hemert [EMAIL PROTECTED] writes: 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 always did it this way. Let's look at it from a pure logic point of view. Given the table: create table a ( b int not null, c int null primary_key(b,c) ); With values: 1 null 1 null Logically these are unique records under the standard proviso that null != null. Yet how could I uniquely identify the first row to delete that row? Brad Eacker ([EMAIL PROTECTED]) -- 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??
From: Frank Bax At 11:07 AM 4/27/05, Jigal van Hemert wrote: So, if we would define that the key entry 0-NULL-Whatever equals 0-NULL-Whatever (which MySQL is capable of if you look at the definition of UNIQUE indexes with BDB tables) then allowing NULLs as part of a key entry would not permit duplicate entries. At least not more than allowing other values. You cannot define that those keys are equal, because SQL standard states that 0-NULL-Whatever is *always* *not-equal* to 0-NULL-Whatever. Allowing only a single NULL entry in a UNIQUE index of BDB tables would also imply that in at least one case a new NULL value is treated as equal to the NULL that is already present in the index. A duplicate key error would IMHO mean that a value that one tried to insert is equal to a value that is already present in the index... But you are also not quite right stating that 0-NULL-Whatever is not equal to 0-NULL-Whatever. Comparing two NULLs will not result in equal or not equal, but in unknown (represented by NULL) ;-P Regards, Jigal. -- 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??
From: Mikhail Entaltsev Mikhail, Before inserting MySQL will try to find a record with the same values. But since comparison with NULL value returns always FALSE MySQL will think that there is no such record in the table. After this point you will get 2 identical records in the table. MySQL as a whole seems a bit more flexible: a UNIQUE index allows multiple NULL values (which seems to follow the NULL handling you describe), but with BDB table only a single NULL value is allowed in a UNIQUE index (so MySQL seems to be able to find a NULL value in de db). Anyway, I gues it's just a question of following a standard and optimizing the engine according to that standard. Regards, Jigal. -- 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??
On 4/26/05, Jay Blanchard [EMAIL PROTECTED] wrote: [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 create row 4. No. NULL is not a value. It is a lack of value. Kind of a special symbol, like infinity in mathematics. You cannot say that infinity = infinity or infinity infinity. Same as you cannot say that NULL = NULL or NULL NULL. Regards, Dawid -- 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??
[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 create row 4. No. NULL is not a value. It is a lack of value. Kind of a special symbol, like infinity in mathematics. You cannot say that Actually, it's not even the lack of value. NULL is a state. A column can have two states: NULL or NON NULL. Hence: WHERE mycolumn IS NOT NULL or mycolumn IS NULL Besides a non-null state, it can have many values :-) With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server Upscene Productions http://www.upscene.com -- 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??
Jigal Anyway, I gues it's just a question of following a standard and optimizing the engine according to that standard. Your proposal would permit dupe primary keys. It's a question of preventing them. PB - Jigal van Hemert wrote: From: "Mikhail Entaltsev" Mikhail, Before inserting MySQL will try to find a record with the same values. But since comparison with NULL value returns always FALSE MySQL will think that there is no such record in the table. After this point you will get 2 identical records in the table. MySQL as a whole seems a bit more flexible: a UNIQUE index allows multiple NULL values (which seems to follow the NULL handling you describe), but with BDB table only a single NULL value is allowed in a UNIQUE index (so MySQL seems to be able to find a NULL value in de db). Anyway, I gues it's just a question of following a standard and optimizing the engine according to that standard. Regards, Jigal. No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005 -- 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??
From: Peter Brawley Peter, Anyway, I gues it's just a question of following a standard and optimizing the engine according to that standard. Your proposal would permit dupe primary keys. It's a question of preventing them. Sorry, but I disagree. If NULL handling is not done by the table engine but by the rest of MySQL then MySQL can compare two NULLs and can act in different ways depending on the situation (UNIQUE index in BDB can only have a single NULL entry, but MySQL allows multiple NULLs in other table types) So, if we would define that the key entry 0-NULL-Whatever equals 0-NULL-Whatever (which MySQL is capable of if you look at the definition of UNIQUE indexes with BDB tables) then allowing NULLs as part of a key entry would not permit duplicate entries. At least not more than allowing other values. Because there is a considerable performance difference between primary and secudary keys in InnoDB it would enable more flexible primary keys that can also be used for searching. It would mean breaking with the standard (PRIMARY = UNIQUE + NOT NULL), so it's not likely that it will ever be introduced ;-) Anyway, I need NULLs in the db (for various reasons which we haven't been able to solve in other ways) but I need fast searching on name/value. So I have to figure out a different method. Thanks for thinking about this! Regards, Jigal. -- 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??
At 11:07 AM 4/27/05, Jigal van Hemert wrote: So, if we would define that the key entry 0-NULL-Whatever equals 0-NULL-Whatever (which MySQL is capable of if you look at the definition of UNIQUE indexes with BDB tables) then allowing NULLs as part of a key entry would not permit duplicate entries. At least not more than allowing other values. You cannot define that those keys are equal, because SQL standard states that 0-NULL-Whatever is *always* *not-equal* to 0-NULL-Whatever. -- 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??
Jigal, I think MS-SQL and Oracle provide switches for treating NULLs as values, which is what your proposal amounts to. It seems to me that much of the performance advantage you are counting on from PKs would go away if PKs could have NULLs and if NULL were a value. IAC I hope MySQL doesn't go that way. PB - Jigal van Hemert wrote: From: "Peter Brawley" Peter, Anyway, I gues it's just a question of following a standard and optimizing the engine according to that standard. Your proposal would permit dupe primary keys. It's a question of preventing them. Sorry, but I disagree. If NULL handling is not done by the table engine but by the rest of MySQL then MySQL can compare two NULLs and can act in different ways depending on the situation (UNIQUE index in BDB can only have a single NULL entry, but MySQL allows multiple NULLs in other table types) So, if we would define that the key entry "0-NULL-Whatever" equals "0-NULL-Whatever" (which MySQL is capable of if you look at the definition of UNIQUE indexes with BDB tables) then allowing NULLs as part of a key entry would not permit duplicate entries. At least not more than allowing other values. Because there is a considerable performance difference between primary and secudary keys in InnoDB it would enable more flexible primary keys that can also be used for searching. It would mean breaking with the standard (PRIMARY = UNIQUE + NOT NULL), so it's not likely that it will ever be introduced ;-) Anyway, I need NULLs in the db (for various reasons which we haven't been able to solve in other ways) but I need fast searching on name/value. So I have to figure out a different method. Thanks for thinking about this! Regards, Jigal. No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.3 - Release Date: 4/25/2005 -- 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??
Hi, let me fall in here. Jigal van Hemert wrote: From: Peter Brawley Peter, [[...]] Your proposal would permit dupe primary keys. It's a question of preventing them. Sorry, but I disagree. If NULL handling is not done by the table engine but by the rest of MySQL then MySQL can compare two NULLs and can act in different ways depending on the situation (UNIQUE index in BDB can only have a single NULL entry, but MySQL allows multiple NULLs in other table types) The handling of values (equality, ordering, ...) should be done at the lowest possible level, for performance reasons. If a system acts in different ways depending on the situation, it lacks systematic properties and is difficult to use and to explain. (Correct, often the situation is important, but if you define the equality of values as depending on the key property of the column then you open a can of worms!) BDB is a special case which you should not take as a model IMO. So, if we would define that the key entry 0-NULL-Whatever equals 0-NULL-Whatever [[...]] Your best way of reaching this is to use some other valuefor the purpose you were going to use NULL for. Let me add an aspect which I did not find mentioned yet: The SQL syntax differs for NULL and non-NULL values! As long as your key columns have the NOT NULL property, you can alwas say WHERE keycol = value. When you qualify by a NULL value, you need to say col IS NULL. This will add complexity to your application(s). [[...]] It would mean breaking with the standard (PRIMARY = UNIQUE + NOT NULL), so it's not likely that it will ever be introduced ;-) I sure hope it will not be, for various reasons. Anyway, I need NULLs in the db (for various reasons which we haven't been able to solve in other ways) but I need fast searching on name/value. So I have to figure out a different method. I hope you get it solved! Regards, Jörg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
why NOT NULL in PRIMARY key??
http://dev.mysql.com/doc/mysql/en/silent-column-changes.html mentions that Columns that are part of a PRIMARY KEY are made NOT NULL even if not declared that way. 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 (and silently). Why is this? I actually do need NULL values (they really are not equal to '0', etc.), but also need this column as part of the PRIMARY key in an InnoDB table... 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. Regards, Jigal. -- 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??
[snip] http://dev.mysql.com/doc/mysql/en/silent-column-changes.html mentions that Columns that are part of a PRIMARY KEY are made NOT NULL even if not declared that way. 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 (and silently). Why is this? I actually do need NULL values (they really are not equal to '0', etc.), but also need this column as part of the PRIMARY key in an InnoDB table... 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. [/snip] 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 you a more informative response. There is a vast difference between a zero value, blank value and NULL (absence of value). -- 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??
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. If duplicate key values exist, the CREATE INDEX statement is canceled and an error message giving the first duplicate is returned. Multiple NULL values are considered duplicates when UNIQUE index is created. SYBASE: Create Unique Index Prohibits duplicate index (also called key) values. The system checks for duplicate key values when the index is created (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. Ed -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 26, 2005 6:50 AM To: Jigal van Hemert; mysql@lists.mysql.com Subject: RE: why NOT NULL in PRIMARY key?? 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 you a more informative response. There is a vast difference between a zero value, blank value and NULL (absence of value). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- 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??
http://dev.mysql.com/doc/mysql/en/silent-column-changes.html mentions that Columns that are part of a PRIMARY KEY are made NOT NULL even if not declared that way. 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 (and silently). Why is this? I actually do need NULL values (they really are not equal to '0', etc.), but also need this column as part of the PRIMARY key in an InnoDB table... 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. 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 database? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server Upscene Productions http://www.upscene.com -- 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??
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 you a more informative response. There is a vast difference between a zero value, blank value and NULL (absence of value). The situation is pretty challenging: - because of high concurrency and huge tables I need to use InnoDB - InnoDB really needs a PRIMARY key for fast results - a PRIMARY key does not allow NULL values as part of the key Anyhow, we something to think about... Regards, Jigal. -- 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??
On 4/26/05, Jigal van Hemert wrote: http://dev.mysql.com/doc/mysql/en/silent-column-changes.html mentions that Columns that are part of a PRIMARY KEY are made NOT NULL even if not declared that way. 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 (and silently). Why is this? Because the SQL standard says so. 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??
At 08:49 AM 4/26/05, Jay Blanchard wrote: [snip] http://dev.mysql.com/doc/mysql/en/silent-column-changes.html mentions that Columns that are part of a PRIMARY KEY are made NOT NULL even if not declared that way. 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 (and silently). Why is this? I actually do need NULL values (they really are not equal to '0', etc.), but also need this column as part of the PRIMARY key in an InnoDB table... 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. [/snip] 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 you a more informative response. I'm not on dev team, but my understanding of RDMS theory is that retrieving data via PK will always return a single row from the table. Since NULL values are never considered equal to each other, allowing them in a column that is part of PK would bypass this expected behaviour. -- 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??
Ed, UNIQUE indexes (which you quote) and PRIMARY KEYS are similar but are NOT the same thing. Both types of keys guard against duplication of values for the tuple defining the index. However, PRIMARY KEYs hold special significance in that many RDBMS storage engines will use the PK to uniquely identify each row and not use an internally generated ROWID (at least that happens for InnoDB). PKs are critical to data integrity (and usually internal table organization,too) and are frequently used as one side of a Foreign Key (FK) relationship. http://dev.mysql.com/doc/mysql/en/table-and-index.html = If you do not define a PRIMARY KEY for your table, MySQL picks the first UNIQUE index that has only NOT NULL columns as the primary key and InnoDB uses it as the clustered index. If there is no such index in the table, InnoDB internally generates a clustered index where the rows are ordered by the row ID that InnoDB assigns to the rows in such a table. = http://dev.mysql.com/doc/mysql/en/create-table.html = In MySQL, a UNIQUE index is one in which all values in the index must be distinct. An error occurs if you try to add a new row with a key that matches an existing row. The exception to this is that if a column in the index is allowed to contain NULL values, it can contain multiple NULL values. This exception does not apply to BDB tables, for which an indexed column allows only a single NULL. = 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 (and silently). A table can have only one PRIMARY KEY. If you don't have a PRIMARY KEY and an application asks for the PRIMARY KEY in your tables, MySQL returns the first UNIQUE index that has no NULL columns as the PRIMARY KEY. = Notice that UNIQUE indexes in MySQL allow multiple nullable columns. However because of their critical importance to record identification, PRIMARY KEYs cannot contain null values. This is true even for the other systems you quoted http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_04_888k.asp (MSDN online documentation for MS SQL Server) A table usually has a column or combination of columns whose values uniquely identify each row in the table. This column (or columns) is called the primary key of the table and enforces the entity integrity of the table. You can create a primary key by defining a PRIMARY KEY constraint when you create or alter a table. A table can have only one PRIMARY KEY constraint, and a column that participates in the PRIMARY KEY constraint cannot accept null values. Because PRIMARY KEY constraints ensure unique data, they are often defined for identity column. === http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@Generic__BookTextView/21064 (online Sybase Transact-SQL User's guide) = You can declare unique or primary key constraints to ensure that no two rows in a table have the same values in the specified columns. Both constraints create unique indexes to enforce this data integrity. However, primary key constraints are more restrictive than unique constraints. Columns with primary key constraints cannot contain a NULL value. You normally use a table's primary key constraint in conjunction with referential integrity constraints defined on other tables. = Respectfully, Shawn Green Database Administrator Unimin Corporation - Spruce Pine [EMAIL PROTECTED] wrote on 04/26/2005 09:16:03 AM: 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. If duplicate key values exist, the CREATE INDEX statement is canceled and an error message giving the first duplicate is returned. Multiple NULL values are considered duplicates when UNIQUE index is created. SYBASE: Create Unique Index Prohibits duplicate index (also called key) values. The system checks for duplicate key values when the index is created (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. Ed -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 26, 2005 6:50 AM To: Jigal van Hemert; mysql@lists.mysql.com Subject: RE: why NOT NULL in PRIMARY key?? Since NULL is the absence
Re: why NOT NULL in PRIMARY key??
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 database? Martijn, The table contains an variable number of integer parameters for accounts: 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 (on might argue that a part of the name would be sufficient, but that is not the issue here). Indeed almost all queries are very fast. A NULL value indicates that it is a mandatory field that was not filled with (a valid) value or that the value was reset, or... Deleting the record is also not very nice, since it will prevent the use of (inner) joins. I have to think back why we dropped the left joins and started using NULL values, but the first thing that came to mind was an increase in speed. An extra challenge: the queries are generated because of the large number of different queries that might be used. We can switch between left joins and inner joins quickly though. Regards, Jigal. -- 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??
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 you a more informative response. There is a vast difference between a zero value, blank value and NULL (absence of value). The situation is pretty challenging: - because of high concurrency and huge tables I need to use InnoDB - InnoDB really needs a PRIMARY key for fast results - a PRIMARY key does not allow NULL values as part of the key Anyhow, we something to think about... Regards, Jigal. 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. Such a unique index therefore cannot be a primary key. However, for the purposes you describe above, it sounds like the solution is simply to define your columns as allowing NULL, and to create your index using UNIQUE rather than PRIMARY KEY. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- 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??
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 database? Martijn, The table contains an variable number of integer parameters for accounts: 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 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. -- 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??
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 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. It could well be a UNIQUE index with the restriction that the complete key (the parts combined) may not be NULL... Such a unique index therefore cannot be a primary key. However, for the purposes you describe above, it sounds like the solution is simply to define your columns as allowing NULL, and to create your index using UNIQUE rather than PRIMARY KEY. Unfortunately ther is a big difference in performance between the primary and secudary indexes in InnoDB. We made (secundary) indexes and didn't have a primary index at all (so MySQL used a 64-bit integer as primary key). After we changed the index to primary the performance increased considerably. Some queries turned out to be quite slow and we found that these relied on NULL values. Converting the index to primary silently converted all NULL constraints to NOT NULL for the columns that are part of the primary key and converted all NULL values in the db to the appropriate default values for the various column types. Regards, Jigal. -- 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??
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. If duplicate key values exist, the CREATE INDEX statement is canceled and an error message giving the first duplicate is returned. Multiple NULL values are considered duplicates when UNIQUE index is created. SYBASE: Create Unique Index Prohibits duplicate index (also called key) values. The system checks for duplicate key values when the index is created (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. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server Upscene Productions http://www.upscene.com -- 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??
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 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 if you want to find the IDs which have a certain value in a parameter? Then you're searching for name and value and only need the id as the glue for joins. The sitution is that I have to store a variable (and changing) number of parameters for a large number of IDs. The traditional construction of a column for each parameter is not usable anymore: - modifying the table structure for new parameters requires a lot of time - all unused parameters will still take space - design of index(es) is virtually impossible because searches are made on various combinations of parameters The parameter table solution is rather simple, but has some performance complications once you have 26,000,000 records for 475,000 accounts in a 5,3GB database... How would *you* normalize this situation? All suggestions are welcome! Regards, Jigal. -- 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??
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 (and silently). Why is this? I actually do need NULL values (they really are not equal to '0', etc.), but also need this column as part of the PRIMARY key in an InnoDB table... 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 with which each row can be explicitly addressed. So if you have 2000 rows in a table, you can write 2000 SELECT statemens which will use columns in primary key and each of these SELECT statements will return exactly one (different) row. Since PRIMARY KEY is a primary key it cannot have NULL values. And there can be only one primary key on the table, for the same reason. If your PRIMARY KEY would allow NULL values, it would not be possible to address these rows with NULL values (*) and therefore it would not be a real primary key, by definiton. It would be a unique key. (*). Supposedly if there could be only one NULL value per column it might be possible, but since NULL means unknown, it should not be mixed with real values. From what you are saying, you need a UNIQUE key, not a PRIMARY KEY. Regards, Dawid -- 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??
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 of a PRIMARY key might not be NULL. Because a value in a PK should uniquely identify a row. Using one or more NULLs negates this... The best systems don't use NULLs at all. NULL is an abomination. You should only store values that are true. Eg: employee with number 1 has a name of Martijn. If salary is optional, do not make it nullable, but create a separate table employee_salaries. This way, there's never NULL confusion either. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server Upscene Productions http://www.upscene.com -- 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??
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 always did it this way. Regards, Jigal. -- 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??
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 (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. It could well be a UNIQUE index with the restriction that the complete key (the parts combined) may not be NULL... I'm not sure I understand what you are saying. But if part of a PRIMARY KEY could be NULL, then it _wouldn't_ uniquely identify records. Such a unique index therefore cannot be a primary key. However, for the purposes you describe above, it sounds like the solution is simply to define your columns as allowing NULL, and to create your index using UNIQUE rather than PRIMARY KEY. Unfortunately ther is a big difference in performance between the primary and secudary indexes in InnoDB. We made (secundary) indexes and didn't have a primary index at all (so MySQL used a 64-bit integer as primary key). After we changed the index to primary the performance increased considerably. Some queries turned out to be quite slow and we found that these relied on NULL values. Converting the index to primary silently converted all NULL constraints to NOT NULL for the columns that are part of the primary key and converted all NULL values in the db to the appropriate default values for the various column types. Regards, Jigal. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- 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??
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 a key with which each row can be explicitly addressed. So if you have 2000 rows in a table, you can write 2000 SELECT statemens which will use columns in primary key and each of these SELECT statements will return exactly one (different) row. With the NULL values included it will still uniquely identify each row... 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. If your PRIMARY KEY would allow NULL values, it would not be possible to address these rows with NULL values (*) and therefore it would not be a real primary key, by definiton. It would be a unique key. That would be true for the entire key, but not for each part of the key... Regards, Jigal. -- 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??
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. It could well be a UNIQUE index with the restriction that the complete key (the parts combined) may not be NULL... I'm not sure I understand what you are saying. But if part of a PRIMARY KEY could be NULL, then it _wouldn't_ uniquely identify records. It would IMHO if the other parts combined would be unique. In this case it's a table that contains account_id, parameter_name and value. Account_id and parameter_name would be sufficient to uniquely identify a records (only one parameter with the same name per account allowed). But since searches use the parameter_name/value combination in almost all cases 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. My combined key would be able to uniquely identify records. I know the SQL standard defines a PRIMARY KEY as a combination between UNIQUE and NOT NULL, but it's still not clear to me why this implies that all *parts* of the primary key *must* also have the NOT NULL constraint. Regards, Jigal. -- 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??
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 it is a PRIMARY KEY. I mean phrase 'PRIMARY KEY' means a key with which each row can be explicitly addressed. So if you have 2000 rows in a table, you can write 2000 SELECT statemens which will use columns in primary key and each of these SELECT statements will return exactly one (different) row. With the NULL values included it will still uniquely identify each row... 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, thus compromising uniqueness of that part, and of the entire key as well. Suppose you have a two part key on columns a and b, with b allowed to be NULL. Then you could have these values: x y x NULL x z x NULL Now, how do you uniquely identify the 2nd and 4th rows? If your PRIMARY KEY would allow NULL values, it would not be possible to address these rows with NULL values (*) and therefore it would not be a real primary key, by definiton. It would be a unique key. That would be true for the entire key, but not for each part of the key... You have it backwards. Any individual column in a primary key might contain duplicates. But the combination of values in all of the columns must be unique. Regards, Jigal. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- 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??
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 with which each row can be explicitly addressed. So if you have 2000 rows in a table, you can write 2000 SELECT statemens which will use columns in primary key and each of these SELECT statements will return exactly one (different) row. With the NULL values included it will still uniquely identify each row... 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. It isn't? Imagine this: mytable with PK (id, value) 1, NULL 1, NULL 2, value 2, NULL now, given that NULL NULL ... what row would 1, NULL identify? Or would you only allow a single NULL for each part of the PK? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server Upscene Productions http://www.upscene.com -- 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??
Jigal van Hemert [EMAIL PROTECTED] wrote on 04/26/2005 10:35:06 AM: 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 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 if you want to find the IDs which have a certain value in a parameter? Then you're searching for name and value and only need the id as the glue for joins. The sitution is that I have to store a variable (and changing) number of parameters for a large number of IDs. The traditional construction of a column for each parameter is not usable anymore: - modifying the table structure for new parameters requires a lot of time - all unused parameters will still take space - design of index(es) is virtually impossible because searches are made on various combinations of parameters The parameter table solution is rather simple, but has some performance complications once you have 26,000,000 records for 475,000 accounts in a 5,3GB database... How would *you* normalize this situation? All suggestions are welcome! Regards, Jigal. I think what tripped us up (and raised the red flags) was that you called your index on (id, name, value) a PRIMARY KEY. However, the unique combination of values on your parameters table should have only been for (id, name), right? The id-name combination will uniquely identify a parameter for an account (and each account cannot have the same parameter more than once, can it?). So your PK should be on (id, name) and a regular index could be placed on (id, name, value) as a covering index. However, your covering index will be a complete duplicate of your table and based on the numbers you give us, it probably won't fit into memory. You would end up trying to use paged memory to search an index to get at the value of the 'value' column just to avoid an extra trip to the disk to get the value based on an PK match. I don't think the extra effort (and disk space used and working memory consumed) would actually help your retrieval speed. If it were me, I would not index that table beyond the PK on just (id, name). But, I would still test it both ways just to be sure. I had a similar situation as you and chose a similar solution. My data storage challenge was to make millions of laboratory reports accessible for statistical analysis. Each report can contain any number of analyses on them (from 2 to 60) depending on what tests were requested when the sample was submitted to the lab. I also created a table for reports and a separate table for each test-result pair (fortunately for me, all of my results were numeric or could be represented with numbers). The two tables are linked through a FK (I am also using InnoDB). Not only is this saving space but the design is flexible because I do not need to change my tables every time the lab comes up with a new analysis it can perform (for the exact same reasons you described above). Don't get discouraged, OK? ;-) Shawn Green Database Administrator Unimin Corporation - Spruce Pine
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 it would be sufficient for a primary key IMHO. It could well be a UNIQUE index with the restriction that the complete key (the parts combined) may not be NULL... I'm not sure I understand what you are saying. But if part of a PRIMARY KEY could be NULL, then it _wouldn't_ uniquely identify records. It would IMHO if the other parts combined would be unique. If the other parts are unique, then you don't need the nullable column in your primary key. Just define it with the other columns. In this case it's a table that contains account_id, parameter_name and value. Account_id and parameter_name would be sufficient to uniquely identify a records (only one parameter with the same name per account allowed). But since searches use the parameter_name/value combination in almost all cases 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. My combined key would be able to uniquely identify records. I know the SQL standard defines a PRIMARY KEY as a combination between UNIQUE and NOT NULL, but it's still not clear to me why this implies that all *parts* of the primary key *must* also have the NOT NULL constraint. Regards, Jigal. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- 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??
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,value) values (1,NULL,12). Before inserting MySQL will try to find a record with the same values. But since comparison with NULL value returns always FALSE MySQL will think that there is no such record in the table. After this point you will get 2 identical records in the table. Mikhail. - Original Message - From: Jigal van Hemert [EMAIL PROTECTED] To: Dawid Kuroczko [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, April 26, 2005 4:47 PM Subject: 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 a key with which each row can be explicitly addressed. So if you have 2000 rows in a table, you can write 2000 SELECT statemens which will use columns in primary key and each of these SELECT statements will return exactly one (different) row. With the NULL values included it will still uniquely identify each row... 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. If your PRIMARY KEY would allow NULL values, it would not be possible to address these rows with NULL values (*) and therefore it would not be a real primary key, by definiton. It would be a unique key. That would be true for the entire key, but not for each part of the key... Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- 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??
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 NULL values, thus compromising uniqueness of that part, and of the entire key as well. Suppose you have a two part key on columns a and b, with b allowed to be NULL. Then you could have these values: x y x NULL x z x NULL Now, how do you uniquely identify the 2nd and 4th rows? 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? If your PRIMARY KEY would allow NULL values, it would not be possible to address these rows with NULL values (*) and therefore it would not be a real primary key, by definiton. It would be a unique key. That would be true for the entire key, but not for each part of the key... You have it backwards. Any individual column in a primary key might contain duplicates. But the combination of values in all of the columns must be unique. Which is the case! The same key definition would be valid as a UNIQUE key plus no combined key value is NULL. It just seems a matter of a definition in the SQL standard... Regards, Jigal. -- 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??
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 NULLs in it. My combined key would be able to uniquely identify records. What!?? NULL has no length. In key values like stringvalueNULLintvalue, what position in the key value is the engine supposed to consider the beginning of intvalue!? I know the SQL standard defines a PRIMARY KEY as a combination between UNIQUE and NOT NULL, but it's still not clear to me why this implies that all *parts* of the primary key *must* also have the NOT NULL constraint. Allow NULLs as PK components and you destroy two things at once, PK logic and performance. PB - 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 it would be sufficient for a primary key IMHO. It could well be a UNIQUE index with the restriction that the complete key (the parts combined) may not be NULL... I'm not sure I understand what you are saying. But if part of a PRIMARY KEY could be NULL, then it _wouldn't_ uniquely identify records. It would IMHO if the other parts combined would be unique. In this case it's a table that contains account_id, parameter_name and value. Account_id and parameter_name would be sufficient to uniquely identify a records (only one parameter with the same name per account allowed). But since searches use the parameter_name/value combination in almost all cases 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. My combined key would be able to uniquely identify records. I know the SQL standard defines a PRIMARY KEY as a combination between UNIQUE and NOT NULL, but it's still not clear to me why this implies that all *parts* of the primary key *must* also have the NOT NULL constraint. Regards, Jigal. No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005 -- 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??
[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 create row 4. -- 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??
In article [EMAIL PROTECTED], Jigal van Hemert [EMAIL PROTECTED] writes: 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 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* normalize this situation? All suggestions are welcome! Your table structure is fine AFAICS, but a primary key should span a minimum number of columns uniquely identifying a single row. value does not meet that need. -- 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??
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, thus compromising uniqueness of that part, and of the entire key as well. Suppose you have a two part key on columns a and b, with b allowed to be NULL. Then you could have these values: x y x NULL x z x NULL Now, how do you uniquely identify the 2nd and 4th rows? 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? Yes well, exactly the point: this is not possible in a primary key :-) With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server Upscene Productions http://www.upscene.com -- 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??
On 4/26/05, Jigal van Hemert wrote: From: Jochem van Dieten Why is this? Because the SQL standard says so. A true observation, but still no explanation or reason why ;-P I consider it a good enough explanation of why MySQL doesn't allow it. As to why the SQL standard doesn't allow it: NULL doesn't fit particularly well in relational theory and there has probably been considerable pressure from certain vendors (imagine the problems when an empty string is indistinguishable from a NULL so both '' = '' and NULL NULL must be true, but now not just for some wacky varchar but for your 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??
Martin, Shawn, you are correct. An oversight on my part...this is why I still follow this list, I am always able to learn something and never cease to be humbled. Ed -Original Message- From: Martijn Tonies [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 26, 2005 8:01 AM To: emierzwa; 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. If duplicate key values exist, the CREATE INDEX statement is canceled and an error message giving the first duplicate is returned. Multiple NULL values are considered duplicates when UNIQUE index is created. SYBASE: Create Unique Index Prohibits duplicate index (also called key) values. The system checks for duplicate key values when the index is created (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. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
