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

0
votes
1answer
26 views

How should I handle unknown primary keys? [on hold]

I'm working as a database-administrator at a school. Recently the school upgraded its pupil administrative software into a web-based solution. This works great, but this web-based solution is missing ...
7
votes
3answers
169 views

Is it reasonable to mark all columns but one as primary key?

I have a table representing movies. The fields are: id (PK), title, genre, runtime, released_in, tags, origin, downloads. My database cannot be polluted by duplicated rows, so I want to enforce ...
1
vote
0answers
52 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 ...
0
votes
2answers
28 views

Basic SQL question about setting constraints

In code such as the following, what is the need to write constraint pk_dept primary key (deptno) if just simply primary key (deptno) works? Is pk_dept something the database uses internally, since it ...
0
votes
0answers
24 views

SQLite table design for PK and/or UNIQUE

I'm trying to design a table in SQLite3, where I can perform these 2 actions. INSERT new row where a unique primary key is created. And INSERT new row with an given primary key Think of a ...
1
vote
0answers
25 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, ...
4
votes
3answers
371 views

How can I convert a key in a SQL Server deadlock report to the value?

I have a deadlock report that tells me that there was a conflict involving waitresource="KEY: 9:72057632651542528 (543066506c7c)" and I can see this: <keylock hobtid="72057632651542528" dbid="9" ...
1
vote
1answer
71 views

Is it ok to create additional column for helping other column to be a PK?

Let's say a Person can have more than one nickname. He(she) must enter his(her) nicknames alongside with other details: Here is the table columns: User_Id, Nickame, Age Of course, for now there ...
2
votes
1answer
30 views

Should I create many ID for a same Person/entity in my DB?

In my model, I must store the name of the teacher of some kids and the classroom in which the teachers work, let’s say. All of the hundreds of teachers in the schools have 1 and only 1 classroom, ...
7
votes
3answers
913 views

Why should a key be made explicit?

I am very new to the subject of databases so this may sound ignorant, but I am curious why a key should be made explicit within a table. Is this primarily to tell the user that the given column value ...
0
votes
1answer
34 views

Problems in Choosing Foreign Keys :S

I am designing my very first relational database. I am now in the conceptual design, building tables and views, selecting the primary and foreign keys and normalizing everything. But now, I am stock ...
1
vote
1answer
83 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 ...
4
votes
1answer
63 views

MySQL 1175 Error While Using Primary Keys

What would cause the error 1175 that starts out with: You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.... The problem I have is that I did include ...
0
votes
2answers
63 views

Is it redundant to set your primary key as a unique key?

I'm putting tables together in MySQL workbench and I'm currently ticking my primary keys to also be uniquely indexed. Is this a redundant configuration?
0
votes
0answers
17 views

Part of composite foreign keys as primary key

I have a table A with a,b,c as primary key. Table B reference has a,b,c as foreign keys and it wants to set its own column d and foreign key a as primary key, Is it legal? Or does it have to set the ...
1
vote
2answers
33 views

DB2 10.5 - When inserting, the database is trying to reuse index keys on GENERATE ALWAYS

I am trying to insert a row into a database table which has a GENERATED ALWAYS primary key column. The table has over 1000 rows, and the primary keys start at 1. When I try to do an insert, the error ...
3
votes
1answer
70 views

Drop primary key without dropping an index

Is it possible to drop primary key constraint on a table without dropping the index? I have a situation when I want to remove the PRIMARY KEY to get rid of the NOT NULL constraints and set it to ...
3
votes
3answers
141 views

Is Primary Key Non Clustered recommended?

I am designing a database table. Columns are RouteId ,Origin , Destination. RouteId is a string, it is always unique but length will be more than 60 characters. Since RouteId is unique, I am ...
0
votes
0answers
26 views

Filesort when using view

I am sorry for asking, but I can't figure what i am doing wrong and filesort appears in my explain. The id column that I use for sorting in the following examples is a primary key... If I create ...
3
votes
0answers
27 views

Superkeys of this relation

I am trying to find the superkeys of this relation, but I am having troubles finding out how many superkeys there are and exactly what they are. I figured out that the candidate keys were ...
0
votes
0answers
46 views

For Postgres, how would I define a custom function to generate primary keys?

In postgres, primary keys are typically generated using a SEQUENCE. I am hoping to generate a unique random 12 character string for use as a primary key. How would I do this in Postgres?
1
vote
1answer
33 views

Need advice on Primary and Clustering Keys of a table

I need advise on a table which is part of a development project I'm involved. A brief summary on the use of the table: Data from multiple databases will be consolidated in this table for the purpose ...
1
vote
1answer
92 views

Shorthand for on primary key?

Is there a shorthand for merging rows based on the primary key? For example: MERGE INTO [MyDatabase].[MySchema].[MyTable] AS target USING [MyLinkedServer].[MyDatabase].[MySchema].[MyTable] AS source ...
0
votes
1answer
27 views

Returning auto-generated primary key after SQL insertion

I am trying to submit data to a database and then have it return the value of the user_id (primary key) created by the insertion, but I can't get it to return the value. I use the same query in ...
10
votes
3answers
1k views

Is a 5+ column primary key bad for large (100 million+) table?

I was reading about some real life DB issues, and one project had a 100 million row plus table that had 5 columns as its primary. I'm thinking this is bad, but can anyone tell me exactly why? EDIT: ...
0
votes
0answers
26 views

