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
1answer
45 views

Ensure same tenant in a Many to Many relationship

I am trying to draft a very simple data model for a multi-tenant database. So far I have only 3 tables: company: pretty much my tenant account: is part of one company and zero or more groups ...
2
votes
1answer
2k views

partitioning a table on a column that does not belong to the current clustered index

I am partitioning the following table, 300 million rows, data from 2011 until today (until 24-mar-2017 in the test system) I want to partition my table over a column called OrderDateSID int that has ...
2
votes
3answers
4k views

How to change VARCHAR length in PRIMARY KEY in SQL Server 2014

I need to change the VARCHAR length from 20 to 25 in a code column in a library table in SQL Server 2014, but SSMS wants to DROP and RE-CREATE all my tables where the library table in question is ...
3
votes
1answer
501 views

How do Databases Ensure Uniqueness of Primary Keys, Particularly UUID Keys?

First off, I am assuming that it is possible for a database to generate but not insert a duplicate UUID value for a column that has a unique constraint. Is there any intelligent way that databases ...
2
votes
1answer
1k views

Duplicate key value error message on a database for a writing groups scenario

I am trying to create a database as portrayed in the following diagram: This is the SQL-DDL code I have written: CREATE TABLE Publishers ( PublisherName VARCHAR(50) NOT NULL, ...
4
votes
1answer
1k views

Inserting in PostgreSQL Foreign Table Violates Primary Key Constraint10

I have two local databases in one of them I have created a foreign table that is referencing a table in the second database. When I want to insert in foreign table it inserts the primary key all over ...
0
votes
1answer
209 views

Search all table, find PrimaryKeys with Id = int/bigint and enable Identity, autoincrement

I have a large list of tables and due to change in middleware platform, the middleware is no longer creating the auto-increment on Add New Record at the app layer, as a result, I need to do this in ...
0
votes
2answers
623 views

Unable to add primary key to innodb table without one

I've inherited myself a fun table with some peculiar indexes (and no primary key) on it, that looks as follows: CREATE TABLE `my_wonky_table` ( `id` bigint(20) unsigned NOT NULL, `login` varchar(...
0
votes
2answers
794 views

Two tables, one foreign key, and two primary keys

Say there are two tables: Student: StudentID(Primary Key) Name StudentTeams: StudentID(Primary Key, Foreign Key) TeamName StudentID in StudentTeams table references Student table primary key. What ...
7
votes
4answers
1k views

How to enforce uniqueness of compound primary key made up of two foreign keys to the same table?

I have two tables: a user table and a friendship table. Say the friendship table looks like this: friendship ------------ user_one_id user_two_id other_fields I need to enforce the uniqueness of the ...
2
votes
2answers
2k 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
463 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 ...
3
votes
2answers
997 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
262 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
162 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 ...
3
votes
1answer
891 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
36 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
127 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
968 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
280 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
67 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
73 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
740 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
778 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
580 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
331 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
124 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
73 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
6k 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
87 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
84 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
717 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
439 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
991 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
9k 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
500 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 ...

1 2 3 4 5 11