MySQL problem with varchar primary key
-
Hello, I am currently porting software for my University from MSSql to MySql. I am having some problems and would really appreciate any help you could give. I am unable to change the core design of the database tables because another student is working on software to process the resulting data, and he is hard coding everything. I have expressed how this is a bad design idea, but to no avail. So I am stuck with the current design. In this design, the tables use a varchar(8000) as a primary key for relating the tables. This is automagically cast into a TEXT field by MySql, and it of course throws the error that a TEXT field as a primary key must have an index. I went ahead and gave indexes to all those text fields and everything on that part works great. However, later in the database creation there are some constraint commands put on these tables that use those TEXT fields as the foreign key, and when it sees that this foreign key is a TEXT field it throws an error that a lenght must be specified. I have no idea (after reading the manual again) how to specify a length in the constraint command. If you know how please let me know, I can post the code if that would help. Thanks Tim Reynolds
-
Hello, I am currently porting software for my University from MSSql to MySql. I am having some problems and would really appreciate any help you could give. I am unable to change the core design of the database tables because another student is working on software to process the resulting data, and he is hard coding everything. I have expressed how this is a bad design idea, but to no avail. So I am stuck with the current design. In this design, the tables use a varchar(8000) as a primary key for relating the tables. This is automagically cast into a TEXT field by MySql, and it of course throws the error that a TEXT field as a primary key must have an index. I went ahead and gave indexes to all those text fields and everything on that part works great. However, later in the database creation there are some constraint commands put on these tables that use those TEXT fields as the foreign key, and when it sees that this foreign key is a TEXT field it throws an error that a lenght must be specified. I have no idea (after reading the manual again) how to specify a length in the constraint command. If you know how please let me know, I can post the code if that would help. Thanks Tim Reynolds
razialx wrote: from MSSql to MySql. Why do you need to do this ? If it's a cost issue, have you looked into MSDE ? razialx wrote: In this design, the tables use a varchar(8000) as a primary key for relating the tables. Your co student is an idiot. I can't think of a worse design, no matter how hard I try. I'd urge you to distance yourself from this cowpat of a project ASAP. A primary key is an index, it needs to be easy to search. A varchar(8000) is about as far from that as you can get. Christian Graus - Microsoft MVP - C++
-
razialx wrote: from MSSql to MySql. Why do you need to do this ? If it's a cost issue, have you looked into MSDE ? razialx wrote: In this design, the tables use a varchar(8000) as a primary key for relating the tables. Your co student is an idiot. I can't think of a worse design, no matter how hard I try. I'd urge you to distance yourself from this cowpat of a project ASAP. A primary key is an index, it needs to be easy to search. A varchar(8000) is about as far from that as you can get. Christian Graus - Microsoft MVP - C++
Christian Graus wrote: Your co student is an idiot. I can't think of a worse design, no matter how hard I try. I bet he gets lots of warning messages too saying that the design makes it possible to create a valid row greater that 8000 bytes which cannot be processed. Since I've never gone nuts like this I've never seen the actual message so I'm not sure what it says, but I'm told it exists.
My: Blog | Photos | Next SQL Presentation WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
-
razialx wrote: from MSSql to MySql. Why do you need to do this ? If it's a cost issue, have you looked into MSDE ? razialx wrote: In this design, the tables use a varchar(8000) as a primary key for relating the tables. Your co student is an idiot. I can't think of a worse design, no matter how hard I try. I'd urge you to distance yourself from this cowpat of a project ASAP. A primary key is an index, it needs to be easy to search. A varchar(8000) is about as far from that as you can get. Christian Graus - Microsoft MVP - C++
Christian Graus wrote: I can't think of a worse design, no matter how hard I try. I'd urge you to distance yourself from this cowpat of a project ASAP Ah! I so much agree! :) Christian Graus wrote: A primary key is an index, it needs to be easy to search I suspect the original designer (designer?) was using some kind of guid as unique key because he/she did not wish to or know how to let the database generate the key. Sorry I can't give you any practical advise to solve your current problem.
-
Christian Graus wrote: I can't think of a worse design, no matter how hard I try. I'd urge you to distance yourself from this cowpat of a project ASAP Ah! I so much agree! :) Christian Graus wrote: A primary key is an index, it needs to be easy to search I suspect the original designer (designer?) was using some kind of guid as unique key because he/she did not wish to or know how to let the database generate the key. Sorry I can't give you any practical advise to solve your current problem.
Guys, I know its terrible. I don't have a choice here. I will explain a little about the project and my role in it... This other student developed an online questionnaire program for use in teacher evaluations. I did not initiate this project or have any part in its design... I wish I had at this point. This last semester I was in a Windows Programming course, and having developed quite a bit of software in .NET I was allowed to work on a variety of projects in place of creating console based Hello, World! demos. The last project I was placed on was this. The project was already considered 'done' when I started working on it, they just wanted me to convert it from a retail database into an open database (the only open source support I have seen at this university...). When I inquired about this terrible design, I was told it was this way so that question numbers could be represented as strings and be kept unique. By this I mean the data could be described as such: "Q1" "Did your teacher give you a better understanding of the subject material?" "bool" "Q1.1" "Yes" "Q1.2" "No" Right, well thats all good until you consider just using an auto-incrementing integer as the primary key to reference things and attributing the question names as being unique. I must also restate that I can NOT change this design, no matter how difficult it is to accept... /sigh. I can still only plead with you folks and your experience on this, because I must get it working. If we were to only consider the varchars as being 10 in lenght (because if someone were to show me a question number 8k long, I would quit programming!). Do you know how I could use this as a foreign key in a constraint statement? If you know for a fact that it will not be possible, tell me so I can try another avenue. And just to pull the sympathy card on my plight, I have to piece together all SQL statements from 50+ lines of : strCmd += "..."; strCmd += "..."; ... strCmd += "..."; and so on and so on. Because putting the SQL statements into an editable format would be out of the question. Thank you for those who have replied thus far, I hope you can help me come to a solution on my problem. Tim