Questions tagged [primary-key]

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.

Filter by
Sorted by
Tagged with
1
vote
1answer
28 views

Unique ID for Row of a Set in One Table

I know the title is a little weird but what I actually want to do is simple. In my Postgres db a user creates a profile and uploads an image. user_id: 1, photo_id: 1 Now a second user makes a ...
0
votes
0answers
1k views

Postgresql Restore - duplicate key value violates unique constraint

I have a PostgreSQL 9.2 server and want to restore this to PostgreSQL 9.6. Here is the table. create table mytable ( ID int, EID int, Name varchar(100), Address varchar(300), primary key (ID, EID) )...
0
votes
1answer
38 views

what are the impacts if I convert one of the column to primary key or add a primary key to an existing table

One of my MSSQL table don't have any primary key, I would like to add one. Just want to know: First, if I convert an existing column (or multiple columns) to a primary key, what would be the impact? ...
1
vote
1answer
142 views

How to prove “A->B,C then A->B and A->C” from Armstrong Axioms

I know that this is true but I don't understand how to prove it. The Problem: Prove or disprove that if A->B,C, then A->B and A->C This answer is similar but it does not prove the relation using ...
1
vote
1answer
88 views

# of key locks while updating

I am using SQL Server 2008, RTM. I have a simple table defined as create table t(id integer not null primary key) This table has 50,000 records, just in case. When I issue an update transaction ...
1
vote
1answer
82 views

Is it safe to make a Unique Index the Primary Key?

I have a vendor database that I want to use Change Data Tracking against but cannot because none of their tables have Primary Keys defined. The tables do have Unique Indexes but they are not defined ...
5
votes
1answer
15k views

Can a database table not have a primary key?

Can a database table not have a primary key? SQLite lets you define a table with no primary key therefore there can be no insertion anomaly.
0
votes
3answers
502 views

Composite Primary Keys

I have a small scenario here and I think using composite primary keys would be a good solution. I'm doing this in SSMS. Here is the code to create the table: CREATE TABLE [PSDesk].[dbo].[Local]( [...
2
votes
1answer
157 views

Can I change which index a primary key uses?

I have a table with a non-clustered index on the id, and a clustered index on some other fields. The fields of the clustered index are non-sequential, and are frequently modified. I'd like to change ...
2
votes
1answer
26 views

Existing data to new table, how to set up the PK & Identity

I am moving an Access app to SQL Server. I have confirmed the data in the existing PK is unique so I want to copy it into the new PK Identity field. The field is currently set as IDENTITY(1,1). I ...
5
votes
1answer
2k views

Designing a database structure for companies and stock owners

I'm working on a personal project where I want to make a database that holds the information for every owner of every public company. Say for example "Sears INC" and the program would get the ...
1
vote
1answer
45 views

Why does key “concatenation” help to bring an unnormalized table to 1NF?

This question is about the proposed technique to bringing an unnormalized table to first normal form (1NF) that my textbook "A Guide To MySQL" presents. It gives an example of an unnormalized table ...
0
votes
1answer
2k views

Filling two tables with a Foreign Key Relationship [duplicate]

I need to create a star schema of table and apply on SSMS. To be simplier I focus on two tables: One containing general informations about clients. Each client has a particular ID but can appear ...
6
votes
2answers
452 views

Should I avoid adding a clustered key to a table with high throughput

We have a SQL Server solution that has a table dsStaging.Audit that stores audit records created by a third party transactional database. We use these audits to synchronise CRUD operations from the ...
-2
votes
2answers
393 views

Problems with having SQL Server Identity Column in the Cloud

Our company is utilizing Identity(1,1) clustered primary index in SQL Server 2016 Enterprise, and will eventually deploy into the Cloud (Amazon AWS, Google Cloud). Is there any issues utilizing ...
2
votes
2answers
1k views

Identity Column in Concurrency, Multithreading, Parallel Processing

Our company would like to understand the effects: If we have single table with Primary Key Identity column, int primary key IDENTITY(1,1), and the Application is conducting multi-threading parallel ...
1
vote
2answers
2k views

Fact table Primary Key: Surrogate Key or multi-column Natural Key?

What's the best practice when choosing a fact table's Primary Key (PK for brevity): Should we have an auto-incremented Surrogate Key (SK), or should we use Natural Key's (NKs) columns? The advantage ...
0
votes
2answers
4k views

