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
1
vote
2answers
19 views

How can I get this code to work? It's in mysql, I keep getting an error 1822' failed to add foreign key constraint. Missing index for constraint' [closed]

CREATE TABLE EMPLOYEE ( employeeNumber VARCHAR (15) NOT NULL, employeeName VARCHAR (20) NOT NULL, department VARCHAR (10) NOT NULL, phone INT NOT NULL, PRIMARY KEY (employeeName, department) ...
1
vote
0answers
18 views

Composed primary keys : “should” duplicates and null values be accepted, and according to which norm?

I just passed some SQL test including a question about the possibility to have duplicates in a primary key column. The expected answer was NO. Mine is, "Yes, if the column is part of a composed ...
0
votes
0answers
17 views

How to create one primary key with many Status [closed]

Hi I'm trying to create a tablethat can allow one primary ID can have multiple status. SQL Status Status Table No (foreign key) Item (foreign key) Status 1 in1 ...
0
votes
1answer
12 views

Hash IDs (such as base64 YouTube video Ids): store as int/bigint, or as string, and why?

I want to generate "unguessable" IDs, rather than /record/1, /record/2, /record/3 etc. YouTube's style of using base 64 allows for 16.7 million IDs to be stored in just 4 characters (64 * 64 * 64 * ...
0
votes
1answer
15 views

How to set current sequence while converting primary key serial(integer) to bigserial(bigint)

The table already has few thousands of rows and I want to convert the primary key from Integer to BigInteger. How to change the sequence for biginteger? --Create new temp sequence (sequence-name1) ...
0
votes
2answers
47 views

Why is it important for every table to have a primary key?

So I have some tables in Sql Server that are essentially a list of sales, things like: ProductID SalesOrderID ProductFamilyID ProductCost ProductSource and so on. In this case, none of the ...
0
votes
0answers
32 views

SQL Azure Indexes need Rebuilding for Foreign Keys to work

We have a product that runs off SQL databases. Until recently, each of our clients had their own fileserver running SQL Server, however we have now started hosting on Azure for some existing and some ...
0
votes
0answers
43 views

Create column referencing multiple PK's from same table

I am trying to create an table, menu items, to use in my front end. Menu items can have 1 or many submenu items. So one column must be able to contain 1 or more ID's from other menu item records. ...
0
votes
1answer
35 views

Does auto increment on Primary Key prevent concurent inserts? [duplicate]

I have a Sql Server logging table, that continuously is being inserted into, I want to revise the design of this table. Currently, we are using a GUID as the PK, because the software can generate ...
-2
votes
1answer
33 views

If all associated attributes of an intended primary key are identical, is it still a true primary key?

I'll start with an example. If I have a person table with intended surrogate primary key Id: +----+------+------------+-------------+ | Id | name | DoB | SSN | +----+------+-----------...
8
votes
2answers
839 views

Does ordering by auto-incrementing PK ensure chronological order?

Is it safe to assume that ordering by an auto-incrementing PK in Postgres will order the records chronologically, from a creation standpoint? I have a many to many relationship, from which I don't ...
0
votes
1answer
93 views

Optimise primary key and index settings for multiple link tables

I have this tables stated as below. orders is the main table that will store all the details such as the customerID, merchantID, which riderID it was assign. The order details like total, subTotal, ...
0
votes
1answer
23 views

How can I use the same primary key across multiple tables as a foreign key? [closed]

I am working on a school project. I have a table of employees with a given employee ID. The employee ID on this table is the primary key. I have already set the employee ID as a foreign key on another ...
0
votes
1answer
16 views

PostgreSQL - How to list all foreign key link to Primary key of a table

I have a table res_users with primary key id I would like to list all the foreign key link to id from all the tables in my database.
0
votes
2answers
50 views

How do you determine the direction of a relationship?

Suppose I have the following relationship diagram: How would you describe the relationship determined by the red line? Reading from left to right you would say each order has only one customer so it ...
0
votes
1answer
41 views

If PK is Non-Clustered index, how to identify clustered index candidate?

I'm designing a database for insurance company. I want to make Primary key as GUID-uniqueidentifier. I want to create Index for each table in database for performance optimization. I did research ...
0
votes
1answer
33 views

Why is my primary key a separate relation taking up storage?

I have a table with a primary key. Postgres automatically created an index on this primary key, and it is separately taking up +34% more storage. Why does Postgres not simply keep the table sorted by ...
1
vote
0answers
22 views

DELETE a single row from a table with CASCADE DELETE picks a slow plan… but not always

Schema Summary A dozen tables related by foreign key to a central table (call it TableA). All tables have a PK that is INT IDENTITY, and Clustered. All the tables' FKs are indexed. This looks like ...
2
votes
3answers
86 views

Is it possible to track the progress of when I drop a the primary key clustered index on a table?

I have a large primary key clustered index I need to drop. I believe dropping it will also trigger index rebuilds on the nonclustered indexes (which are also pretty big). Is there a way to determine ...
0
votes
0answers
19 views

How to handle table that has an id which references multiple dimension tables?

I'm having a hard time figuring out how to create a lookup table or a table that references multiple dimension tables. I've got a "tracking" table that tracks the number of impressions for an e-...
0
votes
0answers
16 views

Clustered Index in Big Partitioned Table on SQL Server 2019

We are in process of developing a project which will process around 51 000 000 records daily (~around 500-600 inserts per second). We choose "Always-On" solution which will contain: One primary ...
0
votes
0answers
55 views

What is the difference between candidate key, super key and alternate key

I'm in IT but not in software development or database. I studied about DBMS subject 6-7 years back. I have confusion between these, candidate keys are like ideal candidates to become become primary ...
-1
votes
1answer
26 views

Is there a usecase to create separate indices on the foreign keys of junction/associative table?

Imagine an associative/junction table junction_table_ with (pseudocode) columns like: id_of_table_x REFERENCES TABLE X id_of_table_y REFERENCES TABLE Y PRIMARY KEY (id_of_table_x, id_of_table_y) So ...
0
votes
2answers
77 views

What's the easiest way to move tables with identity key columns while keeping the referential integrity between them?

I have four tables in database A that have identity key columns and have foreign key references to each other on those columns (without actual foreign key objects created, just the columns themselves)....
0
votes
0answers
27 views

Should I use two indexes? [duplicate]

I would like to create some tables (including users table and products table) inside MySql Database with those index for each tables: Private Index (uses int), for relational table purpose only ...
2
votes
1answer
243 views

UUID or BIGSERIAL Foreign Keys Postgres

I have 8 tables which use UUID Primary Keys and 7 of them have UUID Foreign Keys. Would it be better for performance to use BIGSERIAL instead of UUID?
-1
votes
3answers
119 views

ID, UUID and “deleted_at” columns, How to avoid performance degradation?

Consider this table, but with thousands of records, on PostgreSQL: | id | uuid | color | ... | deleted_at | |----|---------|----------|-----|---------------------| | 1 | 4fc1... | red ...
0
votes
0answers
27 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
320 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
1answer
30 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
903 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
57 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
45 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
44 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
42 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
277 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
71 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
62 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
365 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
60 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
57 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
64 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
49 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
74 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
1k 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
130 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
78 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
34 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
41 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 ...

1
2 3 4 5
11