We’re rewarding the question askers & reputations are being recalculated! Read more.

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
2
votes
1answer
239 views

What is the meaning of “minimal key”?

In the definition, it says minimal key is a minimal set of attributes needed to identify the table. Does the "minimal" part mean "minimal number of attributes" or "cannot be eliminated"? For example: ...
1
vote
1answer
70 views

mysql : ordering a table in the order of the primary key

I have a table with a primary key consisting of two integer columns. I store ~200 millions lines in this table. My program needs to get the full table ordered by the columns of the primary key in the ...
1
vote
1answer
41 views

Best structure for MySQL table

I'm designing my table, but I have doubts: I will have two columns A and B that cannot have repetitions and, in the same row, can't both be null. I don't know if the best thing to do is to use a ...
2
votes
1answer
61 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 ...
1
vote
0answers
106 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
198 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
33 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
50 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
304 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 ...
1
vote
0answers
35 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 ...
4
votes
2answers
967 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
26 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
60 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
59 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
596 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 ...
10
votes
3answers
4k 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?
4
votes
1answer
768 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 ...
2
votes
1answer
1k 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
91 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 ...
2
votes
2answers
1k 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
46 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
678 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
1k 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
2k 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
39 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
90 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
31 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
377 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
49 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
588 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
2k 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
118 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
1k 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
364 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
103 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 ...
0
votes
1answer
6k 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 | ...
-4
votes
1answer
89 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
57 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) ...
6
votes
3answers
2k 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 ...
4
votes
1answer
782 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
133 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
34 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
47 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
216 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 ...
2
votes
0answers
74 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
236 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
740 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
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
952 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? ...