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
0answers
18 views

DB2 for IBM i selecting from insert [closed]

I'm writing a script to automate exports of data in the form of inserts to a header/detail table setup, looping over the header then looping over detail within the header. I'm using the following: ...
0
votes
0answers
20 views

Normalization of a Database - Is primary key = candidate key?

I'm given a database called Projects where I know that id, pid, sid is the primary key. My goal is to decompose this database such that each sub-relation is in BCNF or 3NF. P(id,pid,sid,sn,pn,mid,mn) ...
2
votes
2answers
71 views

how to add data compression to a primary key of a table?

We work with tables with over 300GB of size, a small percentage of them tables use sparse columns although that doesn't always agree with CPU utilization as this question shows. For the tables that ...
1
vote
0answers
22 views

Find all tables in a snapshot replication that have primary keys

I've been tasked with moving all tables in a single snapshot replication that have primary keys to the transaction replication. I've tried to break it down into two steps, finding all tables in a ...
4
votes
2answers
69 views

When the primary key is also the foreign key, in Postgres

In Postgres 12, I am tracking photographs. Each is large, about 6 to 8 megs each. For quick browsing I will store a thumbnail image along with the full image. Both full and thumbnail are BYTEA type ...
0
votes
1answer
51 views

SQL Server Chaining Many-To-Many tables

I have a scenario where we want to build an inventory description system and I am trying to design the schema but need a little guidance to make sure i'm not veering off the track. The basic premise ...
0
votes
1answer
37 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
21 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
38 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
53 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
31 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
48 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
23 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
42 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
57 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
270 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
87 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
47 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
31 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
28 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
37 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
42 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
57 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
147 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
32 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
69 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
82 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
342 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
290 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
117 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
31 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
160 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
284 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
80 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
49 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
186 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
47 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
59 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
820 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
53 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
33 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))...