1
vote
1answer
76 views

What's better here - a single column or multicolumn primary key?

Let's assume you have a table groups and a table item. Each item belongs to exactly one group. It is an inherent part of that group. An item cannot exist outside of a group and it cannot be moved to ...
2
votes
2answers
98 views

What can I do to make mysql use the expected indices?

What can I do to make mysql use the expected indices? I've got 4 tables, two containing resources, and the others containing historical changes. One pair uses indexes correctly, the other doesn't, ...
6
votes
3answers
179 views

Primary Key efficiency

If I need a table to hold Point of Sales transactions, and am told I need to store: Country ID Store Number POS Terminal Number Transaction Date Item Code Teller ID Another Field More Fields Now, ...
1
vote
1answer
134 views

Multiple primary keys

I am new to database design and have been looking at some example data models here. One thing I don't understand is if you have a table like client_addresses from the link above: ...
3
votes
1answer
342 views

Index Key Column Order

I have created a joining table for many-to-many relationship. The table only has 2 cols in it, ticketid and groupid typical data would be groupid ticketid 20 56 20 87 20 ...
12
votes
5answers
1k views

Sequential GUID or bigint for 'huge' database table PK

I know this type of question comes up a lot, but I've yet to read any compelling arguments to help me make this decision. Please bear with me! I have a huge database - it grows by about 10,000,000 ...
1
vote
1answer
396 views

How to alter primary key constraint for 2 columns in same table in SQL Server?

In SQL Server, I already created a table called tblDatabase with some columns. I want to add a primary key on the two columns DatabaseName and Servername. I tried to add: ALTER TABLE tblDatabase ...
1
vote
1answer
453 views

SQL - double column used from primary key with autoincrement

I am user the following as primary key in the SqlDB: I am doing the Index as Autoincrement, but it is autoincremented in general not per user. Example userid=1,index=1, then insert another for ...
2
votes
3answers
696 views

performance of multi field primary key or 'contrived' 'semi artificial' key

This is not a question regarding the benefits or otherwise of using an artificial auto increment key in any given table against using a multi-field 'primary key'. That discussion (or argument) can ...
2
votes
1answer
111 views

Can I use a Clob as a table key (from Java)?

Following a suggestion made to another question to store large amounts of text: can one use a CLOB as primary key field (i.e., from Java's DataNucleus implementation of JDO)? I know it is probably a ...
1
vote
1answer
2k views

How is INDEX on Composite Primary Key in mysql?

When making a composite primary key for two or more columns, e.g. PRIMARY KEY(col1, col2, col3); will the system INDEX each column individually? The reason that I am asking this question is that when ...
2
votes
4answers
1k views

Two-sided UNIQUE INDEX for two columns

Creating a table with composite PRIMARY KEY or using UNIQUE INDEX for two columns guarantee uniqueness of col1, col2. Is there a tricky approach to make the reverse order of two columns UNIQUE too ...
2
votes
2answers
2k views

“on duplicate key update” all the non-key fields mentioned in the insert

How can I update the field values mentioned in the insert portion without listing them again in the on duplicate key update portion? insert into tablename set keyname = 'keyvalue', fieldname = ...
3
votes
2answers
219 views

Is index_type ignored on MySQL primary keys in create table?

I have seen a create table whose primary key had an identifier where it should have an index_type, something like that: create table a (foo INTEGER, PRIMARY KEY foo_id (foo)); The thing is that ...
1
vote
2answers
384 views

serial field issue

Q: I use a serial as a datatype for my primary keys in my tables. I insert a lot of data then truncate these data one after one many times to test the data entry. My question is: Is there any ...
1
vote
1answer
127 views

Is it require to fill all tables inside of the database?

Now,I created upper that table and in order to test I will give 3 textboxes values as below. SqlCommand cmd = new SqlCommand ("Insert Into dbo.PrnInf(PrnName, PrnSurName, PrnEgn ) Values ...
13
votes
5answers
751 views

Why do primary keys have names of their own?

From a mathematical view, granted that a table has at most one primary key, it seems to be a shortsighted design decision to refer to primary keys by some arbitrary name instead of a simple table ...