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
42 views

Using a second table for unique key?

This is a fairly noob question. I work with an old-school PowerBuilder software (PowerCampus) that has some strange design decisions from the 80s and 90s. What's perhaps the most important table in ...
1
vote
2answers
54 views

SQL Server: Explaining to C-Level Executives the impact of using Primary Key of VARCHAR(50) in many tables throughout a database?

Suppose a global, mid-sized company is considering a document control management solution that is backed by a SQL Server database and allows developers to create document form types using a form ...
1
vote
1answer
34 views

Optimization on table selected where primary key

I have this query: SELECT * FROM wp_dev_posts, wp_dev_postmeta, wp_dev_term_relationships WHERE ((post_type = 'projects') OR (post_type = 'post_cost_codes')) AND (wp_dev_posts.ID = ...
1
vote
1answer
39 views

Can and should relational database contain tables that don't have primary keys? [duplicate]

So this site says: "All tables in a relational database should have a primary key". Here it also says that every table should have a primary key. But if I look here and here they say that tables ...
1
vote
1answer
64 views

What is the fastest way to (session) lock table?

I have some triggers to log changes on a table to Log table. On insert and delete I add row to the Log table and for update I add two rows. The log table contain identity column, and I want the 2 ...
1
vote
1answer
27 views

Insert into table with column same values as auto increment column ( computed column )

Say I have a table with "column1" (Primary Key, Auto Increment) and "column2". How can insert into the table with column2 will have the same values as column1 which is auto increment column?
1
vote
2answers
78 views

primary key constraint Sorting

Is having a field in primary key constraint optimizes sorting of the table ? Here's the schema of the table: CREATE TABLE Persons ( Field1 long NOT NULL, Field2 long NOT NULL, Field3 long NOT NULL, ...
1
vote
1answer
37 views

Duplicate rows with primary key constraint in PostgreSQL 10.6

I have a table that's had CONSTRAINT table_pkey PRIMARY KEY (id) since setup. However, I recently found that I have around 100 duplicate rows (I ran select count(*) from (select count(*) from table ...
0
votes
1answer
28 views

How can i replicate many database into one, by avoid primary key collision?

I have n number of databases and I have to collect data from those into one database. The databases has some specific value (e.g. currencies) which I want to update from the collector database(of ...
2
votes
1answer
34 views

Do I need a Primary Key if it's already an Index

I have a bunch of tables with no Primary Key. Some do already have a non clustered index comprised of what would be the Primary Key (ClaimID, LineNBR). The columns used are currently set to accept ...
0
votes
1answer
26 views

How do i get a list of records from two tables that have different values based on a matching PK? [closed]

Lets say i have the following two tables: Table1: |IP |Name | Gateway | Version | +------+------+---------+---------+ |1 |A | 1.0.0 | v1 | +------+------+---------+---------+ |2 ...
0
votes
2answers
27 views

Speeding up Postgresql manual index reset?

I'm merging databases with each other. One problem I have is that both of the database tables have the same primary keys (starting from 1, but otherwise they are different records). To solve this, I ...
0
votes
1answer
13 views

SQL Server space in MDF file with multiple files and one Primary file

We have space crunch in our F Drive and our H drive. We have added one more drive - I drive and it has 500 GB. Can we add the new MDF file in the I drive and set the autogrowth option in the F drive ...
0
votes
2answers
37 views

Predicting the primary keys exhaustion in MySQL based on rate of insertion

We are running out of primary keys for our table in production. We do not want that to occur again. So we need a mechanism to alert us when we are running out of primary keys based on the rate of ...
0
votes
2answers
44 views

Query not returning results if selecting by indexed columns (Mysql 5.6)

We had a very strange encounter that all of our queries that uses an indexed column in the select wasn't returning any results. On the other hand, if we do select * or on any other non-indexed column ...
0
votes
1answer
94 views

mysql - can a slave have a different primary key than the master?

I have a table with PRIMARY KEY (`id`) and I want to change it to PRIMARY KEY (`username`, `id`). These columns are defined as: `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `username` ...
0
votes
2answers
30 views

Two composite foreign keys have identical columns which must be equal value

Let's say I have a relation: CourseRegistrations with composite primary key: (course_id, sem_id, student_id) I have another relation: CourseEvaluations with composite primary key: (course_id, sem_id, ...
0
votes
1answer
40 views

Alter table - existing column as primary key

I am attempting to set one of the columns in the table as primary key. Here are the columns from the table: Now, when I run the sql: ALTER TABLE cb_company ADD PRIMARY KEY(uuid); I get the ...
0
votes
2answers
65 views

What is the difference between a foreign key and a primary foreign key?

I understand that you use foreign keys to relate rows from one table to the other and if this foreign key also becomes a primary key of that table then it is a primary foreign key. But, for example, ...
1
vote
2answers
36 views

How to get number of PK used as FK?

How to get number of PK used as FK? Table 1 has a column ID (PK). Table 2 & 3 has a column ID (FK - refers Table 1 PK). How to get Value 1 (PK) has used in table 2 & 3 or not without ...
3
votes
1answer
312 views

Checking @@ROWCOUNT failing

I have a code similar to the one below in one of my SQL procedures, declare @rowcount int update table1 set value = @value where id = @id select @rowcount = @@ROWCOUNT if ( @rowcount = 0 ) begin ...
0
votes
2answers
174 views

What are the disadvantages of using surrogate keys? [closed]

I am using MS SQL Server but in general database design I want to know what problems can arise when every row in a database has its auto generated surrogate key value. I know some advantages, for ...
0
votes
1answer
88 views

What problems are there in not using a primary key column at all?

What purpose would a primary key column serve on a table such as post_versions here? Nothing refers to it, and there are no queries where I will ever want to select a row by post_versions.id. It'll be ...
0
votes
2answers
27 views

Finding MySQL Tables with no primary key OR no composite key

I can easily find tables that do not contain a PK from information_schema, but I'm having a tough time writing the logic to find tables that might contain neither a PK nor a Composite Keys (via two ...
0
votes
2answers
94 views

Dealing with subtypes and supertypes in Database design

I'm currently doing a database project for a class and I am having some difficulty dealing the subtypes and supertypes. This is the list of entities and their attributes: Piece (PIECE_ID, Description)...
2
votes
2answers
113 views

AdonisJS: is something wrong with belongsTo() relationship design?

In AdonisJs framework, suppose we have a Profile model in which we define belongsTo() relationship to theUser model. The documentation says this will lead us to define our databases this way: The ...
1
vote
2answers
45 views

Way around Composite Primary Key requiring Composite Foreign Key

Here is a table called Make (where name is the PK) name|other columns ---------------------- A | ... B | ... C | ... And a table called Type (where make, name together is the PK and make ...
1
vote
1answer
31 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
42 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?
5
votes
2answers
145 views

Does the concept of candidate key 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 any way to designate a particular column or set of columns as a candidate ...
1
vote
2answers
46 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
45 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
26 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
628 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
52 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
29 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))...
6
votes
3answers
199 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
133 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
72 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
759 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
163 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
47 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
40 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
84 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
178 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
31 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
46 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
242 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 ...