Re: multiple primary keys on one table?
I have a primary key set on a table which consists of the combination of the values: firstname, lastname, and a schedule_id (BIGINT(20))... I have this so the records in this table do not have duplicates, being that no one record should have the exact same name and schedule_id identifier. However, I want to keep this same restriction while also ensuring that no two records have the same email_address and schedule_id identifier... You can't have the db enforce two different primary keys on one table, so how would I implement having this kind of restriction, which, in itself, seems to require that I have a second primary key to enforce another constraint to dissalow records to be added that carry the same combination of: email_address and schedule_id? You cannot have multiple PRIMARY key constraints, that's why it's called primary. You can, however, use multiple unique constraints, which do (almost) the same. With regard to other replies: indices are used for quick data retrieval, constraints for business requirements. That there happens to be something as a unique index is an implementation artifact. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: multiple primary keys on one table?
You will have to UPDATE to NULL those fields, modify the aplication to enter NULL instead of empty-string... Then add the UNIQUE INDEX... In MySQL (unless modified) the dafault is that NULL values are incomparable thus allowing the creation of a UNIQUE INDEX. -- -- -- -- -- -- -- -- -- -- -- -- -- -- Gabriel PREDA Senior Web Developer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: multiple primary keys on one table?
right... - Original Message - From: Gabriel PREDA [EMAIL PROTECTED] To: Ferindo Middleton [EMAIL PROTECTED] Cc: Dan Buettner [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Friday, October 06, 2006 6:31 PM Subject: Re: multiple primary keys on one table? You will have to UPDATE to NULL those fields, modify the aplication to enter NULL instead of empty-string... Then add the UNIQUE INDEX... In MySQL (unless modified) the dafault is that NULL values are incomparable thus allowing the creation of a UNIQUE INDEX. -- -- -- -- -- -- -- -- -- -- -- -- -- -- Gabriel PREDA Senior Web Developer -- 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: multiple primary keys on one table?
Ferindo, you can create multiple UNIQUE indexes on a table to enforce your data requirements. http://dev.mysql.com/doc/refman/5.0/en/alter-table.html Dan On 10/5/06, Ferindo Middleton [EMAIL PROTECTED] wrote: I have a primary key set on a table which consists of the combination of the values: firstname, lastname, and a schedule_id (BIGINT(20))... I have this so the records in this table do not have duplicates, being that no one record should have the exact same name and schedule_id identifier. However, I want to keep this same restriction while also ensuring that no two records have the same email_address and schedule_id identifier... You can't have the db enforce two different primary keys on one table, so how would I implement having this kind of restriction, which, in itself, seems to require that I have a second primary key to enforce another constraint to dissalow records to be added that carry the same combination of: email_address and schedule_id? just ferindo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: multiple primary keys on one table?
At 06:26 PM 10/5/2006, Ferindo Middleton wrote: I have a primary key set on a table which consists of the combination of the values: firstname, lastname, and a schedule_id (BIGINT(20))... I have this so the records in this table do not have duplicates, being that no one record should have the exact same name and schedule_id identifier. However, I want to keep this same restriction while also ensuring that no two records have the same email_address and schedule_id identifier... You can't have the db enforce two different primary keys on one table, so how would I implement having this kind of restriction, which, in itself, seems to require that I have a second primary key to enforce another constraint to dissalow records to be added that carry the same combination of: email_address and schedule_id? just ferindo unique index M. -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.407 / Virus Database: 268.12.13/463 - Release Date: 10/4/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: multiple primary keys on one table?
Thanks. I now have this issue where I can't create the unique index on this table because the email_address is often times blank or unknown for a while (The front-end_application sets is value to '' (blank) on inserts and updates if the user doesn't enter it so instead of going in as NULL, the field gets a blank field value... Is there a way to get UNIQUE index to treat blank data fields that are part of the index as null and not enforce the constraint if the subject columns are blank (whitespace)... Do I need to force the application to not pas the value into the field and make it NULL instead. just ferindo On 10/5/06, Dan Buettner [EMAIL PROTECTED] wrote: Ferindo, you can create multiple UNIQUE indexes on a table to enforce your data requirements. http://dev.mysql.com/doc/refman/5.0/en/alter-table.html Dan On 10/5/06, Ferindo Middleton [EMAIL PROTECTED] wrote: I have a primary key set on a table which consists of the combination of the values: firstname, lastname, and a schedule_id (BIGINT(20))... I have this so the records in this table do not have duplicates, being that no one record should have the exact same name and schedule_id identifier. However, I want to keep this same restriction while also ensuring that no two records have the same email_address and schedule_id identifier... You can't have the db enforce two different primary keys on one table, so how would I implement having this kind of restriction, which, in itself, seems to require that I have a second primary key to enforce another constraint to dissalow records to be added that carry the same combination of: email_address and schedule_id? just ferindo
Re: Multiple primary keys
Quoting nngau [EMAIL PROTECTED]: Can someone figure out what's going on. This is the only change I made to this table. Basically I don't want any duplicate rows, so I setup 4 fields to be my primary key. If you don't want any duplicate rows, use UNIQUE, all those primary keys will just take up unwanted space. If you still think these fields should be indexed due to the large number of hits, then use an INDEX. Chris White -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple primary keys
On 4/27/06, nngau [EMAIL PROTECTED] wrote: Can someone figure out what's going on. This is the only change I made to this table. Basically I don't want any duplicate rows, so I setup 4 fields to be my primary key. When I do a simple select query it takes nearly 30 seconds to complete. This is affecting my websites and taking a very long time to query the Products. Have I setup this table right? Thank You!! +-+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-+---+ | itemID | int(6) | | PRI | 0 | | | classA | int(3) | | PRI | 0 | | | classB | int(3) | | PRI | 0 | | | classC | int(3) | | PRI | 0 | | | picture | varchar(10) | YES | | NULL| | | sex | char(2) | YES | | NULL| | +-+-+--+-+-+---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] You can avoid duplicate rows with only one primary key, unless you have a specific reason for having your primary key span on four columns. Your query being slower depends on how you have indexed your columns relating to your queries. Please give us a sample query which is running slowly.. Kishore Jalleda http://kjalleda.googlepages.com/projects
RE: Multiple primary keys
Thanks all. The query I run is a subquery. I noticed joined query run a lot faster than the sub. This is the subquery: select * from class_c where detail_id in (select classC from item_classification where classb=216) order by detail; This query takes nearly 3 minutes, before it did not take that long. I guess I should use a primary key As an index. I want to be able to add items that I can classify into different classa, classb or classc. Example: Itemid 1025 ClassA: 101 classB: 218 classC: 356 Same item can be put into another class. Itemid 105 ClassA: 101 classb: 218 classC: 357 So not having a primary key/index will slow my queries? -Original Message- From: Kishore Jalleda [mailto:[EMAIL PROTECTED] Sent: Thursday, April 27, 2006 10:49 AM To: nngau Cc: mysql@lists.mysql.com Subject: Re: Multiple primary keys On 4/27/06, nngau [EMAIL PROTECTED] wrote: Can someone figure out what's going on. This is the only change I made to this table. Basically I don't want any duplicate rows, so I setup 4 fields to be my primary key. When I do a simple select query it takes nearly 30 seconds to complete. This is affecting my websites and taking a very long time to query the Products. Have I setup this table right? Thank You!! +-+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-+---+ | itemID | int(6) | | PRI | 0 | | | classA | int(3) | | PRI | 0 | | | classB | int(3) | | PRI | 0 | | | classC | int(3) | | PRI | 0 | | | picture | varchar(10) | YES | | NULL| | | sex | char(2) | YES | | NULL| | +-+-+--+-+-+---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] You can avoid duplicate rows with only one primary key, unless you have a specific reason for having your primary key span on four columns. Your query being slower depends on how you have indexed your columns relating to your queries. Please give us a sample query which is running slowly.. Kishore Jalleda http://kjalleda.googlepages.com/projects -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple primary keys
MySQL will not use the primary key unless you use the left most columns. For a 1 column primary key then it's easy. For a 2 column primary key you can either use the 1st column in the primary key or both columns. If you only reference the 2nd column the query will not use the primary key and will do a full table scan. In your case you are referencing classb which is not the left most collumn in the primary key set. Put the word explain preceding the statement and execute the query. it will show you what keys are used in the query and in your case it is none. You either need to define another KEY with classb as the 1st column in the definition or if all of your queries at least reference classb then you could rebuild the primary key and put classb as the 1st entry in the definition. - Original Message - From: nngau [EMAIL PROTECTED] To: 'Kishore Jalleda' [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Thursday, April 27, 2006 9:11 AM Subject: RE: Multiple primary keys Thanks all. The query I run is a subquery. I noticed joined query run a lot faster than the sub. This is the subquery: select * from class_c where detail_id in (select classC from item_classification where classb=216) order by detail; This query takes nearly 3 minutes, before it did not take that long. I guess I should use a primary key As an index. I want to be able to add items that I can classify into different classa, classb or classc. Example: Itemid 1025 ClassA: 101 classB: 218 classC: 356 Same item can be put into another class. Itemid 105 ClassA: 101 classb: 218 classC: 357 So not having a primary key/index will slow my queries? -Original Message- From: Kishore Jalleda [mailto:[EMAIL PROTECTED] Sent: Thursday, April 27, 2006 10:49 AM To: nngau Cc: mysql@lists.mysql.com Subject: Re: Multiple primary keys On 4/27/06, nngau [EMAIL PROTECTED] wrote: Can someone figure out what's going on. This is the only change I made to this table. Basically I don't want any duplicate rows, so I setup 4 fields to be my primary key. When I do a simple select query it takes nearly 30 seconds to complete. This is affecting my websites and taking a very long time to query the Products. Have I setup this table right? Thank You!! +-+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-+---+ | itemID | int(6) | | PRI | 0 | | | classA | int(3) | | PRI | 0 | | | classB | int(3) | | PRI | 0 | | | classC | int(3) | | PRI | 0 | | | picture | varchar(10) | YES | | NULL| | | sex | char(2) | YES | | NULL| | +-+-+--+-+-+---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] You can avoid duplicate rows with only one primary key, unless you have a specific reason for having your primary key span on four columns. Your query being slower depends on how you have indexed your columns relating to your queries. Please give us a sample query which is running slowly.. Kishore Jalleda http://kjalleda.googlepages.com/projects -- 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]