Creating mysql config setting per database

I am trying to setup MySQL so one database auto increments by 2. I am using the auto_increment_increment variable in my.cnf configuration file, but this is being applied to every database. I would ...
0
votes
0answers
48 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 ...
0
votes
0answers
55 views

Converting Entity Relation(ER) Model to Relational Schema?

I have the following ER Model I am trying to convert this to a relational database schema but I am not sure about several things: PORT_VISIT and PORT are weak entities. How do I figure on which ...
-1
votes
2answers
84 views

Question about choosing primary key

My scenario is that I have a super type named PERSON and two sub types EMPLOYEE and CUSTOMER. I use the Primary key from PERSON, PersonID, in both EMPLOYEE and CUSTOMER as foreign keys. However, I ...
2
votes
1answer
112 views

How to efficiently create a new PRIMARY KEY CLUSTERED definition online

Given a table with a PRIMARY KEY definition (acting as the clustered index), I want to efficiently change the definition of the PRIMARY KEY (or at a minimum, just the clustered index) online. I would ...
0
votes
2answers
25 views

Can we determine FDs if we are given some tuples?

I am taking a databases course and i'm learning about functional dependencies. I have searched around and did not find any information on this. My questions is if we are given a table with some ...
1
vote
1answer
154 views

Rename the constraint of a possibly referenced primary key in PostgreSQL

Is it safe to use this command just like that, even though the table might be referenced from other tables as foreign key? ALTER INDEX foo_pkey RENAME TO pk__foo__id; I have just tried it and it ...
2
votes
2answers
375 views

Primary key auto increment best practices

Apologies if something similar has already been asked, I wasn't sure of the best term to search for to get what I was after, so please let me know if there is already a bunch of other related ...
1
vote
3answers
198 views

Copy table with separate sequences for serial columns

While creating a column management tool I came across the need to quickly replicate tables in PostgreSQL so I would not test new tools with non-test tables. To effectively test the new column tool ...
1
vote
4answers
1k views

NVARCHAR column as PRIMARY KEY or as UNIQUE column

I'm developing a SQL Server 2012 database and I have a doubt about nvarchar columns as primary keys. I have this table: CREATE TABLE [dbo].[CODES] ( [ID_CODE] [bigint] IDENTITY(1,1) NOT NULL, ...
1
vote
3answers
75 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 ...
0
votes
0answers
22 views

Medical History - Is this DB properly design?

Could you please tell me if this DB is properly design? Are relationships type correct? Is there anything I should delete or add? Thanks you all very much!
0
votes
0answers
61 views

How can I make a foreign key from a fragment of the primary key?

I have this table: CREATE TABLE Phones ( Brand VARCHAR(20), Model VARCHAR(20), PRIMARY KEY (Brand, Model) ); A mobile phone can't be uniquely identified just by its model (for example: HTC (One) or ...
1
vote
1answer
62 views

PostgreSQL Primary key disappears from test table

I have a somewhat convoluted scenario where a test table I had created with a primary key won't return the primary key. pgAdmin III reports there are no constraints. I have the entire PostgreSQL query ...
1
vote
1answer
297 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
451 views

PostgreSQL SELECT primary key as “serial” or “bigserial”

I've meshed together a way to determine what the data_type is as in the data_type you use in the syntax when creating a new table based off of the PostgreSQL wiki page. If there is something wrong ...
2
votes
1answer
90 views

If A and B are candidate keys, then AB is candidate key or not ?

If R{abc} is a relation, f{ a->b b->a a->c b->c }, then a and b are candidate keys. What about ab ? Is it a candidate key or is it a super key or both ?
0
votes
0answers
46 views

redshift performance effect if use 32 character long unique string as Primary key instead of auto incremented int

In my analytics application I am creating 32 char long unique string for each hit as user_session. In current implementation, I am storing user_session in tbl_sesssion in session column and I am ...
3
votes
3answers
294 views

Clustered index performance consideration in MySQL & PostgreSQL

In MySQL/InnoDB, the clustered index is synonymous with the primary key, so picking up a poor primary key affect your db performance, i.e. use of UUID as PK is a performance killer for database write. ...
1
vote
1answer
3k views

Linking Fields From 2 Different Tables (Microsoft Access)

I am trying to link 2 fields from 2 different tables on Access but I will give an example to try and make the issue clearer. I have 1 table called "Employee Details" which contains the field ...
7
votes
1answer
178 views

unable to insert row at id, but row does not exist

Here is the weird problem i am facing. I am trying to enter data using following query insert into product_product (id, product_tmpl_id, make_equip, model_equip, name_template, serial_num_equip, ...
0
votes
0answers
185 views

PostgreSQL replace primary key and converting non-serial to serial type

I'm basically building my own version of phpPgAdmin (if I get good enough to make it good enough I'll post it on a repository hosting service) and currently I'm revising my primary key tool to replace ...
8
votes
3answers
1k views

Email address unique or primary key?

I am a newbie in databases. I read around and found out that it's probably not a great idea to use email address as primary key because string comparisons are slower which effects performance in ...
2
votes
0answers
77 views

Storing URLs in a database

I am thinking about storing URL's in my database, but I am not sure whether the way I use is an efficient way to do things or not. Table name : url url_id (Primary Key & Auto Increment) url_value ...
1
vote
1answer
148 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 ...