We’re rewarding the question askers & reputations are being recalculated! Read more.

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

Microsoft SQL database table size

I have come across a sizing issue with my database tables becoming too large. Some digging has shown that it is how MS SQL stores the information. I created two test tables and filled them with data: ...
0
votes
1answer
426 views

What are the consequences, positive or negative, to having a surrogate primary key for a table which already has a guaranteed unique column? [closed]

I recently made a table that looks like the one below CREATE TABLE example( id SERIAL PRIMARY KEY, subindusty_id INT UNIQUE NOT NULL, name TEXT NOT NULL -- other fields... ) I realized that ...
2
votes
2answers
911 views

How do I create a PRIMARY KEY/UNIQUE constraint that considers trailing space significant?

I would like to be able to store records about things that are identified by strings where space is significant. I know that T-SQL follows rules about string comparison to implicitly pad up to the ...
2
votes
1answer
1k views

MySQL Duplicate Key Entry Error when there is not already an entry for this key

Duplicate entry '35839' for key 'PRIMARY' I am receiving the above error message suddenly on a database I keep logs in. This is an auto-increment field which the last key for the last entry is 35838....
1
vote
0answers
239 views

Definition and declaration of a PRIMARY KEY for a temporal table

My teacher gave us an example bitemporal table holding rows related to medical records to test our homework on. The table has the columns that follow: first name last name parameter value unit valid ...
4
votes
1answer
161 views

How can I define and declare a good PRIMARY KEY for my dataset?

I have a large historical dataset that has the following attributes: productCode: Alphabetic code used to identify a product name, e.g., 'LE' (limited edition), 'LI' (limited inclusion) productMonth:...
3
votes
1answer
1k views

MySQL Replication query - Primary key need

I have few doubts regarding MySQL replication (Master-Slave). Is it mandatory for a table to have primary query for replication to function properly? Referring this Percona link for the above ...
2
votes
1answer
821 views

Advantages/Disadvantages of using a multicolumn clustering index

What are the advantage/ disadvantages of using a primary key that is a combination of multiple columns? I have come across a table that has a unique non clustered index as a GUID but the clustering ...
0
votes
0answers
22 views

Insert new row with possible minimum PRIMARY ID key

For example, I have table like this: ID | name =============== 1 | BMW 4 | MERCEDES 12 | FERRARI for example, i add a new row, and i think that ID will be 17, right? ...
0
votes
2answers
35 views

Does it matter if id (in TABLE ) starts from 0 or from 10000

