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
0answers
10 views

MariaDB / mYSQL Order BY PrimaryKey DESC vs DESC set by Default

What do you guys think about: After each query, I put "Order BY PrimaryKey DESC" VS I set "PrimaryKey Sort by DESC Default" and each query I don't need to put Order By there. Which one will be ...
1
vote
1answer
59 views

SQL Server query problem when selecting data from child table based on column in parent table

I am using MS SQL Server 2008 using Management Studio. I am running a query(below) to extract dates and value based on the 'ticker' column from the parent table, the attribute column (child) , and ...
1
vote
4answers
90 views

How can I allow part of a key to be null and enforce uniqueness of records with nulls?

I have an Access table called Engagement with four fields: Emp_id, Year, Week, Act_id It records when an employee is/was engaged in an activity. Each field is Number and each makes up part of the ...
0
votes
3answers
68 views

Clustered Index and PK - Two different Objects?

I have a table which has a Clustered PK as follows: CREATE TABLE [dbo].[PanelCcmRawData] ADD CONSTRAINT [PK_CcmRawData] PRIMARY KEY CLUSTERED ( [CcmRawDataId] ASC, [LineageId] ASC ) I ...
16
votes
8answers
3k views

Why would a primary key value change?

I have been researching the concept of ROWGUID's recently and came across this question. This answer gave insight, but has led me down a different rabbit hole with the mention of changing the primary ...
1
vote
1answer
246 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 all)....
1
vote
1answer
62 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 ...
7
votes
3answers
312 views

Should I add an auto-increment / IDENTITY field to a cross-reference table just for PK purposes?

I'm adding the following cross-reference table to my SQL Server-hosted DB: company_id bigint not null (FK) org_path nvarchar (2048) not null The company_id field refers to the id field in another ...
2
votes
1answer
11k 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 '...
0
votes
0answers
45 views

MySQL - Database Design : Encrypting user id and creating a mask for further internal references

For a food order delivery system that I am working on, I want to make sure my user's private information is well protected; from system admins too. So I thought of coming up with this concept of ...
-1
votes
0answers
12 views

Why only one candidate can be the primary key? [duplicate]

I'm a rookie to DBMS. I have read that only one candidate key can be the primary key. Why not more than one ? Please explain with an example.
7
votes
1answer
20k views

How to set up multiple fields as primary key in MySQL?

I have a table with fields EmployeeID blahblah blahblah2 ..... RecordMonth RecodrdYear so each employee should only have a matching entry for a month, year, Emp#. How do I set up a table. ...
0
votes
1answer
45 views

Foreign key as part of composite primary key or not?

I'm designing a relational database. This is what I'm trying to design: I will have production orders and products. A production order will have one product, but a product could be on zero or more ...
14
votes
4answers
2k 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
0answers
25 views

mysql duplicate key error during restoration through mysqldump data

