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

Can a database have an unnamed row?

I came across a question asking whether it is possible to have unnamed rows and columns in a database. I assumed unnamed implied primary key. I'm not sure whether my assumption is correct. When I ...
0
votes
0answers
11 views

interactive database with depent combobox

I have to do a tool to fill by user. User must from three "dimension" choose one "area". "Area" is divided on "problems" and output is choice one "problem&...
0
votes
2answers
44 views

What are the performance implications of using uuid as primary key in Postgres 10.12? (need canonical answer) [duplicate]

I'm at a crossroads where I need to decide if I'm going to stick with bigserial as my primary key, or change to uuid (non-auto-generating—my API server will generate the ID using uuid v4 and insert it)...
0
votes
1answer
23 views

In SQLITE, how do you create a new table into its own file?

I am trying to do key value pairing, where performance is the top priority. I understand that the smaller a sqlite file is, the better the read and write speeds are, and so I am going to create about ...
0
votes
1answer
22 views

Determining primary key columns in PGAdmin, if the constraint name is generated by system

I am trying to learn a new schema of a PostgreSQL database, but it's unclear from looking at pgAdmin which columns per the tables constitute the primary keys. It appears that when the tables were ...
2
votes
1answer
16 views

MySQL - Exclude a result if even one foreign key doesn't meet criteria (date availability checker)

I have three tables, one that lists regions, one that lists hotels and another that lists reservations. The hotels table has a foreign key for the regions table, to show which hotel is within which ...
1
vote
1answer
35 views

SQL: How to get all tables' columns & keys + the primary key?

Database Info MySQL 8.0.22 InnoDB The Goal I'm trying to get all tables in a database, with their column definitions and foreign keys, and primary keys on their own row for each table group. Given the ...
1
vote
1answer
61 views

MariaDB Uuid as Primary Key Performance Problem / Tweaks

im upgrading our mariadb database from PRIMARY KEY INT AUTO_INCREMENT to UUID PRIMARY KEY. We have around 80 tables with multiple relations between them. I've build a migration script to change the id ...
0
votes
1answer
66 views

Innodb primary key advice

We have a database table growing at a rate of ~20 million records per month, we're expecting this to double shortly. The existing schema has a uuid4 as a primary key, and then two additional indexes ...
-2
votes
2answers
25 views

PostgreSQL read performance is terrible [closed]

Table id, description situation Contains 5 records id is PK request SELECT * FROM table WHERE id = 1 Execution 743 ms Question Why in heavens name is this so slow. Same design, samen query on MySQL ...
1
vote
1answer
29 views

Are indexes need for parts of primary key?