Sorry for my incorrect terminology (as I don't know what to call that).. does it matter (with regard to performance) if unique (index) key starts from 0 and from i.e. 100000 ? For example, if I have ...
1
vote
0answers
114 views

Performance of Long Primary Keys

There are plenty of discussions on the benefits and drawbacks of using hashes as primary keys, but in my case I have a database of webpages that I need to look up by address, and these addresses are ...
0
votes
1answer
3k views

Creating a Primary Key on Existing table

We have a partition table with 2 billion records and we need to assign a primary key to one of the columns for enabling change tracking feature on it. After searching we found out that primary key ...
2
votes
1answer
856 views

How to model a three-way association that involves Product, Category and Label?

I have the following three tables: products: product_id, product_name, ... categories: category_id, category_name, category_parent_id, category_priority (for sort ...
2
votes
2answers
260 views

Can a Key Preserved Table have a Composite Primary Key, on Oracle db?

I'm trying to create an updatable view. I understand that the db must be able to identify one single row that I'm trying to update. I believe I satisfied this requirement. There are no aggregates. ...
2
votes
1answer
4k views

How to store multiple foreign key values in a single field?

I have following two tables in an MS SQL-Server Database: user_info ( user_id PRIMARY KEY, email, password ) and messages ( time_stamp, message, sender, recipients, ...
3
votes
3answers
2k views

Difference between a unique clustered index and a clustered primary key?

I'd like to ask two supplementary/follow-on questions, further to this previous/existing question: Is there any tangible difference between a unique clustered index and a clustered primary key? That ...
1
vote
0answers
20 views

InnoDB: in what scenarios, if any, is it okay to have random insert WRT primary key?

For now let's ignore the benefits of having a surrogate key for business logic reasons. Let's only consider performance (speed and disk usage). CREATE TABLE `person` ( `ssn` bigint(20) unsigned NOT ...
1
vote
0answers
45 views

What type of primary key we need to use for specific length of bits in Cassandra?

I want to know in which situations which type of primary key I need to use. Situations: 1. If I need 64 bit key than is bigint is the best for it? If I will chose text type will performance will be ...
0
votes
1answer
65 views

generate unique primary key for all clients from the local database and NOT the server

I'm using MySQL Innodb database to work as a distributed database among many places and also each place will work offline but at the end all the data will be centralized in one server, I want to ...
2
votes
1answer
69 views

Is it possible to use part of a composite primary key as a foreign key to another table?

In my attempt to model a one to many relationship, I am trying to achieve the following in SQL (MariaDB) given my basic DB knowledge: Profiles Table: Primary Key: profileName <----- ...
0
votes
2answers
91 views

Need to generate PK value programatically in multi-threaded application

Suppose We create a table, with identity disabled: CREATE TABLE TestIdentity ( Id int NOT NULL, abc varchar(10) NULL, CONSTRAINT PK_ident_test PRIMARY KEY CLUSTERED (id) ); Note : I ...
1
vote
1answer
700 views

Is this proper way to link parent and child tables with logical data instead of parent and child using primary and foreign key

I have two tables one is product which is a parent table with one primary key and i do have another child table of product, which is a product_details table. But the child table is linking with parent ...
2
votes
3answers
728 views

should I include the primary key in the clustered index in order to make it unique?

I have the following table: IF OBJECT_ID('[dbo].[ShipTaxAddress]') IS NOT NULL DROP TABLE [dbo].[ShipTaxAddress] GO CREATE TABLE [dbo].[ShipTaxAddress] ( [TaxRegionAddressId] INT ...
0
votes
1answer
534 views

Performance issue with composite primary key or not on Transactional Replication with AlwaysOn Subscribers? [closed]

I configure identity column with composite primary key on Transactional Replication scenario to maintain unique row value. CREATE TABLE [dbo].[AD_CLIENT_DETAIL_MST]( [CLIENT_ID] [bigint] ...
1
vote
2answers
294 views

What are the Pros and Cons of assigning value for ids from an offset? (Ex: 100,200,1000)

Pre-dominantly, everybody uses the int data-type or it's variants tinyint,smallint and mediumint to generate a unique value for the id field of their database table(s). I have seen so many questions, ...
3
votes
2answers
126 views

Using floats for PKs. Is it a good idea?

Let's say I have an entity PizzaStore which has a location as part of its attributes Now a PizzaStore can have N of other PizzaStore that are close by. So seems to be a self referencing relationship 1-...
1
vote
1answer
122 views

Mysql auto insert record in primary table if not exist

Is it possible to auto insert a record into the primary table if the record does not exist when adding a foreign key? For example, assume these tables: - user(id, name, age) - topic(id, name) - ...
1
vote
1answer
1k views

Primary Index VS Unique Index Performance

I have the following table: CREATE TABLE `actions` ( `user_id_from` int(11) unsigned NOT NULL, `user_id_to` int(11) unsigned NOT NULL, `action_type` int(11) NOT NULL, `counter` smallint(11) ...
2
votes
0answers
69 views

How should I select a primary key in my use case

I have an app wherein I want to give offers to certain devices which fall in range of IMEI numbers (I have a separate algo to calculate this range). So I want to create a structure in which I have a ...
3
votes
1answer
2k views

Ordering of table is based on Clustered index or non clustered primary key?

I have created a table with primary key on name column, the ordering of the table is based on name column create table TestPrimaryKey (Name varchar(20) primary key,id int identity) insert into ...
14
votes
2answers
5k views

What are the best practices regarding lookup tables in relational databases?

Lookup tables (or code tables, as some people call them) are usually a collection of the possible values that can be given for a certain column. For example, suppose we have a lookup table called ...
0
votes
0answers
83 views

Cannot delete Postgresql constraints that were former unique keys

I have modified unique constraints. I am adding a field to every unique constraint. To do that, I need to drop the existing unique constraints and create the new ones. I am trying to delete foreign ...
3
votes
1answer
83 views

Copy primary keys/clustered indexes from Prod to DEv/Test env

We followed the best practices of creating Primary Keys/Clustered indexes on tables which are actively queried on prod. Now there is a scenario where we need to get the similar above changes done ...
5
votes
1answer
647 views

How to manage primary key value while multiple secondary server sync with primary server?

we have three secondary database server in different cities and one primary server. my question is i want asynchronous transaction commit to all three database but primary key data conflict occur ...
1
vote
1answer
389 views

MariaDB / mYSQL Order BY PrimaryKey DESC vs DESC set by Default

What do you guys think about: After each query, I put "Order BY PrimaryKey DESC" VS I set "PrimaryKey Sort by DESC Default" and each query I don't need to put Order By there. Which one will be ...
1
vote
1answer
905 views

SQL Server query problem when selecting data from child table based on column in parent table

I am using MS SQL Server 2008 using Management Studio. I am running a query(below) to extract dates and value based on the 'ticker' column from the parent table, the attribute column (child) , and ...
4
votes
5answers
1k views

How can I allow part of a key to be null and enforce uniqueness of records with nulls?

I have an Access table called Engagement with four fields: Emp_id, Year, Week, Act_id It records when an employee is/was engaged in an activity. Each field is Number and each makes up part of the ...
0
votes
3answers
77 views

Clustered Index and PK - Two different Objects?

I have a table which has a Clustered PK as follows: CREATE TABLE [dbo].[PanelCcmRawData] ADD CONSTRAINT [PK_CcmRawData] PRIMARY KEY CLUSTERED ( [CcmRawDataId] ASC, [LineageId] ASC ) I ...
18
votes
8answers
8k views

Why would a primary key value change?

I have been researching the concept of ROWGUID's recently and came across this question. This answer gave insight, but has led me down a different rabbit hole with the mention of changing the primary ...
1
vote
1answer
451 views

Converting a composite primary key to composite unique, and adding an auto-incremented PK column

Background I have taken over responsibility for a table that, in the long run, will grow monolithic. The table is a very simple link table with 2 columns, call them col1 and col2, both being foreign ...
9
votes
3answers
1k views

Should I add an auto-increment / IDENTITY field to a cross-reference table just for PK purposes?

I'm adding the following cross-reference table to my SQL Server-hosted DB: company_id bigint not null (FK) org_path nvarchar (2048) not null The company_id field refers to the id field in another ...
0
votes
0answers
150 views

mysql duplicate key error during restoration through mysqldump data

I have a mysql database which is having one table for geo location and saves latitude and longitude values of users. The fields are as followed CREATE TABLE `USER_LOCATION` ( `LATITUDE` float NOT ...
5
votes
1answer
3k views

Oracle SQL 12c How to correctly use compound foreign keys in compound primary keys?

DISCLAIMER: I am a student. I do not work for an airline. I do not pretend to be a professional programmer. Greetings, I want understand what I need to correct in order for these tables to work ...
2
votes
1answer
4k views

Two tables with the same primary keys

Can two tables have the same primary keys? For instance:
1
vote
1answer
268 views

Decomposition In Boyce-Codd Normal Form may lose relations?

Let be the following Realtion R={UtilisateurID, Nom, Prenom, AdresseEmail, Login, Passwd, ServeurMail} with the functional dependencies: F = { UtilisateurID → Nom, Prenom UtilisateurID, ...
1
vote
1answer
76 views

InnoDB: secondary key that's 'parallel' to primary--do joins wherein the resulting primary keys are contiguous perform better?

Say I have a table ( person_id INT AUTO_INCREMENT, birthday_timestamp BIGINT, country, state, ..., PRIMARY KEY (person_id), KEY (birthday_timestamp) ) where people are added the instant they'...
1
vote
2answers
480 views

Foreign key as part of composite primary key or not?

I'm designing a relational database. This is what I'm trying to design: I will have production orders and products. A production order will have one product, but a product could be on zero or more ...
3
votes
1answer
2k views

Automatic index creation for primary vs. foreign keys in Postgresql

For PostgreSQL 9.4 (http://www.postgresql.org/docs/9.4/static/ddl-constraints.html) it is stated that A foreign key must reference columns that either are a primary key or form a unique constraint....
1
vote
0answers
55 views

Varchar PrimaryKey. Clustered or nonclustered? [duplicate]

I am working on a legacy database. The primary key is clustered varchar(128) and the table contains a high volume of data. Does it necessarily help performance if I change the clustered PK to ...
0
votes
1answer
4k views

Difference between surrogate key(s) and composite key(s)?

I'm a beginner with databases and have been perplexed by the various definitions to distinguish these two. Various google searches have not fully clarified their differences. Please help explain them ...