|
Just a dumb question about composite primary key.
I am designing a DB, then i have a specific problem that having 2 primary keys can only solve.
Now I am wondering if having 2 primary keys could hurt performance. Could it be?
I am using MySQL 5.6, and in MyISAM engine.
Any advice will be appreciated.
Thank you
|
|
|
|
|
There cannot be two primary keys; such is always a design-error.
You can have multiple fields that uniquely identify the field, but only one of them will be the actual primary key. The others are 'candidate keys', 'unique constraints'.
No, you don't need a primary key to define a relation, but I would urge to not to. And yes, it will hurt performance.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Ohh I see, thank you.
But why MySQL accept this query
CREATE TABLE IF NOT EXISTS `tbl_sample`
(
`tbl_sample_id` INT UNSIGNED NOT NULL,
`tracked_id` INT UNSIGNED NOT NULL,
PRIMARY KEY (`tbl_sample_id`, `tracked_id`)
)ENGINE = MyISAM
what are you referring about "design-error"? do you mean the design standard or the DBMS itself?
Thank you
|
|
|
|
|
Gilbert Consellado wrote: But why MySQL accept this query
..because it defines a single key, which happens to consist of two fields. It is still a single primary key.
Gilbert Consellado wrote: what are you referring about "design-error"?
I mean that there can only be one "primary".
Having multiple fields in a primary key is quite common. Yes, a larger key has a slight performance penalty; it has to check more data than in a single field, and any index containing the key will also grow.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
last question, which is better of the two?
#1
CREATE TABLE IF NOT EXISTS `tbl_sample`
(
`tbl_sample_id` INT UNSIGNED NOT NULL,
`tracked_id` INT UNSIGNED NOT NULL,
PRIMARY KEY (`tbl_sample_id`, `tracked_id`)
)ENGINE = MyISAM
#2
CREATE TABLE IF NOT EXISTS `tbl_sample`
(
`tbl_sample_id` INT UNSIGNED NOT NULL,
`tracked_id` INT UNSIGNED NOT NULL,
PRIMARY KEY (`tbl_sample_id`),
INDEX `track` (`track_id` ASC)
)ENGINE = MyISAM
|
|
|
|
|
The upper uses two fields to identify a single record. The lower one uses a single field; they would allow different things.
I'd recommend to use the smallest set of fields required. Imagine you are the end-user, how do you differentiate between each record? What is it that makes a sample unique?
If a sample can only be identified by giving both a tbl_sample_id and a track_id, they'd need both be part of the key. If either can be used, then the smallest would become the key, and the other a unique index (the upper in the first case, lower in the second case).
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
|
You're welcome 
|
|
|
|
|
Agree with Eddy... and, if you're of an age, think of "Highlander"... there can be only one.
|
|
|
|
|
ohh, but I don't know what Highlander is 
|
|
|
|
|
Highlander - a 1986 sci-fi movie. If you watch it, the comment will become clear.
|
|
|
|
|
Agree with Eddy Vluggen. To add a few things:
- Personally I would define any column combinations that are unique in real world as unique, natural keys, not as primary keys.
- Beyond that I would define a single columns surrogate key for all tables and always fetch/modify rows based on this surrogate key.
For more information, see Surrogate key[^]
|
|
|
|
|
Yup, and Edd save me on this.
But, I am not able or somehow use surrogate keys on this design.
|
|
|
|
|
Just to be clear, as far as I can see, you haven't talked about surrogate keys which is different from composite keys. The idea of surrogate is to have a key which has no relation to the real data, it only serves as an identifier, nothing more.
What comes to usability in your design, I've never seen a situation where surrogate wouldn't be usable, but I'm not familiar with your case.
|
|
|
|
|
I go slightly against the grain here, I use a LinkID field for many to many tables, what you are describing, setting that as the primary key. The 2 foriegn key fields can then have a unique constraint applied.
And yes this adds a performance cost, minor unless you are using serious volumes, but if there are tables referencing the link table you only need the linkid field rather than the 2 fields making up the composite key.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I'm not good at SQL indulgence.
Am not sure if it's a theme for database or C #.
I want to be able to distinguish when logging into my SQL database if the person has used the correct user name.
As usual connection to SQL Server looks like this in C #. Using SQL connection and not the Windows connection.
SqlConnection con = new SqlConnection ("connection string with the user name here");
con.Open ();
Question: How can I know if the connection with the user name for the SQL server has failed using C # code.
|
|
|
|
|
SqlConnection con = new SqlConnection("connection string with the user name here");
try
{
con.Open();
}
catch (SqlException ex)
{
}
modified 26-Aug-15 8:42am.
|
|
|
|
|
Jörgen Andersson wrote: catch (Exception ex)
You should only be catching exception types which you can handle. In this case, it should be sufficient to catch SqlException.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Yes, you're right, I've updated the answer.
|
|
|
|
|
|
Hello all,
I hope its the correct forum for my question..
I need some advices from your side for a new project.
In the past I wrote a mediaplayer in vb.net and its using a MS access database (.mdb) to store some data from the songs I scanned in my player. (for the metadata, duration, rating and so on)
Now I decided to rewrite my project in C# and I think for a new database solution, as well. My first idea was mySQL. (got no experience so far) but it read that its not a local database (only if you use the localhost as server, correct me if I'm wrong)
That means that my player isn't portable to other computers, right?
Another idea was XML.(also only low experience) Is it possible for me to work with SQL statements in it?
How fast is XML? (compared to mySQL and MS Access)
-----------------------
Here are some background information for you:
at the moment I work with one table which is in my database. I tested with around 90000 datarecords and for each record I use 7 columns. (path, metadata, duration etc)
to achieve a good speed during the program runs I read ALL datarecords in one array which I use in the prog instead of reading the database the whole time.
one function is a "live-search" which directly returns all matching records from the database depending on the pattern you enterd in a textbox.
---
okay, now I need only a tip which kind of LOCAL database I should use.
best regards
turion
modified 22-Aug-15 11:15am.
|
|
|
|
|
SQL Server has several editions. Most of them are used as centralized installations so that the data resides only on the database server and the client applications connect to the serve in order to fetch and manipulate the data. This way all of the clients share the same data.
However there is also a standalone edition, Compact edition, which is very lightweight and designed for single user scenarios. Based on the requirements you wrote, this sounds like potentially good choice for you. Have a look at:
SQL Server Compact...[^]
|
|
|
|
|
I'm with Mika on this, SQL Compact would probably be you best choice, there are many other and performance is not going to be an issue. There is probably more support and resources available for MS products than any other.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
turion87 wrote: Another idea was XML.(also only low experience) Is it possible for me to work with SQL statements in it?
It's possible, but it would require third party libraries AFAIK.
turion87 wrote: How fast is XML? (compared to mySQL and MS Access)
Not fast at all, we're talking unindexed text parsing here.
turion87 wrote: okay, now I need only a tip which kind of LOCAL database I should use.
For the best support you should probably go for SQLServer as the other have suggested.
For a small footprint, easy distribution and portability, I would have a look at SQLite, just add one library and one datafile.
|
|
|
|
|
I would go for something like SQLite[^].
The beauty of SQLite it that your database and the sql engine are all contained within one file.
What I would also do is archive the data to xml as a backup for the sake of your sanity and hard work of inputting the data - an archive on each closing of the application, to a file with a datestamp within its name, is the way I tend to handle things with a deletion of old xml files every 30 days.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
