Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange
Join us in building a kind, collaborative learning community via our updated Code of Conduct.

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.

1
vote
2answers
27 views

Any benefit in running ANALYZE after bulk insert in table with only PRIMARY KEY index in SQLite?

If I create a table in SQLite that has only the PRIMARY KEY index, is there any role for running ANALYZE after a bulk insert, or will that key automatically have been kept up-to-date by necessity? In ...
1
vote
0answers
40 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
1answer
33 views

Clarification on Cardinality and Participation

I'm currently redesigning a schema with 20 or so tables and my first task is to create an ERD from the existing tables/constraints/relationships. This has proven more difficult than I expected because ...
8
votes
3answers
3k views

Can CURRENT_TIMESTAMP be used as a PRIMARY KEY?

Can CURRENT_TIMESTAMP be used as a PRIMARY KEY? Is there a possibility that two or more different INSERTs, get the same CURRENT_TIMESTAMP?
2
votes
1answer
57 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 ...
0
votes
1answer
57 views

Is there a performance benefit to use tinyint instead of int for Primary Key?

Is there a performance benefit to use tinyint instead of int for Primary Key when I know that the number of rows are limited in this table but this key will be used as a foreign key in another huge ...
2
votes
2answers
45 views

Viability of surrogate key based from existing column as primary key

I'm designing the schema for a database and have a question about primary key choice. There aren't any natural keys available in the tables we're looking at now although there's interest from my team ...
1
vote
2answers
55 views

Is there a way to order a PostgreSQL table by its primary key automatically?

I want to dump the whole content of a table, ordered by its primary key. Intuitively, I would like to do a SELECT * FROM %table_name% ORDER BY %primary_key_of(table_name)%; This would be run by an ...
2
votes
0answers
39 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 ...
0
votes
1answer
41 views

Is it ever useful to SELECT DISTINCT on the primary key column(s)?

From my understanding, primary keys are unique by definition. If that's true, would one ever want to SELECT DISTINCT on the primary key? Is this ever practical/useful?
1
vote
1answer
76 views

In Postgres 10, how do the choice of SERIAL or UUID type as primary key affect replication, if at all?