In Postgres, is it beneficial to query performance to create an index on field2 in the following table if I intend to sometimes use queries with only field2 in the WHERE clause? CREATE TABLE my_table (...
1
vote
2answers
70 views

How to allow NULL in foreign keys of a compound primary key

I want to have NULLs in foreign keys of a compound primary key. This is an example of what I would expect to be valid data. product_id variant_id 123-123 ABC 123-123 NULL 456-456 ABC I cannot ...
0
votes
0answers
8 views

Making similar points close in distance O(n) query by distance

I'm trying to create a database in DynamoDB that has many points with high dimensions. For the most part, I have designed the database to the regular access patterns. However, for a search endpoint, I ...
-1
votes
1answer
82 views

Error 1005 errno 150 “Foreign key constraint is incorrectly formed” where no foreign key declared

I have an issue on a basic CREATE TABLE statement Error 1005 errno 150 "Foreign key constraint is incorrectly formed" returned where no Foreign key is declared - CREATE TABLE Person ( ...
1
vote
2answers
39 views

Does it matter to convert integer primary key into equivalent string?

In a PostgreSQL DB, I have a sortable bigint primary key, which is a bit verbose in terms of readability. I want to encode it as base36 or base64, like using char(n). In theory the number of bytes ...
0
votes
1answer
27 views

Should we have OrderId and separate OrderNumber for E-Commerce Website or we can have only field for both?

I am working on an Online Grocery Store type of website in which I have one order table to store the order details. Here I am having confusion that whether I should have an OrderId (primary key) and ...
2
votes
1answer
32 views

Best practices for integer key types - mixed or all the same integer type?

I am designing a postgres database for scientific data. It has about 30-40 tables, many of which are lookup tables with less than 10 records. The bulk of the data are concentrated in just a few tables ...
-1
votes
3answers
92 views

Advantages / disadvantages of simple vs compound primary keys

I am trying to model for a database. For one part of the problem I have an entity Area, which contains Departments, and each department is assigned specific functions. I have the following. I have ...
0
votes
1answer
80 views

Create fast-refresh MV over dblink on table without PK?

I want to create a materialized view between a primary system (Oracle 18c) and an external system (Oracle 19c) via a dblink. The MV would refresh on a 30-second schedule (since on-commit is not ...
1
vote
1answer
51 views

Why am I able to create MV logs using PK — even though there is no PK?

I have a table that does not have a primary key: I'm able to create MV logs on the table — without specifying whether to use the primary key or the rowid: create materialized view log on maximo....
0
votes
1answer
24 views

Postgres: find all FK constraints associated with a given PK

Given some input PK column, e.g. ani.animals.id, I want to find all FK constraints names associated with that PK. I also need table and schema's names where each particular FK constraint is located. ...
0
votes
1answer
21 views

How to design table to allow marking records as related to each other?

My web app's main MySQL table has tens of thousands of rows. Pretend it has 2 columns: ID, Content. I'm trying to design a new feature that allows rows to be marked as related to each other. Obviously ...
1
vote
0answers
30 views

Using one sequence across all tables in PostgreSQL [duplicate]

I propose using a single sequence for the bigint identity columns of most or all tables. (Essentially, OID, except 64-bit.) This helps prevent security problems for a multi-tenant application that ...
0
votes
1answer
31 views

Am I breaking something if I set an autoincrement PK to 0?

I have this table CREATE TABLE `test_table` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `name_UNIQUE` (`name`) ) ENGINE=InnoDB; ...
0
votes
1answer
28 views

Two key attributes in an EER, what does it mean?

I'm currently drawing an EER diagram and stumbled across this problem: Let's say I'm modeling a workplace where all employee's uniquely can be identified by their name (ie no employees share the exact ...
0
votes
2answers
28 views

Multiple Primary-Foreign Key connections between Tables Redundant?

I have two tables, Reviews and Critic I'm pretty new to RDBMS and I was wondering if the connection between Review.rID and Critic.Review is redundant? My reasoning is that a single critic can have ...
0
votes
2answers
34 views

In mysql, how to add a primary key and a composite key to a table so that it forbids duplicate rows, when there are null values within the columns?

In the following table (8 mi rows), every column has Null values. However, there can never be two identical rows (there will be always at least one column with a different value for each row). How can ...
0
votes
1answer
21 views

Whats the right table relationship for these entities?

I want to store all the data for the following entities: Users Projects Roles A user can work in multiple projects and can have many roles, but only one role for a user should be allowed in each ...
1
vote
1answer
51 views

How to find out if a row with a specific primary key exists in a table in SQL [closed]

I have created a table in SQL which is named testTable like this where I have defined the: PRIMARY KEY (FeatureNbr, DateTime) CREATE TABLE testTable ( [DateTime] SMALLDATETIME NOT NULL, [...
0
votes
1answer
26 views

Pros and Cons of building a relationship model where all table ids are just “_id”?

I have always had the pk of the table as this pattern: lowercase tablename + Id. That was what I was taught in college and didn't think about it. When I was challenged on it from someone using mongo ...
-1
votes
1answer
20 views

Nullable integer as part of composite primary key

I want to store phone numbers as integers and I've created this SQL: DROP TABLE `user_id_phone`; CREATE TABLE `user_id_phone` ( `user_id` int unsigned NOT NULL, `country_code` ...
2
votes
2answers
126 views

What is the best choose for PK in Partitioned Table?

I have one large table that is partitioned (table name: Trans). At the moment this table is to be created on 32 partitions. This table will contain approximately 300 million records and data older ...
2
votes
1answer
41 views

Implications of removing unnecessary column from primary key

I have a database in which a number of tables are defined with multiple UUID columns forming the Primary Key when they should instead be defined as Foreign Keys, e.g. CREATE TABLE [dbo].[SomeTable]( ...
0
votes
2answers
39 views

Using secondary id as foreign key to other table

So I have a Customer table that is linked to different tables. For instance, a Configuration table is linked to Customer table. Instead of using Customer Id as foreign key to the Configuration table, ...
1
vote
0answers
17 views

How not to “naturalize” surrogate keys?

Don't naturalize surrogate keys. As soon as you display the value of a surrogate key to your end users, or worse yet allow them to work with the value (perhaps to search), you have effectively given ...
0
votes
0answers
26 views

Text type vs CIDR?

I have a table in which I store IP prefixes. I know that Postgresql offers the CIDR type to store Network addresses however I found out that in my case using this type as a primary key and foreign key ...
0
votes
1answer
38 views

is it possible to populate a primary key of a table based on a column of another table?

I have a table that contains a text-type column(for example graph path : 12 5 7 19 30 ..) and I want to split this text by space and put all of the output strings as a primary key in the other table. ...
1
vote
1answer
29 views

Understanding linking tables in a many-to-many relationship

I am still working on a photo storage app (designing the database for the data for the photos stored), using MySQL, and have been told that a photo may have one or more people in it, and a person may ...
0
votes
1answer
42 views

MySQL id not saving in order (Laravel in production) [closed]

I'm really confused about this.. So, I created a Laravel app and hosted it on Heroku. I'm using ClearDB extension to be able to use MySQL. Problem is: when I save a new User on my DB, it is not being ...
0
votes
1answer
33 views

alter primary key and all references in sql server [closed]

I'm trying to alter a primary key field but is being referenced by about 93 tables. I used the following script to drop and create the FK constraints: https://social.technet.microsoft.com/wiki/...
1
vote
1answer
894 views

Does a primary key contain exactly one Null value…?

My work A primary key is unique and only one contains for a table. There are many unique keys that can contain in a table. In SQL Server, the primary key is not Nullable. But, a unique key may ...
1
vote
2answers
47 views

3 additional varchar(32) keys and also updating them — too bad for performance?

I have a table in mind which basicaly would look like this: table1 -------- id bigserial autoincrement primary key, token1 varchar(32) unique not null, token2 varchar(32) unique not null, ...
1
vote
2answers
188 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
33 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 ...
2
votes
2answers
118 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
203 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
72 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
43 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
49 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
45 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 ...

1
2 3 4 5
12