Stack Exchange Network

Stack Exchange network consists of 175 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

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.

1
vote
1answer
21 views

When creating a many to many relation with an attribute, should I include a non-composite primary key?

For example, I have frame and voltage tables, both of which relate to a motor object: frame(id, motor_attribute); voltage(id, voltage_attribute); I can pick a frame and pick a voltage and for each ...
0
votes
1answer
34 views

Oracle partitioning scenario with primary key absolutely required

Is there any partitioning scenario in which the partitioned table is absolutely required to have primary key?
3
votes
1answer
51 views

Do candidate key concept exist only in theory?

I know the concept of candidate key in RDBMS theory. But do candidate keys really exist in actual SQL engines? I mean is there anyway to designate particular column or a set of columns as candidate ...
1
vote
2answers
44 views

Defining a primary key: Customer numbers are unique only within a country but may be duplicated across different countries

I am facing the following problem: I have a table with customer information like the customer number, i.e. customerID set up as the primary key, and, besides a number of other data, also a country ...
1
vote
1answer
31 views

2 tables with foreign keys to 3rd table with primary key

This might be a silly question. But can you have the following: 1 Table with Primary Key, 2 different tables each with a foreign key referencing the first table Primary key? I know its possible to ...
0
votes
0answers
25 views

Specifying primary keys, and designing relationships for my data

I have this database I want to use as a backend for a teacher resource database. The columns in question are as follows: In this table, a class has one subject, one course number, and one section. ...
6
votes
2answers
245 views

How to get the ignored row while IGNORE_DUP_KEY is ON on SQL Server?

I have a tmp table shown below. In the above table IGNORE_DUP_KEY is set to ON and the id column is the primary key. Said table has only one key. After inserting lots of data I will get the ...
0
votes
1answer
47 views

Database Design - multiple tables

I am trying to build a database that has all of our employees, their skills, and the level of their skills. The idea is that a manager could go in, type in a skill, and relevant employees are returned....
0
votes
1answer
25 views

How is it possible that a key of char+bigint has better performance than key of bigint+bigint?

I would like to store some user related data into a table. The data is identified by MD5 hash. I have 2 options: option 1 - have a table with 2 columns: hash_id (unsigned bigint) and md5 (char(32))...
5
votes
3answers
168 views

No Primary Key in some sql server tables

I have downloaded WorldWideImporters Database as a standard db (Microsoft Sample Database for SQL Server). In this db, some of the tables don't have primary keys but clustered indexes can be seen. Can ...
0
votes
1answer
49 views

Auto incrementing a foreign key value the same as the primary key of the parent table in SQL when inserting data?

I have the following two connected tables as shown below in the Microsoft SQL Server. When inserting data from visual studio, the foreign key in the Reservations table (Customer_ID) should be auto ...
0
votes
1answer
60 views

Good practice for hiding primary keys from a client

Let's say I have a table like this: CREATE TABLE users ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY ); Later on in my client, I may fetch a RESTful endpoint like this: GET /v1/users/1 The ...
2
votes
1answer
644 views

Do primary keys change when indexes are rebuilt or reorganized?

I have a database containing tables that each have auto-increment int columns serving as the primary key column. When I do a rebuild or reorganize operation on my indexes, does either operation ...
2
votes
1answer
62 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
32 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
30 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
60 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
43 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
31 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
28 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
34 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
98 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
47 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
28 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
398 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
20 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
43 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
46 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
198 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
241 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
290 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
71 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
191 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
43 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
196 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
515 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
852 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
36 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
40 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
171 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
40 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
516 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
819 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
85 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
409 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
254 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
70 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
3k 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 | ...