Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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.

1
vote
0answers
31 views

Primary key: (a) set it on a single column for two distinct data points or (b) set it on a combination of two columns, each for a separate data point?

I am designing a database for an e-commerce company. Each product will be identified by a unique ID (SKU). We want to format the SKU as LLLL000. LLLL is the parent product, for example Iphone X. 000 ...
0
votes
0answers
39 views

Is There A Way To Update Every Key In A Database With 250 Tables (Magento)?

I originally posted this question in the Magento StackExchange but had no answers so I thought that since my problem is database related that I might have a better chance of getting help here. ...
1
vote
0answers
15 views

Choosing the correct shard key for MongoDB

I'm new to MongoDB. From the mongod documentation, I know that when choosing shard keys the key should always have high cardinality, so write operations are distributed to different shards (a kind of ...
0
votes
1answer
30 views

MySQL Varchar Primary Key With Not Null Constraint Accepting Nulls

I have the following table definition: CREATE TABLE `invite` ( `id` varchar(255) CHARACTER SET utf8 NOT NULL, `email` varchar(255) CHARACTER SET utf8 NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `...
0
votes
1answer
26 views

efficient primary key that does not expose business internals

I am using auto_increment primary key to store each task user performs. Users will be given the key to perform queries about their tasks. Keys are short, efficient, but are auto incremented in a ...
0
votes
1answer
31 views

Do I need an auto-generated ID in addition to a unique field?

I have a table that represents a list of currencies. Here is a simplified version of it: CREATE TABLE currencies ( id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY, code ...
0
votes
1answer
49 views

Getting around Primary Key requiring non-nullable columns

Word Problem I need to be able to create a table that has a column which is a foreign key and is nullable, but is also part of the (composite) primary key. Obviously the problem is that (at least ...
0
votes
0answers
23 views

Primary key based on high-resolution timestamp generated on client

I'm seeking a primary key for my relational database (Postgres) tables that: Can be generated on the client and has a low chance of collisions Can support fast (and possibly clustered) indexes. Will ...
1
vote
0answers
26 views

How to optimize this Postgresql query

Table A has columns (P1, P2, Value1) where P1 and P2 are a multi column primary key. Table B has columns (P3, P1, Q1) where P3 is the primary key. Table C has columns (P2, Q1, Value2) where P2 and Q1 ...
3
votes
2answers
158 views

Clustered vs Non Clustered Indexes for a Primary Key

I have a large table with around 19 million records in it. The primary key is a uniqueidentifier column called ID and there is a nonclustered index on this column. An application generates these ...
1
vote
0answers
18 views

Ideal and Efficient Way of Handling User Logins with Multiple Types (i.e. Employee, Client, etc.)?

I'm developing a database where I will have two types of users: Employee and Client. I'm thinking it would be ideal to just have one big USERS TABLE with a user_ID as PK and Type column to ...
2
votes
2answers
36 views

Any benefit in running ANALYZE after bulk insert in table with only PRIMARY KEY index in SQLite?

If I create a table in SQLite that has only the PRIMARY KEY index, is there any role for running ANALYZE after a bulk insert, or will that key automatically have been kept up-to-date by necessity? In ...
1
vote
0answers
44 views

Associate multiple tables with a single table

I have a RDBMS database and I need to associate a column in a new table to multiple other existing tables whose schema can't be changed. The new table looks something like this, UUID(pk) PartId(pk)...
1
vote
1answer
85 views

Clarification on Cardinality and Participation

I'm currently redesigning a schema with 20 or so tables and my first task is to create an ERD from the existing tables/constraints/relationships. This has proven more difficult than I expected because ...
8
votes
3answers
3k views

Can CURRENT_TIMESTAMP be used as a PRIMARY KEY?

Can CURRENT_TIMESTAMP be used as a PRIMARY KEY? Is there a possibility that two or more different INSERTs, get the same CURRENT_TIMESTAMP?
3
votes
1answer
95 views

Is there a strategy to use timestamp as primary key?

The majority of times we use integer sequences for primary keys to maintain uniqueness of row in a table. Couldn't we use timestamps instead of integers for this purpose? Is there any flaw in that ...
0
votes
1answer
114 views

Is there a performance benefit to use tinyint instead of int for Primary Key?

Is there a performance benefit to use tinyint instead of int for Primary Key when I know that the number of rows are limited in this table but this key will be used as a foreign key in another huge ...
2
votes
2answers
56 views

Viability of surrogate key based from existing column as primary key

I'm designing the schema for a database and have a question about primary key choice. There aren't any natural keys available in the tables we're looking at now although there's interest from my team ...
1
vote
2answers
67 views

Is there a way to order a PostgreSQL table by its primary key automatically?

I want to dump the whole content of a table, ordered by its primary key. Intuitively, I would like to do a SELECT * FROM %table_name% ORDER BY %primary_key_of(table_name)%; This would be run by an ...
2
votes
0answers
42 views

Design for a child to have a single parent `A` OR `B` with FK cascade constraint?

I'm assuming this is not possible with PK and FK... Let's say I have tables A, B, and C. A and B are "parents", and B and C are "children" A is the root B can be the child of either A or B C ...
0
votes
1answer
64 views

Is it ever useful to SELECT DISTINCT on the primary key column(s)?

From my understanding, primary keys are unique by definition. If that's true, would one ever want to SELECT DISTINCT on the primary key? Is this ever practical/useful?
1
vote
1answer
240 views

In Postgres 10, how do the choice of SERIAL or UUID type as primary key affect replication, if at all?

A popular answer on SO states that using UUIDs as primary keys has the following benefit: Makes replication trivial (as opposed to int's, which makes it REALLY hard) I have been unable to find ...
-1
votes
2answers
262 views

How to avoid primary key violation while doing inserts

I have a code which does insert into destination table from source, but at times we are ending up with deadlocks. Due to deadlock , I have to rerun this code again. while i do re-run , I end up with ...
0
votes
1answer
34 views

M:M Table Design - Use of PK

I have a many-to-many table that contains id's from 2 related tables. For creating a primary key on this table, is it better to create another integer column and have that as a surrogate PK? Or ...
0
votes
2answers
32 views

SQL Server - Copying a key from one table to another

In a trigger, I have created a table using SELECT * INTO. I have done that in a specific way, in order to create the table without also creating the identity column that exists in it (let's name this ...
0
votes
1answer
30 views

Relations and Sets

From my understanding, One = {1,2,3,4,5...20}, and then if Two = {1}, then One won't map into Two. On that token, how come the answer is d instead of a?
0
votes
1answer
89 views

ORA-02270: Error report: no matching unique or primary key for this column list

I have created two tables: CREATE TABLE team ( teamname VARCHAR2(30) NOT NULL, carndate DATE NOT NULL, teamnomembers NUMBER(2) NOT NULL, charname VARCHAR2(30), ...
0
votes
1answer
38 views

Find minimum number of columns that identify uniqely all rows

I'm using SQL Server Management Studio. I've a table and I want to find a Primary Key for this table. Can I write a script in T-SQL that finds which columns (and I want that the number of columns is ...
3
votes
3answers
470 views

Why intended integrity constraint cannot be enforced?

What does the following integrity constraint try to enforce? (integrity constraint is primary key and check in this case but I couldn't understand what I should really write). Explain why the ...
0
votes
1answer
459 views

Are multiple Primary Keys in MySQL allowed?

I haven't used SQL for ages and now I have to create a software with a small DB. I've decided to use MySQL with MySQL Workbench to help me design this database (by drawing a model with ER diagram). ...
1
vote
1answer
67 views

Moving data from one database to another database with auto incriment keys

I have table1, table2 and table3, all of them have a primary serial key id (or think of any auto increment type). table3 has two foreign keys(table1 id and table2 id). The questions is, I have 2 ...
1
vote
2answers
103 views

MySQL not using primary key on inner join

I have two tables: Table 1: CREATE TABLE `lk_transaction_types` ( `transactionTypeID` int(10) unsigned NOT NULL AUTO_INCREMENT, `isActive` tinyint(2) unsigned NOT NULL, `code` varchar(8) NOT ...
1
vote
2answers
216 views

Composite vs auto incrementing integer as Primary key

I am designing a MySQL 5.7 database for a mobile platform. This platform has activities that users can add to 'to-do' and 'completed' lists. I have 3 tables, one for activities where it has an auto ...
0
votes
2answers
55 views

Does using an auto increment of 1 on numeric PK hurts performance?

A teacher of mine said that having an numeric PK with auto increment is a bad practice because it hurts performance. He also said that Oracle database doesn't have an easy way to set up auto ...
-1
votes
1answer
1k views

Two foreign keys referring to one primary key

I'd to create a database that records transactions between two users. A user can transfer points (think of it as money) to another user. user table looks like: | userID | name | ...
0
votes
0answers
328 views

Auto increment primary key in Access / sql server application

I have an application developed with Microsoft Access and I have a table with Auto increment primary key(id).the last value is id=32767. In the add form I clicked on a new line, the id must have the ...
-4
votes
1answer
48 views

can I combine foreign key with primary key in `id` column in 1 table? [closed]

Here is the sample CREATE TABLE apple( `appleId` int(10) NOT NULL, `apple_Name` varchar(100) DEFAULT NULL, PRIMARY KEY (`appleId`) ); ALTER TABLE fruit( `fruitID` int(10) NOT NULL ...
0
votes
2answers
51 views

Index on Primary Key: is there any reason for that?

I'm auditing the schema of a MySQL database used by a off-the-shelf e-commerce application and I found this as output of SHOW CREATE TABLE thistable: CREATE TABLE `thistable` ( `case_id` int(11) ...
-1
votes
1answer
537 views

Creating a custom Id (Primary key) in SQL table [closed]

I have used this code to make the table and the script goes like this : USE [interlock2] GO /****** Object: Table [dbo].[t_interlock] Script Date: 3/13/2018 7:14:19 AM ******/ SET ANSI_NULLS ON ...
5
votes
3answers
424 views

When should a primary key be meaningful?

Excuse the looseness of any definitions here, but I am trying to explore a simple concept. A Primary Key uniquely identifies a row. There are possibly other columns in a table with unique values, so ...
3
votes
1answer
136 views

Composite Primary Key on partitioned tables, and Foreign Keys

We have a database where several tables are partitioned. The primary key (PK for brevity) for these tables is of the form transaction_date, id Where transaction_date is the partitioning key, and id ...
1
vote
1answer
71 views

INSERT INTO with PK andFK [closed]

I have the following problem: I created two tables: CREATE TABLE DimServers ( [ID_DimServer] INT NOT NULL IDENTITY(1,1) PRIMARY KEY , [ServerName] VARCHAR(13) NOT NULL, [Domain] VARCHAR(...
0
votes
2answers
33 views

Set PK as a combined string of table 1 and 2 records [closed]

Say, for example, I have 2 tables - Table 1 is Drink Type and Table 2 is Flavor. What is the query to create the unique identifier column which would result to drink_type_drink_flavor? +-------------...
1
vote
0answers
41 views

Foreign key constraint across tables

I have tables A, B and C where there is a one-to-many relationship between B and A and B and C respectively. I need a constraint that for every row of C rowC.propertyC is element of SELECT A....
7
votes
3answers
197 views

Choosing primary keys: Scientific names of species or system-assigned numeric identifiers?

Scientific names are well accepted unique identifiers of species. However, scientific names have special characters such as spaces and dots. Can it be a good unique identifier for databasing or should ...
0
votes
0answers
46 views

User table definition when using an third-party Identity Provider

As-Is The user table has a classic structure where everything is mixed in a single table: id BIGINT PRIMARY KEY idp_id TEXT NOT NULL UNIQUE -- user ID in the Identity Provider ...
3
votes
0answers
56 views

How to design normalized relations to store “global/generic” data?

Context I was designing a relational database model to store data about a company and its production facilities. I had a facility relation and many other relations referencing it, most of the time ...
2
votes
0answers
140 views

Postgresql - Primary key index getting corrupted after upgrade from 9.2 to 9.6

Im using a Postgresql-9.2 on Centos 6.7. I tried to upgrade the PostgreSQL using --link. After that, I took a table backup and tried to restore this on Dev server. But Im getting an error like, ...
0
votes
2answers
257 views

Make a non-ordinal numeric ID in PostgreSQL

If I declare a column to be serial in PostgreSql, it will auto-increment in a ordinal sequence. The end result may not be contigious due to rollbacks and whatnot, but it's more or less ordinal. I ...
1
vote
1answer
23 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 ...