How to use mySQL to generate auto-increment ID for me

This may be a bad structure. Let me know if it is, it's been a long time since I did any database design. I have a tblGame in a mySQL database, which has two fields: ID_Table ID_Player In the ...
2
votes
2answers
335 views

Merging multple pg_dumps with columns using auto key

I have two unique instances of Postgres using the same database schema but with independent datasets. One of the tables has a column setup for auto increment and it is a primary key. No other tables ...
1
vote
2answers
169 views

Proper table design for sparse primary key

In my system I have temporary entities that are created based on rules stored in my database, and the entities are not persisted. Now, I need is to store information about these entities, and because ...
6
votes
4answers
2k views

How is this SQL Server PK violation possible?

I'm getting an error that I thought was impossible. I have a SQL Server (ver 11.0.6594) table that looks like this: CREATE TABLE [IDMaster]( [ID] [int] NOT NULL, CONSTRAINT [PK_IDMaster] PRIMARY ...
1
vote
2answers
3k views

Duplicate key error for composite primary key when running update query without having duplicate

I have a table with the below schema CREATE TABLE `my_field_table` ( `entity_type` varchar(128) NOT NULL DEFAULT '' , `bundle` varchar(128) NOT NULL DEFAULT '', `deleted` tinyint(4) NOT ...
1
vote
2answers
395 views

MySQL Design - What is best primary key for our needs?

The site I am designing for is in the final stages of development. It is a forum where, obviously, users ask and answer questions. The owners want id values to be of no value to competitors. These ...
0
votes
1answer
75 views

SQL Server - moved index behind PK constraint to different filegroup, but space not freed

I have a large table Foo. There are 4 filegroups (FG1, FG2, FG3, FG4) FG2 is on a disk that is nearly full. It has 2 indexes: a) clustered index (on Date) b) a unique non-clustered index (on ID) ...
1
vote
1answer
678 views

Identity versus Composite values for Primary Keys in Many-To-Many Tables [closed]

I am in the middle of an interesting "Data Architecture" discussion at a place I am contracting & need some input. As a habit, I always create a Surrogate Key as the Primary Key in my tables - be ...
1
vote
2answers
741 views

Using VARCHAR keys or INT

I'm building a Data Warehouse in MySQL based on yelp dataset. Most of the keys in the dataset are given as strings: user_id review_id business_id hzw-qTUVpmLAKjdkoUNh8A ...
0
votes
1answer
680 views

Can I reorganize a Primary Key after deleting some rows?

In my project there is a table in which I add testing data for smoke tests, proceed with some tests, then delete the data I added. The Primary Key on that table is and not null int and has an auto ...
5
votes
2answers
5k views

Autoincrement vs composite primary key for a big table on innodb

I have a had a quite lengthy discussion with Rick James on this this we came out with idea of having composite key to replace the autoincrement pk where the int is limited close 2 billion. My table ...
9
votes
1answer
1k views

Does Postgres offer a feature like “NEWSEQUENTIALID” in MS SQL Server to make UUID as primary key more efficient

Microsoft SQL Server offers the NEWID command to generate a new GUID (the Microsoft version of UUID) value that can be used as a primary key value (in their uniqueidentifier data type). These are not ...
3
votes
2answers
520 views

How to TRIGGER/RETURNING this generator function in PostgreSQL?

I am providing a key table to my users in various categories so I am thinking how to TRIGGER/RETURNING the function with each SELECT. Functions to generate shorthand unique IDs The thread answer ...
4
votes
2answers
150 views

Two primary keys but not a composite, what is this sort of relationship called?

I am integrating employee/position data with another system. The first system is HR. It has an Employee table and a Positions table. The second system tracks office space and tracks who the office ...
0
votes
2answers
89 views

Adding TIME data to a DATE type column in time series table, and what sort of foreign key

I've got a v. simple table currently containing millions of data points of price on a given date: CREATE TABLE data_point ( id INT PRIMARY KEY, symbol_id INT, x DATE, y DOUBLE ) This ...
1
vote
2answers
483 views

Does it makes sense to index a primary key if it is not used?

I have a database with many tables which can be joined by indexed keys (primary and foreign keys). Some tables are transaction tables which have an autoincremental primary key just to define a ID for ...
1
vote
1answer
4k views