I have a mysql database which is having one table for geo location and saves latitude and longitude values of users. The fields are as followed CREATE TABLE `USER_LOCATION` ( `LATITUDE` float NOT ...
4
votes
1answer
31 views

Oracle SQL 12c How to correctly use compound foreign keys in compound primary keys?

DISCLAIMER: I am a student. I do not work for an airline. I do not pretend to be a professional programmer. Greetings, I want understand what I need to correct in order for these tables to work ...
2
votes
1answer
57 views

Two tables with the same primary keys

Can two tables have the same primary keys? For instance:
7
votes
2answers
4k views

Why does Postgres generate an already used PK value?

I'm using Django, and every once in a while I get this error: IntegrityError: duplicate key value violates unique constraint "myapp_mymodel_pkey" DETAIL: Key (id)=(1) already exists. My ...
0
votes
1answer
40 views

Decomposition In Boyce-Codd Normal Form may lost relations?

Let be the following Realtion R={UtilisateurID, Nom, Prenom, AdresseEmail, Login, Passwd, ServeurMail} with the functional dependencies: F = { UtilisateurID → Nom, Prenom UtilisateurID, ...
1
vote
1answer
48 views

InnoDB: secondary key that's 'parallel' to primary--do joins wherein the resulting primary keys are contiguous perform better?

Say I have a table ( person_id INT AUTO_INCREMENT, birthday_timestamp BIGINT, country, state, ..., PRIMARY KEY (person_id), KEY (birthday_timestamp) ) where people are added the instant they'...
1
vote
1answer
67 views

Automatic index creation for primary vs. foreign keys in Postgresql

For PostgreSQL 9.4 (http://www.postgresql.org/docs/9.4/static/ddl-constraints.html) it is stated that A foreign key must reference columns that either are a primary key or form a unique ...
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 ...
3
votes
3answers
113 views

What does having the primary key as the last column in a composite secondary index in an InnoDB table do?

Say I have a 1-to-N relationship (person_id, pet_id). I have a table where pet_id is the primary key. I understand that an InnoDB secondary index is essentially a B-tree where the values are the ...
0
votes
1answer
26 views

Difference between surrogate key(s) and composite key(s)?

I'm a beginner with databases and have been perplexed by the various definitions to distinguish these two. Various google searches have not fully clarified their differences. Please help explain them ...
91
votes
5answers
58k views

When should a primary key be declared non-clustered?

While creating a test database for another question I asked earlier, I remembered about a Primary Key being able to be declared NONCLUSTERED When would you use a NONCLUSTERED primary key as opposed ...
10
votes
3answers
2k 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? The ...
3
votes
1answer
603 views

Is SYSUTCDATETIME a safe primary key?

I have a serially modified table of processed events. A utc time for when it was processed and an eventId. Is it safe to use SYSUTCDATETIME() as my primary key? I can easily use a surrogate key but ...
0
votes
1answer
25 views

Primary key which is foreign key in an relationship is specified NOT NULL. Why?

I have question about a foreign key. Primary key which is foreign key in an relationship is specified NOT NULL in some book's example. Why? I was thinking that primary key couldn't take on value "...
2
votes
2answers
882 views

DynamoDB - Multiple range keys

Is it possible to have multiple fields as range key? Say I have a table where each row is uniquely identified by <A,B,C> ------------------------------- A | B | C | D | E | ----...
6
votes
3answers
2k views

Remove field from composite key and collate duplicate data

I'm looking for the best way to go about changing the primary key on a table in my DB without having to completely reinsert data from files again. The modification I'm going to make is going to ...
0
votes
1answer
52 views

Don't know why MySQL is not creating table with primary key constraint [closed]

I can't understand why MySQL showing this error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '...
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
0
votes
0answers
40 views

primary and foreign keys used in object-relational table

Currently I am designing an object-relational database based on an existing relational database with multiple primary and foreign keys, and was wondering if it is OK to use primary and foreign keys in ...
0
votes
0answers
29 views

unique BIGINT as foreign key instead of primary key in distributed system

I am creating user management database schema. I am using Postgresql as database. Following is my approach. Please suggest if there is any performance issue if I use this structure. Requirement: ...
9
votes
1answer
11k views

Autoincrement primary key in CREATE TABLE … AS SELECT

I created table using a complicated select query via CREATE TABLE ... AS SELECT.... How can I add an autoincrement primary key in this query? For example: create table `user_mv` select `user`.`...
2
votes
1answer
67 views

Add a primary key constraint to an existing unique column

I've seen lots of information around adding a new primary key column to a large database table, but I have a somewhat different situation. I have a large table (~90,000,000 records), that has no ...
0
votes
0answers
75 views

SQL Timeout when updating specific rows that goes away when other columns are updated

The data I'm going to provide won't be very complete, so I apologize in advance for that, and will happily supplement with more on request. I mostly just don't know where to begin looking, and don't ...
1
vote
1answer
85 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 ...
5
votes
3answers
791 views

Add autoincrement to existing PK

I created a table in a DB that already exists in another DB. It was initially populated with the old DB data. The table's PK had to receive the values that already exist on those records, so it couldn'...
1
vote
2answers
2k views

VARCHAR primary key - MySQL

Currently, I have a categories table with 2 columns - category VARCHAR(50) NOT NULL PRIMARY KEY and parent VARCHAR(50). The parent column is a foreign key (FK) to the category column. This seems like ...
2
votes
1answer
57 views

Surrogate vs composite key in hierarchical data structure

I'm currently evaluating a schema for a hierarchical data structure. My main problem is how I should design the schema to prevent inconsistent data (reference of foreign key in another hierarchy). The ...
0
votes
1answer
74 views

Phpmyadmin / mysql error defining primary keys as foreign keys of two separate tables

I am designing a mysql database using phpmyadmin 3.3.10.4. Everything in the database is InnoDB. I have two tables, person with primary key person.id_numberand e-mail_address with pk e-mail_address....
17
votes
2answers
7k views

Why would a table use its primary key as a foreign key to itself

Looking through a database, I came across a table that used its primary key as a foreign key to itself. I've seen that a table can have a foreign key to itself to build a hierarchy structure, but it ...
4
votes
3answers
17k views

Listing indexes and constraints

I am looking at a SQL Server database for an application I have inherited. I have not looked into SQL Server for approximately 10 years, so please bear with me. The database table I am looking at has ...
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 ...
2
votes
2answers
506 views
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 ...
2
votes
1answer
61 views

Primary key addition for a table which contatins no data is too slow

We are using mysql. We are running 10 alter queries which contains 3 Primary key deletion and 4 column addition and 3 primary key addition. And this alter query is on three tables which don't contain ...
1
vote
1answer
37 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 ...