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.

67 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
4
votes
1answer
773 views

Is there a strategy to use timestamp as primary key?

The majority of times we use integer sequences for primary keys to maintain uniqueness of row in a table. Couldn't we use timestamps instead of integers for this purpose? Is there any flaw in that ...
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 ...
3
votes
0answers
498 views

Clustered index on a join table

I have a table defined as follows, but I'm not sure how to cluster the table: CREATE TABLE [Security].[UserGroups] ( [UserId] INT NOT NULL, [GroupId] INT NOT NULL, [IsAdmin] BIT NOT NULL, ...
3
votes
0answers
204 views

Superkeys of this relation

I am trying to find the superkeys of this relation, but I am having troubles finding out how many superkeys there are and exactly what they are. I figured out that the candidate keys were {A},{B},{C},{...
3
votes
1answer
541 views

Sybase ASA: Saving and dropping all foreign keys and most Primary Keys for (Bulk) Insert

Background Information: I am trying to migrate from an ASA 12 Database, to a ASA 12 Database by using a Remote Server. I have to migrate a lot of Rows (not all), spread over a lot of tables (not all)....
2
votes
0answers
46 views

Design for a child to have a single parent `A` OR `B` with FK cascade constraint?

I'm assuming this is not possible with PK and FK... Let's say I have tables A, B, and C. A and B are "parents", and B and C are "children" A is the root B can be the child of either A or B C ...
2
votes
0answers
74 views

How to design normalized relations to store “global/generic” data?

Context I was designing a relational database model to store data about a company and its production facilities. I had a facility relation and many other relations referencing it, most of the time ...
2
votes
0answers
237 views

Postgresql - Primary key index getting corrupted after upgrade from 9.2 to 9.6

Im using a Postgresql-9.2 on Centos 6.7. I tried to upgrade the PostgreSQL using --link. After that, I took a table backup and tried to restore this on Dev server. But Im getting an error like, ...
2
votes
1answer
70 views

Pull data from one DB Server to another DB server

I want to pull Few tables (33 Tables) from one DB server A to another DB server B. I have Schema ready with few datas in DB Server B but when I try to pull data from DB Server A to DB Server B, ...
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 ...
2
votes
1answer
103 views

Primary key addition for a table which contatins no data is too slow

We are using mysql. We are running 10 alter queries which contains 3 Primary key deletion and 4 column addition and 3 primary key addition. And this alter query is on three tables which don't contain ...
2
votes
1answer
75 views

Keys of a relation

I got relation: R = { (ABCDEF) , (A B F -> C, B C -> F, F -> A, F -> B, D E -> E, E -> D)} keys of this relation are: {BCE}, {EF} But I don't get it... why? Why not only {EF}.. it got less ...
1
vote
0answers
37 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
21 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
1answer
46 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 ...
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 ...
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
0answers
106 views

Choosing the correct shard key for MongoDB

I'm new to MongoDB. From the mongod documentation, I know that when choosing shard keys the key should always have high cardinality, so write operations are distributed to different shards (a kind of ...
1
vote
0answers
35 views

How to optimize this Postgresql query

Table A has columns (P1, P2, Value1) where P1 and P2 are a multi column primary key. Table B has columns (P3, P1, Q1) where P3 is the primary key. Table C has columns (P2, Q1, Value2) where P2 and Q1 ...
1
vote
0answers
26 views

Ideal and Efficient Way of Handling User Logins with Multiple Types (i.e. Employee, Client, etc.)?

I'm developing a database where I will have two types of users: Employee and Client. I'm thinking it would be ideal to just have one big USERS TABLE with a user_ID as PK and Type column to ...
1
vote
0answers
59 views

Associate multiple tables with a single table

I have a RDBMS database and I need to associate a column in a new table to multiple other existing tables whose schema can't be changed. The new table looks something like this, UUID(pk) PartId(pk)...
1
vote
0answers
47 views

Foreign key constraint across tables

I have tables A, B and C where there is a one-to-many relationship between B and A and B and C respectively. I need a constraint that for every row of C rowC.propertyC is element of SELECT A....
1
vote
0answers
130 views

PostgreSQL problems after vacuum - multiple rows with same pkey, different counts of those rows from seq scan vs index

We tried to run a full vacuum on our Postgres database the other day because of disk problems. A bunch of things went wrong along the way and eventually we stopped the vacuum. Now we have some new ...
1
vote
1answer
43 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 ...
1
vote
0answers
240 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 ...
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 ...
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 ...
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
390 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
0answers
60 views

General Definition of/for Primary Key for Table with Repeated Entries (i.e., not in 1NF)

my first post, my apologies if this does not belong here, please refer me to the/a right site. So I am looking for a general form for a primary key given a table containing non-atomic repeated ...
1
vote
0answers
360 views

Can I use a Composite Key as criteria for selection in a query?

I have created a meeting minutes table and would like to use two fields as a unique ID for each meeting (field one is a meeting code (lookup from another table) for each type of meeting and field two ...
1
vote
0answers
1k views

Is there a way of implementing a composite primary key with varying fields in MongoDB?

Here is the information about my development environment: -Mongodb 3.0.0 -Microsoft Visual Studio Professional 2013 -.NET Framework 4.5 We are trying to design some MongoDB collections at work ...
1
vote
0answers
413 views

PostgreSQL 9.3: Minimize locking when updating a large table with primary key

I am building a DB solution to handle a fast stream of online events. Each event UPSERTs a row in a location_update table, using its car_id primary key. Since there are very few new car_ids, the ...
1
vote
0answers
101 views

How to pass the value of PK to FK

Ok, so here it goes. I have a table called employee. then I have another table called languages. I need to copy the value of PK of employee table to foreign key in Languages table, rather than ...
1
vote
0answers
53 views

Speeding Up Query - Hangs at Sending Data - Indexed Correctly

I am looking for ways to speed up the below query. I currently have indices on FIRST, LAST, UNIT_PARENT, HomeAddressLine1, HomeAddressLine2, HomeCityNAme, HomeStateAbbreviation, HomeZipCode, HomeZip4, ...
1
vote
0answers
154 views

Table with both primary key and unique key

My system requirement defines a unique business/domain/natural key (i.e. <yyyymmdd><branch_id><running_number>, in bigint). Should I use this unique key as primary key or I should ...
1
vote
0answers
2k views

Using partitioned table, and dealing with auto_increment PK

I have a big table (>100 million rows) that tracks user's activities. It has an ID, which is PK and has auto_increment property. This is the only unique key. Table also has userid and datetime column. ...
1
vote
2answers
3k views

Crow's Foot notation and identifying -vs- non-identifying relationships

Is it possible to distinguish between identifying and non-identifying relationships using the Crow's Foot notation? Case in point: In table Book, part of the book's Primary Key is a FK to the Author ...
0
votes
1answer
16 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 ...
0
votes
1answer
29 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
41 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
1answer
137 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
1answer
61 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
30 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
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. ...
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
378 views

ORA-02270: Error report: no matching unique or primary key for this column list

I have created two tables: CREATE TABLE team ( teamname VARCHAR2(30) NOT NULL, carndate DATE NOT NULL, teamnomembers NUMBER(2) NOT NULL, charname VARCHAR2(30), ...
0
votes
0answers
955 views

Postgresql Restore - duplicate key value violates unique constraint

I have a PostgreSQL 9.2 server and want to restore this to PostgreSQL 9.6. Here is the table. create table mytable ( ID int, EID int, Name varchar(100), Address varchar(300), primary key (ID, EID) )...
0
votes
1answer
74 views

SQL Server - moved index behind PK constraint to different filegroup, but space not freed

I have a large table Foo. There are 4 filegroups (FG1, FG2, FG3, FG4) FG2 is on a disk that is nearly full. It has 2 indexes: a) clustered index (on Date) b) a unique non-clustered index (on ID) ...