In relational database design, a primary key can uniquely identify each row in a table. A primary key comprises a single column or a set of columns.

learn more… | top users | synonyms

1
vote
1answer
137 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 ...
1
vote
1answer
3k 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 ...
1
vote
1answer
118 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 ...
1
vote
2answers
1k views

What could cause an auto increment primary key to skip numbers?

I have a table created with the following T-SQL: CREATE TABLE [dbo].[TableName]( [id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, ...
1
vote
1answer
76 views

When do you add your foreign keys?

I'm scripting out a fairly simple database so I can simply hand off a script to someone else for an application to use. I'm curious about where best practice/the community stand on this: When should ...
1
vote
2answers
694 views

How are auto_increment keys handled in INSERT (SELECT * FROM…)

I have table1 and table2 in MySQL. Both have a primary auto_increment key id. If the table schemas match and I do INSERT INTO table1 (SELECT * FROM table2) what happens with regards to the new rows ...
1
vote
1answer
142 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: ...
1
vote
2answers
465 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
2answers
58 views

Relationship with a table without unique/primary key

I have a database with 3 tables. Table Work, with a field named workName. Table SubWorks, which have a field subWorkName and work with a relationship with Work.workName. And table Activity with ...
1
vote
1answer
242 views

Multiple primary keys in PostgreSQL

I have the following table: CREATE TABLE word( word CHARACTER VARYING NOT NULL, id BIGINT NOT NULL, repeat INTEGER NOT NULL ); ALTER TABLE public.word OWNER TO postgres; ALTER TABLE ONLY word ADD ...
1
vote
1answer
111 views

How do I validate all my constraints in postgresql?

It seems like I have been bitten by the first bug described in the release notes of postgresql 9.3.4: http://www.postgresql.org/docs/devel/static/release-9-3-4.html I now have e.g. duplicate primary ...
1
vote
2answers
128 views

Column suitable for natural key?

I have read many articles now about natural vs surrogate primary keys, and came to the conclusion there is no single best practice. I have a table that will have around 2000 definite unique values, ...
1
vote
1answer
124 views

MySQL, temporary on disk table on primary key retrieval?

MySQL 5.5.24, Windows 7 64-bit. I've InnoDB table with primary key defined on three columns: CREATE TABLE `common_descriptions` ( `object_id` mediumint(8) unsigned NOT NULL DEFAULT '0', ...
1
vote
1answer
837 views

Auto Increment Primary ID inserting random values

Today I was asked to look into this problem and I have never seen anything like this before. A table in our MySQL database, with a primary IDs column, which is set to autoincrement int, had some weird ...
1
vote
1answer
149 views

MySQL looking up more rows than needed (indexing issue)

In our MySQL 5.5 database, we have following InnoDB table with 30M+ rows: +----------------+-------------+-----------------+--------+ | participant_id | question_id | given_answer_id | status | ...
1
vote
2answers
108 views

Relations, dependencies and normal form

I have some issues in understanding about relations and dependencies. I will show two examples and what I want to know: Assume the relation GTX {G,T,X} with the following dependencies: G,X -> T ...
1
vote
1answer
143 views

Is there a way to extract a report of or script all of the primary keys in a database?

I have just discovered that deployed copies of one of our databases have no primary keys. This is probably the fault of the SSMS Import/Export wizard, which has done the same to me. Our master copy of ...
1
vote
1answer
400 views

Missing PK/FK after database migration

We recently had a server migration. A new server is now the PROD server, and what used to be the PROD server is now the staging environment. Apparently, the data was transferred correctly, but the ...
1
vote
1answer
536 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
731 views

What should one do if the auto increment primary key exceeds the maximum?

I have a primary key for a table with the following structure, **Field** **Type** nid int(10)unsigned I know the maximum limit of int(10) is 4294967295. But What ...
1
vote
1answer
2k views

Does the order of keys in compound Primary key make any difference?

Package Package ------- PackageID PackageName CityID PRIMARY KEY (PackageID) UNIQUE KEY (PackageName) FOREIGN KEY (CityID) REFERENCES City (CityID) Package versions: PackageVersion ...
1
vote
1answer
1k views

updating mysql primary keys

I need to be able to swap values that are used in a primary key. Meaning, in one update statement, I need to change id 1 to id 2, and id 2 to id 1. In other relational databases, (Sql Server and ...
1
vote
1answer
50 views

Almost sequential GUIDs vs random GUIDs?

I'm looking at using GUIDs (UUID) as primary key with clustered index in SQL Server. In my webb-app I have many clients who will create items client side in javascript and save them only now and ...
1
vote
2answers
57 views

compound primary key with separate id field for reference

I am trying to build locations into a database like so: Country -> Region -> Town Countries will be populated with a list of pre-defined worldwide countries, regions & towns are input by users. ...
1
vote
1answer
47 views

one to one relationship

This is my database structure: Users {user_id, username} Profiles {profile_id, user_id, user_views} My question is: If I do it the way mentioned above, should I to set user_id as primary key or ...
1
vote
1answer
167 views

MySQL auto_increment delivers different primary key in MyISAM and InnoDB

I have a table with auto incremental primary key. If I want to change a row and add new rows I got different primary keys depending on engine MyISAM and InnoDB. The original table is: pk.. ...
1
vote
1answer
274 views

Would Mongo's ObjectID work well in InnoDB's clustered index?

Mongo's ObjectID is defined like this: ObjectId is a 12-byte BSON type, constructed using: a 4-byte value representing the seconds since the Unix epoch, a 3-byte machine identifier, ...
1
vote
1answer
98 views

Key lookup partition pruning

I have a query inner joining multiple tables all of which are partitioned and have 10 partitions each: SELECT A.COL1, B.COL2, C.COL3 FROM A INNER JOIN B ON A.ID = B.ID INNER JOIN C ON A.ID = C.ID ...
1
vote
1answer
135 views

Is there a tuning parameter for MySQL that allows you to set an on-disk gap between non-sequential primary keys?

I understand that one of the primary issues with using a non-sequential primary key is that every disk/memory write that does not come after the last known key causes a re-write of everything from the ...
1
vote
1answer
103 views

Can I place the attribute in the Entity, when the foreign key is also already present?

I am using DBdesigner Fork for generating ER diagram. I have the 'Company' as an entity for which can I place the attribute of 'idShares', despite of having the foreign key of Shares Entity in the ...
1
vote
1answer
506 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 ...
1
vote
2answers
172 views

Update primary key

I want to update a primary key column, but the change should cascade to all other child tables where this primary key is used as foreign key. Example: table1 has column "slug" as primary key. One row ...
1
vote
0answers
145 views

“Temporary file write failure” when trying to change primary key in MySQL

In MySQL, When I trying to change primary key by this query: ALTER TABLE `resources` DROP PRIMARY KEY, ADD PRIMARY KEY (`role_id`, `category_id`, `lang`, `model`, `rule`); I execute this query ...
1
vote
1answer
36 views

Sybase ASA: Saving and dropping all foreign keys and most Primary Keys for (Bulk) Insert

Background Information: I am trying to migrate from an ASA 12 Database, to a ASA 12 Database by using a Remote Server. I have to migrate a lot of Rows (not all), spread over a lot of tables (not ...
1
vote
0answers
139 views

Crow Feet notation and identifying -vs- non-identifying relationships

Is it possible to distinguish between identifying and non-identifying relationships using the Crow Feet notation? Case in point: In table Book, part of the book's Primary Key is a FK to the Author ...
1
vote
1answer
1k views

MySQL Subquery returns more than one row

I have a table namely tbl_plays. It has the following columns and data id(PK)| gameid | drawid | etc ---------------------------------------------- 1 | 1509 | 73 | ...
1
vote
0answers
30 views

Keys of a relation

I got relation: R = { (ABCDEF) , (A B F -> C, B C -> F, F -> A, F -> B, D E -> E, E -> D)} keys of this relation are: {BCE}, {EF} But i dont get it... why? Why not only {EF}.. it got less ...
1
vote
1answer
96 views

Can one part of a multi-column primary key be used across tables?

I want to use SQLite as file format for my app, a reporting program. Below is an idea of the kind of schema I'll use (just the first few tables). There will be one report per file and all tables are ...
1
vote
1answer
86 views

Proper implementation of child (derived) table PK/FK during single and multiple inheritance hard coded generalization physical data model

Below is an image of hard coded generalization UML logical model of entities. I'd like to implement these classes as tables in a physical data model. Two derived classes/entities (Class2 and ...
1
vote
1answer
149 views

Are these custom module Drupal tables lacking foreign keys? mysqldump with table structures without data is included

I am troubleshooting a some tables in MySQL used by a custom Drupal module. Documentation for these tables is poor so I used mysqldump to get the structures in the Drupal database without the data. I ...
1
vote
0answers
80 views

Is it possible to change the data-type of a primary key column in SQL Azure? [duplicate]

Possible Duplicate: How can I alter an existing Primary Key on SQL Azure? OK, so say I have a table: 'Contact' and a primary key: 'ContactID'. The primary key is an INT but I want to ...
1
vote
5answers
2k views

How should I save apache logs into a mysql table?

This is a php script to view apache logs. I want to save apache logs into a mysql database. Then add some rules for tagging urls using mysql REGEXP search, like: SET tag='some tag' WHERE url REGEXP ...
0
votes
2answers
3k views

MySQL - Autoincrement does not increment sequentally if last row was deleted

I have a table which contains an auto incremented primary key id. If I delete the last row (highest id, for exampe id = 6) and insert a new row, the new id starts at 7. Which paramater I have to ...
0
votes
1answer
112 views

Use surrogate or natural PK if a row is uniquely identified by two or more columns?

I am having a hard time choosing between natural and surrogate PK for my database table. The database is for a ranking system for an MOBA game. Here is more or less what I would do if I were to go ...
0
votes
1answer
220 views

Maintain table with too many insert

I have to create a table. Its size is going to increase very fast. Is there going to be any issue if many insert queries are fired at the same instant as mentioned here - User autoincrement ID for ...
0
votes
2answers
104 views

What is the advantage of using numeric primary keys in a relational database?

Many tables in a relational database system (in my admittedly limited experience) have this sort of structure (Oracle syntax as that's what I'm used to): create table widgets ( widget_id number ...
0
votes
2answers
591 views

Can a surrogate key and a primary key be in the same table?

For example we have: Surrogate key: ABC123 Primary key: 1 Name: James Is this legit in a Data Warehouse table?
0
votes
1answer
848 views

The four properties of a primary key

In our dbms class, these were the four properties of a primary key discussed for Oracle. Unique Not NULL Fully functional dependency Indexed I understand all the properties except the 3rd one ...
0
votes
1answer
332 views

How SQLite index ID (or line number)

SQLite is based on a flatfile, and store virtual tables in a flatfile (I think based on offset, correct me if I'm wrong). But how it index the Primary Key (ID), which I think is identical to line ...
0
votes
2answers
105 views

Primary key for link/junction table with time-dependent data

I want to link two tables in such a way that for any given time period the relationship is one-to-one, but over time the relationship can change. As an minimal example, suppose I want to list the ...