A popular answer on SO states that using UUIDs as primary keys has the following benefit: Makes replication trivial (as opposed to int's, which makes it REALLY hard) I have been unable to find ...
-1
votes
2answers
88 views

How to avoid primary key violation while doing inserts

I have a code which does insert into destination table from source, but at times we are ending up with deadlocks. Due to deadlock , I have to rerun this code again. while i do re-run , I end up with ...
0
votes
1answer
33 views

M:M Table Design - Use of PK

I have a many-to-many table that contains id's from 2 related tables. For creating a primary key on this table, is it better to create another integer column and have that as a surrogate PK? Or ...
0
votes
2answers
32 views

SQL Server - Copying a key from one table to another

In a trigger, I have created a table using SELECT * INTO. I have done that in a specific way, in order to create the table without also creating the identity column that exists in it (let's name this ...
0
votes
1answer
30 views

Relations and Sets

From my understanding, One = {1,2,3,4,5...20}, and then if Two = {1}, then One won't map into Two. On that token, how come the answer is d instead of a?
0
votes
1answer
55 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
1answer
37 views

Find minimum number of columns that identify uniqely all rows

I'm using SQL Server Management Studio. I've a table and I want to find a Primary Key for this table. Can I write a script in T-SQL that finds which columns (and I want that the number of columns is ...
3
votes
3answers
440 views

Why intended integrity constraint cannot be enforced?

What does the following integrity constraint try to enforce? (integrity constraint is primary key and check in this case but I couldn't understand what I should really write). Explain why the ...
0
votes
1answer
219 views

Are multiple Primary Keys in MySQL allowed?

I haven't used SQL for ages and now I have to create a software with a small DB. I've decided to use MySQL with MySQL Workbench to help me design this database (by drawing a model with ER diagram). ...
1
vote
1answer
60 views

Moving data from one database to another database with auto incriment keys

I have table1, table2 and table3, all of them have a primary serial key id (or think of any auto increment type). table3 has two foreign keys(table1 id and table2 id). The questions is, I have 2 ...
1
vote
2answers
65 views

MySQL not using primary key on inner join

I have two tables: Table 1: CREATE TABLE `lk_transaction_types` ( `transactionTypeID` int(10) unsigned NOT NULL AUTO_INCREMENT, `isActive` tinyint(2) unsigned NOT NULL, `code` varchar(8) NOT ...
1
vote
2answers
206 views

Composite vs auto incrementing integer as Primary key

I am designing a MySQL 5.7 database for a mobile platform. This platform has activities that users can add to 'to-do' and 'completed' lists. I have 3 tables, one for activities where it has an auto ...
0
votes
2answers
51 views

Does using an auto increment of 1 on numeric PK hurts performance?

A teacher of mine said that having an numeric PK with auto increment is a bad practice because it hurts performance. He also said that Oracle database doesn't have an easy way to set up auto ...
-1
votes
1answer
482 views

Two foreign keys referring to one primary key

I'd to create a database that records transactions between two users. A user can transfer points (think of it as money) to another user. user table looks like: | userID | name | ...
0
votes
0answers
267 views

Auto increment primary key in Access / sql server application

I have an application developed with Microsoft Access and I have a table with Auto increment primary key(id).the last value is id=32767. In the add form I clicked on a new line, the id must have the ...
-4
votes
1answer
46 views

can I combine foreign key with primary key in `id` column in 1 table? [closed]

Here is the sample CREATE TABLE apple( `appleId` int(10) NOT NULL, `apple_Name` varchar(100) DEFAULT NULL, PRIMARY KEY (`appleId`) ); ALTER TABLE fruit( `fruitID` int(10) NOT NULL ...
0
votes
2answers
50 views

Index on Primary Key: is there any reason for that?

I'm auditing the schema of a MySQL database used by a off-the-shelf e-commerce application and I found this as output of SHOW CREATE TABLE thistable: CREATE TABLE `thistable` ( `case_id` int(11) ...
-1
votes
1answer
263 views

Creating a custom Id (Primary key) in SQL table [closed]

I have used this code to make the table and the script goes like this : USE [interlock2] GO /****** Object: Table [dbo].[t_interlock] Script Date: 3/13/2018 7:14:19 AM ******/ SET ANSI_NULLS ON ...
5
votes
3answers
247 views

When should a primary key be meaningful?

Excuse the looseness of any definitions here, but I am trying to explore a simple concept. A Primary Key uniquely identifies a row. There are possibly other columns in a table with unique values, so ...
3
votes
1answer
78 views

Composite Primary Key on partitioned tables, and Foreign Keys

We have a database where several tables are partitioned. The primary key (PK for brevity) for these tables is of the form transaction_date, id Where transaction_date is the partitioning key, and id ...
1
vote
1answer
62 views

INSERT INTO with PK andFK [closed]

I have the following problem: I created two tables: CREATE TABLE DimServers ( [ID_DimServer] INT NOT NULL IDENTITY(1,1) PRIMARY KEY , [ServerName] VARCHAR(13) NOT NULL, [Domain] VARCHAR(...
0
votes
2answers
32 views

Set PK as a combined string of table 1 and 2 records [closed]

Say, for example, I have 2 tables - Table 1 is Drink Type and Table 2 is Flavor. What is the query to create the unique identifier column which would result to drink_type_drink_flavor? +-------------...
1
vote
0answers
40 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....
7
votes
3answers
194 views

Choosing primary keys: Scientific names of species or system-assigned numeric identifiers?

Scientific names are well accepted unique identifiers of species. However, scientific names have special characters such as spaces and dots. Can it be a good unique identifier for databasing or should ...
0
votes
0answers
35 views

User table definition when using an third-party Identity Provider

As-Is The user table has a classic structure where everything is mixed in a single table: id BIGINT PRIMARY KEY idp_id TEXT NOT NULL UNIQUE -- user ID in the Identity Provider ...
3
votes
0answers
48 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
126 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, ...
0
votes
2answers
196 views

Make a non-ordinal numeric ID in PostgreSQL

If I declare a column to be serial in PostgreSql, it will auto-increment in a ordinal sequence. The end result may not be contigious due to rollbacks and whatnot, but it's more or less ordinal. I ...
1
vote
1answer
23 views

Unique ID for Row of a Set in One Table

I know the title is a little weird but what I actually want to do is simple. In my Postgres db a user creates a profile and uploads an image. user_id: 1, photo_id: 1 Now a second user makes a ...
0
votes
0answers
367 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
33 views

what are the impacts if I convert one of the column to primary key or add a primary key to an existing table

One of my MSSQL table don't have any primary key, I would like to add one. Just want to know: First, if I convert an existing column (or multiple columns) to a primary key, what would be the impact? ...
1
vote
1answer
39 views

How to prove “A->B,C then A->B and A->C” from Armstrong Axioms

I know that this is true but I don't understand how to prove it. The Problem: Prove or disprove that if A->B,C, then A->B and A->C This answer is similar but it does not prove the relation using ...
1
vote
1answer
53 views

# of key locks while updating

I am using SQL Server 2008, RTM. I have a simple table defined as create table t(id integer not null primary key) This table has 50,000 records, just in case. When I issue an update transaction ...
0
votes
0answers
31 views

How much overhead does doctrine2 composite primary keys add exactly?

I have seen at composite primary keys doctrine documentation that the composite primary keys add overhead. Using composite keys always comes with a performance hit compared to using entities with a ...
1
vote
1answer
79 views

Is it safe to make a Unique Index the Primary Key?

I have a vendor database that I want to use Change Data Tracking against but cannot because none of their tables have Primary Keys defined. The tables do have Unique Indexes but they are not defined ...
2
votes
1answer
642 views

Can a database table not have a primary key?

Can a database table not have a primary key? SQLite lets you define a table with no primary key therefore there can be no insertion anomaly.
1
vote
0answers
45 views

Oracle scanning table multiple times on PK creation

I am unable to find an explanation for why Oracle is scanning a table multiple times during the process of adding a primary key constraint. I have a large range-partitioned table. I created a local ...
1
vote
0answers
28 views

Why is Oracle scanning table multiple times on PK creation? [duplicate]

I am unable to find an explanation for why Oracle is scanning a table multiple times during the process of adding a primary key constraint. I have a large range-partitioned table. I created a local ...
0
votes
3answers
253 views

Composite Primary Keys

I have a small scenario here and I think using composite primary keys would be a good solution. I'm doing this in SSMS. Here is the code to create the table: CREATE TABLE [PSDesk].[dbo].[Local]( [...
3
votes
1answer
65 views

Can I change which index a primary key uses?

I have a table with a non-clustered index on the id, and a clustered index on some other fields. The fields of the clustered index are non-sequential, and are frequently modified. I'd like to change ...