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
55 views

SQL Server Management Studio: The columns in table do not match an existing primary key or unique constraint

I have two tables: Students and Courses. I want to set a foreign key in the Students table from the couseId column to the courseId column in the Courses table. I have set primary keys to both ...
1
vote
1answer
32 views

Find insert/update/delete queries that reference a partitioned view

I have just given a SQL Server with performance problems. The database is very large and use distributed views with a very large primary clustered key. (24 bytes). I would like to change this primary ...
1
vote
1answer
33 views

Trying to understand how an auto increment primary key is better than no primary key and some other primary key questions

I am trying to understand primary keys better, how to use them effectively in table design and in queries. First, are the primary keys themselves used in WHERE clauses? For example, if I have a table ...
1
vote
1answer
36 views

What is the best approach to find all fields that has (Foreign Key and Primary Key) constraints in a Mysql database?

I have a mysql database that has 250 tables. I would like to run a query to retrieve: tableName ; FieldName ; ConstaintName (Primary or foreign key). I could use ...
1
vote
3answers
142 views

Best practice - Update Primary Keys

What's you opinion about Update a primary Key in SQL? I have a "link" table with 3 key from 3 other tables. Should the administration let people to edit one of the keys? I usely said no, dont change ...
1
vote
1answer
608 views

Non-sequential Primary Key performance in PostgreSQL 9.3

Due to requirement to support distributed environment, each of my clients has been assigned unique node id, it appends the node id with running number as primary key and stores at local database. E.g. ...
1
vote
1answer
322 views

How to insert values into a junction table?

I add values into my two tables using the code below, but I also have a junction table called book_main_cover and I want to add data into this table to make a connection between book_id and ...
1
vote
1answer
913 views

How can one drop all default values and allow NULL for all columns of a table?

I'm working on a PHP script to modify the primary key (PK) of a table. My current code is: $q = "SHOW KEYS FROM $db.$table WHERE Key_name='PRIMARY';"; $keys = Query ( $q); if ( $keys) { $q = ...
1
vote
2answers
87 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
53 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
178 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
279 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
616 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
156 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
142 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
141 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
784 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
1answer
192 views

Impact of miss-ordered fields in a composite, clustered primary key?

I have tables where the primary key is something like this: -StoreID -BusinessDate Typically, there is one row for every store (~100) for every single day. One row for each store is inserted on a ...
1
vote
2answers
236 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
31 views

Converting a composite primary key to composite unique, and adding an auto-incremented PK column

Background I have taken over responsibility for a table that, in the long run, will grow monolithic. The table is a very simple link table with 2 columns, call them col1 and col2, both being foreign ...
1
vote
0answers
21 views

Varchar PrimaryKey. Clustered or nonclustered? [duplicate]

I am working on a legacy database. The primary key is clustered varchar(128) and the table contains a high volume of data. Does it necessarily help performance if I change the clustered PK to ...
1
vote
1answer
72 views

How can I randomize primary key values?

I need to avoid having my primary keys sequential. Basically looking for this in MySQL: https://wiki.postgresql.org/wiki/Pseudo_encrypt
1
vote
1answer
35 views

Cities and States (a few data): single table or splitted?

I am designing a DB schema and I need to store cities and states. I expect only a few rows, let's say, 20 cities. Example: id | city | state 1 | Rio de Janeiro | RJ 2 | Niteroi ...
1
vote
0answers
37 views

Many-to-many relationship, what's the best choice for primary key? [duplicate]

In advance, I'd like to apologize if it's a duplicate question... I can't find a direct answer, probably because I'm not a native english speaker. Straight to the subject: I have 2 tables: company, ...
1
vote
0answers
21 views

General Definition of/for Primary Key for Table with Repeated Entries (i.e., not in 1NF)

my first post, my apologies if this does not belong here, please refer me to the/a right site. So I am looking for a general form for a primary key given a table containing non-atomic repeated ...
1
vote
0answers
295 views

Sudden PRIMARY KEY violation on IDENTITY column

I have a log table with an ID identity column. Everything works fine for years; then, yesterday, I see the following error in the logs: The statement has been terminated. Violation of PRIMARY ...
1
vote
0answers
29 views

Can I use a Composite Key as criteria for selection in a query?

I have created a meeting minutes table and would like to use two fields as a unique ID for each meeting (field one is a meeting code (lookup from another table) for each type of meeting and field two ...
1
vote
0answers
45 views

Is there a way of implementing a composite primary key with varying fields in MongoDB?

Here is the information about my development environment: -Mongodb 3.0.0 -Microsoft Visual Studio Professional 2013 -.NET Framework 4.5 We are trying to design some MongoDB collections at work ...
1
vote
0answers
132 views

How to use an RSA public key as the primary key to a table in postgres

I have a data set that needs to be looked up by RSA public keys. These keys are 392 text characters long. What is the best way to make that a primary and/or unique key? Should it be a BYTEA ...
1
vote
0answers
73 views

How to pass the value of PK to FK

Ok, so here it goes. I have a table called employee. then I have another table called languages. I need to copy the value of PK of employee table to foreign key in Languages table, rather than ...
1
vote
0answers
46 views

Speeding Up Query - Hangs at Sending Data - Indexed Correctly

I am looking for ways to speed up the below query. I currently have indices on FIRST, LAST, UNIT_PARENT, HomeAddressLine1, HomeAddressLine2, HomeCityNAme, HomeStateAbbreviation, HomeZipCode, HomeZip4, ...
1
vote
1answer
187 views

Optimizing Queries HAVING COUNT and INSERT…ON DUPLICATE KEY UPDATE

There are two main queries I'm tying to improve, one question is much more conceptual and learning about databases and another is much more straightforward. Both cases deal with the same table It has ...
1
vote
0answers
76 views

Table with both primary key and unique key

My system requirement defines a unique business/domain/natural key (i.e. <yyyymmdd><branch_id><running_number>, in bigint). Should I use this unique key as primary key or I should ...
1
vote
1answer
478 views

choose Username or ID as primary key? [duplicate]

my sites dont need use ID at all, then I am trying to manage user by username. Assume my sites is abc.com, then access to user profile by URL: abc.com/user1 So if you want to view all info of that ...
1
vote
0answers
107 views

How to determine next value to use in composite key in SQL Server? [closed]

I am trying to figure out how to determine the next value to use when adding a new record to a table that uses a composite/compound primary key in a system with concurrent users. Suppose I have an ...
1
vote
0answers
2k 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
234 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
1k 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
8k 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
36 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
197 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
3k 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
3answers
50 views

Reset Key Names to Defaults?

I'm working with some tables/columns that were renamed at some point, but the names of the primary/foreign keys are still based on the previous names. In SSMS is there any (preferably automatic) way ...
0
votes
2answers
4k 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
137 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
310 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
1answer
36 views

Using default values in primary key

Good day. I have a table like this: CREATE TABLE mytable ( myname varchar(30), mydate date DEFAULT CURRENT_DATE, PRIMARY KEY(myname, mydate) ); EDIT: Forgot to mention I am using a ...
0
votes
1answer
2k views

Create relationship between two tables with composite primary key

I've created a table [t1] with two fields and a composite primary key, show in below: create table t1( id int, name nchar(10), primary key(id,name) ); I want to create a second table ...
0
votes
4answers
124 views

SqlServer: can one table's PK be the FK of other two table's? [duplicate]

I have three tables project, users and proj_user. The PK of proj_user table is the FK of project and users table's pk e.g.(project_id,user_id) My question is, is it possible for the project_id and ...