compouned (composite) unique keys

I was looking in this page but also on stackoverflow and google because I though this may be a quite common question but could not find anything related... I may not be using the right terminology ...
0
votes
2answers
5k views

alter primary id field to uniqueidentifier GUID as default in sql server

I have tables with id(primary key - int) with identity specification (1,1) . I want to change this column to uniqueidentifier with default ( newid() ) . What I've tried is ALTER TABLE myTable ALTER ...
1
vote
0answers
130 views

PostgreSQL problems after vacuum - multiple rows with same pkey, different counts of those rows from seq scan vs index

We tried to run a full vacuum on our Postgres database the other day because of disk problems. A bunch of things went wrong along the way and eventually we stopped the vacuum. Now we have some new ...
3
votes
1answer
12k views

INSERT INTO table FROM SELECT * with nextval() specification

I need to copy records within the same table changing only one field. My table has default-generated sequence entry_id_seq, however I am not sure id column is SERIAL (how to check that?). \d tab ...
6
votes
1answer
119 views

Incorrect uniqueidentifier works without a Primary Key but fails with one

So we were recently running some logic tests on an insert into a table with a uniqueidentifier as a primary key. Here's my test query; IF OBJECT_ID('tempdb..#DatabaseTable') IS NOT NULL DROP TABLE #...
2
votes
1answer
71 views

Pull data from one DB Server to another DB server

I want to pull Few tables (33 Tables) from one DB server A to another DB server B. I have Schema ready with few datas in DB Server B but when I try to pull data from DB Server A to DB Server B, ...
1
vote
1answer
181 views

Hundreds of millions of rows: Index by composite BIGINT, VARCHAR, or CHAR of HASH?

Given the following: Target platform: MySQL 5.7, using InnoDB. Scenario: Storing hundreds of millions of email addresses (plus some properties not used in queries). All queries will be done by ...
1
vote
1answer
634 views

How do I set up primary and foreign keys referencing two different tables?

I need to have two primary keys also to be the foreign keys in the same table. These two keys are referenced in different tables. I keep getting error messages that are unclear to me. My code is as ...
-2
votes
2answers
86 views

How to identify foreign id value [closed]

People say that the primary key for a table should ideally be an integer "Id" which has no meaning in the outside world. Under these circumstances how does one handle the following situation in a ...
2
votes
1answer
2k views

GUID vs Auto Increment Insert performance

I'm testing the performance of inserts between Auto increment integers vs GUIDs (v4) in tables with a lot of data. Following this blog post, i was expecting seeing a difference. But right now with ...
1
vote
1answer
132 views

Advance primary key after mass insert

I have a table with a few rows in it. It has a primary. I insert a large number of rows, including all fields including the primary key. Now when I try to an insert without specifying the primary key ...
1
vote
1answer
43 views

Ensure same tenant in a Many to Many relationship

I am trying to draft a very simple data model for a multi-tenant database. So far I have only 3 tables: company: pretty much my tenant account: is part of one company and zero or more groups ...
2
votes
1answer
1k views

partitioning a table on a column that does not belong to the current clustered index

I am partitioning the following table, 300 million rows, data from 2011 until today (until 24-mar-2017 in the test system) I want to partition my table over a column called OrderDateSID int that has ...
2
votes
3answers
3k views

How to change VARCHAR length in PRIMARY KEY in SQL Server 2014

I need to change the VARCHAR length from 20 to 25 in a code column in a library table in SQL Server 2014, but SSMS wants to DROP and RE-CREATE all my tables where the library table in question is ...
3
votes
1answer
471 views

How do Databases Ensure Uniqueness of Primary Keys, Particularly UUID Keys?

First off, I am assuming that it is possible for a database to generate but not insert a duplicate UUID value for a column that has a unique constraint. Is there any intelligent way that databases ...
2
votes
1answer
1k views

Duplicate key value error message on a database for a writing groups scenario

I am trying to create a database as portrayed in the following diagram: This is the SQL-DDL code I have written: CREATE TABLE Publishers ( PublisherName VARCHAR(50) NOT NULL, ...
4
votes
1answer
1k views

Inserting in PostgreSQL Foreign Table Violates Primary Key Constraint10

I have two local databases in one of them I have created a foreign table that is referencing a table in the second database. When I want to insert in foreign table it inserts the primary key all over ...