Episode #125 of the Stack Overflow podcast is here. We talk Tilde Club and mechanical keyboards. Listen now

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
0
votes
1answer
34 views

Can I fix this insertion anomaly without using a composite key?

Suppose I have a schema with the following basic elements: Client : id (PK) Pet : id (PK) owner_id (FK to Client) Contract : id (PK) client_id (FK to Client) PetInContract : ...
0
votes
1answer
14 views

Primary Key issue in MemSQL doesn't support case sensitive

Creating table in MemSQL and creating composite primary key. Lets create simple table with following structure: Table User Data Name userid Primary Key (userid) And assume application support case ...
1
vote
0answers
36 views

Changing clustered key on a big table

I have a big fact table in a datawarehouse (40 Gb worth of data in dev, 120gb in production ) which was created in a very classic way CREATE TABLE BigFactTable ( BigFactTableId bigint IDENTITY ...
1
vote
1answer
46 views

How to change Postgres primary key column values?

My postgres database has a column called "id" that runs from 40,000,000 to about 50,000,000. The "id" column is the primary key. I need to change the "id" column values such that they span different ...
1
vote
1answer
26 views

What is a “part-key”?

I'm a beginner and student with regards to database design. The tutor recently discussed “part-keys”. I am struggling to find any information on what a part-key is, and I suspect it probably goes by ...
1
vote
1answer
45 views

How do I reference a row which has two foreign keys as its primary key?

I am currently trying to setup a database managing players and teams for a competition. Players are competing in teams of two but may be shuffled. As every team combination can only exist once, I ...
1
vote
1answer
17 views

Updating table basing on composite primary keys from derived table

I have two tables TableA and TableB defined as followings: TableA has 4 columns id1 (Primary key), id2 (Primary key), code, enable. TableB has 3 columns id (Primary key), code, loc. TableB.code ...
1
vote
2answers
40 views

Primary Key Int vs String [duplicate]

I have an attributes table, where I want a unique name for each attribute in order to prevent confusion between potentially similar attributes. In terms of the table design, the way I see it, there ...
3
votes
1answer
52 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
56 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
35 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
43 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
67 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
73 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
85 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
44 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
30 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
37 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
30 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
25 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
40 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
48 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
128 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
53 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
70 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
37 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
329 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
229 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
98 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
29 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
121 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
198 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
52 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
45 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
174 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
52 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
731 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
30 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
213 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
190 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
73 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
808 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
222 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
63 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 ...