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.

learn more… | top users | synonyms

0
votes
3answers
50 views

Reset Key Names to Defaults?

I'm working with some tables/columns that were renamed at some point, but the names of the primary/foreign keys are still based on the previous names. In SSMS is there any (preferably automatic) way ...
0
votes
2answers
5k views

Can a surrogate key and a primary key be in the same table?

For example we have: Surrogate key: ABC123 Primary key: 1 Name: James Is this legit in a Data Warehouse table?
0
votes
1answer
137 views

Use surrogate or natural PK if a row is uniquely identified by two or more columns?

I am having a hard time choosing between natural and surrogate PK for my database table. The database is for a ranking system for an MOBA game. Here is more or less what I would do if I were to go ...
0
votes
1answer
310 views

Maintain table with too many insert

I have to create a table. Its size is going to increase very fast. Is there going to be any issue if many insert queries are fired at the same instant as mentioned here - User autoincrement ID for ...
0
votes
1answer
38 views

Using default values in primary key

Good day. I have a table like this: CREATE TABLE mytable ( myname varchar(30), mydate date DEFAULT CURRENT_DATE, PRIMARY KEY(myname, mydate) ); EDIT: Forgot to mention I am using a ...
0
votes
1answer
2k views

Create relationship between two tables with composite primary key

I've created a table [t1] with two fields and a composite primary key, show in below: create table t1( id int, name nchar(10), primary key(id,name) ); I want to create a second table [...
0
votes
4answers
124 views

SqlServer: can one table's PK be the FK of other two table's? [duplicate]

I have three tables project, users and proj_user. The PK of proj_user table is the FK of project and users table's pk e.g.(project_id,user_id) My question is, is it possible for the project_id and ...
0
votes
1answer
120 views

Batch converting indexes to keys [closed]

I have a legacy MS SQL Server database where all the primary and foreign keys were created as indexes. I am developing the new version of our software with Entity Frameworks, which I have learned ...
0
votes
3answers
1k views

Performance of primary key IDs with gaps (but in sequential order)

I know that having non-sequential IDs is bad for index performance. But assuming all my IDs are created in correct order, but with large gaps: i.e: 154300000 283700000 351300000 464200000 ...will ...
0
votes
1answer
3k views

The four properties of a primary key

In our dbms class, these were the four properties of a primary key discussed for Oracle. Unique Not NULL Fully functional dependency Indexed I understand all the properties except the 3rd one (...
0
votes
1answer
390 views

How SQLite index ID (or line number)

SQLite is based on a flatfile, and store virtual tables in a flatfile (I think based on offset, correct me if I'm wrong). But how it index the Primary Key (ID), which I think is identical to line ...
0
votes
1answer
40 views

Decomposition In Boyce-Codd Normal Form may lost relations?

Let be the following Realtion R={UtilisateurID, Nom, Prenom, AdresseEmail, Login, Passwd, ServeurMail} with the functional dependencies: F = { UtilisateurID → Nom, Prenom UtilisateurID, ...
0
votes
2answers
36 views

Basic SQL question about setting constraints

In code such as the following, what is the need to write constraint pk_dept primary key (deptno) if just simply primary key (deptno) works? Is pk_dept something the database uses internally, since it ...
0
votes
3answers
771 views

Can i have one foreign key to both parent and it's childs in postgresql?

I want to have a foreign key to reference table and it's children. Here's how I tried and failed: CREATE TABLE parent ( id serial, CONSTRAINT parent_pk PRIMARY KEY(id) ); CREATE TABLE child ( ...
0
votes
1answer
55 views

Postgres table contains 1 column, and that column is PK. How to insert new record?

In my application, I have an abstract base class, and 2 derived classes. The number of derived classed will probably not increase. If it does, it won't be more than a few. The class structure looks ...
0
votes
2answers
181 views

Primary key for link/junction table with time-dependent data

I want to link two tables in such a way that for any given time period the relationship is one-to-one, but over time the relationship can change. As an minimal example, suppose I want to list the ...
0
votes
1answer
344 views

Implementing foreign keys on generalized data model

Suppose the ASCII tree below is a UML generalized concept model. To represent these entities/classes in UML, lets assume [mammal] a super class, and the level 1 classes, [wild] and [domestic] are sub ...
0
votes
2answers
2k views

Use timestamp(or datetime) as part of primary key (or part of clustered index)

I use following query frequently: SELECT * FROM table WHERE Timestamp > [SomeTime] AND Timestamp < [SomeOtherTime] and publish = 1 and type = 2 order by Timestamp I would like to optimize ...
0
votes
1answer
26 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 ...
0
votes
1answer
67 views

InnoDB: SELECT COUNT(*) using primary key 25 times slower on Windows

MySql 5.6, InnoDB. I have a table with 8 million records. It has an integer primary key, and a unique index u1 CHAR(40). It also has a few small fields and a VARCHAR(1000). SELECT COUNT(*) FROM ...
0
votes
1answer
36 views

How should I handle unknown primary keys? [closed]

I'm working as a database-administrator at a school. Recently the school upgraded its pupil administrative software into a web-based solution. This works great, but this web-based solution is missing ...
0
votes
3answers
425 views

Autoincrement Primary Key For Constants

We are having an issue that is probably due to some poor design choice or lack of understanding of how to use primary keys. We have a simple constants table CREATE TABLE IF NOT EXISTS `constants` ( ...
0
votes
1answer
218 views

Relational database primary key requirements

I am trying to put together an ER Diagram to design a fairly simple asset tag tracking/inventory management system for our servers/switches/UPS/etc. The top-level entity corresponds to the business ...
0
votes
1answer
28 views

Which of tables key to reference?

MySQL. There is a table users with autoincrement primary key id. There is also a table employees (every employee is a user but not vice versa). The employees primary key is userid. The field userid ...
0
votes
1answer
547 views

MySQL non-sequential primary key of certain length

what's the best way to create a non-sequential primary key of a certain length in MySQL? I'm looking to use the primary key as a 'Pin' number which real actual human people can use to refer to the ...
0
votes
1answer
25 views

Primary key which is foreign key in an relationship is specified NOT NULL. Why?

I have question about a foreign key. Primary key which is foreign key in an relationship is specified NOT NULL in some book's example. Why? I was thinking that primary key couldn't take on value "...
0
votes
1answer
52 views

Don't know why MySQL is not creating table with primary key constraint [closed]

I can't understand why MySQL showing this error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '...
0
votes
2answers
150 views

Check if a primary key value is referred in any other table

I am using oracle database. Is there a way I can check if the primary key value in one table is being used as a foreign key value (have any associations) in any other table? Example: if I have the ...
0
votes
2answers
159 views

Should I create a child table without primary key?

I'm designing a database with a child table that may contains billions of records in the future. So I try to use less fields (and smaller ones) as possible. So I'm considering not using a primary key,...
0
votes
1answer
130 views

Derived classes with same base class and class specific ids

I have three classes in my project. One base class and two derived classes. public class Animal { public long Id { get; set; } public DateTime Created { get; set; } public string Name { ...
0
votes
2answers
32 views

Primary Keys for Adress tables

I'm working on a database that stores address information. Each address has a Street, Number, Bus (as in the apartment they live in) and CityCode parameter. The CityCode parameter is a FK to the City ...
0
votes
2answers
216 views

Is it redundant to set your primary key as a unique key?

I'm putting tables together in MySQL workbench and I'm currently ticking my primary keys to also be uniquely indexed. Is this a redundant configuration?
0
votes
2answers
29 views

Can we determine FDs if we are given some tuples?

I am taking a databases course and i'm learning about functional dependencies. I have searched around and did not find any information on this. My questions is if we are given a table with some tuples,...
0
votes
1answer
77 views

Database design: Which will perform better, auto incremented column or a composite key for appointments

The schema of table is : appointment(date,time,patientID,...) pateint(patientID,PName,age,...) PS: two or more patient can came at the same time . The number of appointments per day up to 500 . ...
0
votes
2answers
1k views

Msg 2627 Violation of PRIMARY KEY constraint

trying to make a database for teams in a tv show here. but when I try and insert data into tblShowteam the following error made its appearance. Msg 2627, Level 14, State 1, Line 2 Violation of ...
0
votes
1answer
90 views

Can a complex primary key made up of 4 fields have only one of this fields set to AUTO INCREMENT?

I have a complex primary key, I wonder if what I write in the title is possible: I have a look up table with 4 fields (they should compose the primary key of the table): 1) an INT column; 2) ...
0
votes
1answer
226 views

Table design for departments with duplicate department numbers

How should I define a database table for my company's departments where we have a set of duplicate department numbers used by an upper management tier. dept_id dept_name manager_id ...
0
votes
1answer
140 views

How to arrange rows selected in MYSQL query?

I'm fetching data from a table "TABLE_A", it's having Primary Key as "PK_ONE" (which is unsigned INT) . I'm fetching data from "TABLE_A" with where clause on PK_ONE ; Ex: SELECT PK_ONE from TABLE_A ...
0
votes
1answer
468 views

Postgresql table with one integer column, sorted index, with duplicate primary key

I want to have a table in PostgreSQL with these properties: One column with an integer Allow duplicates in this column. Duplicates will be rare. Duplicates have no meaning logically and will be ...
0
votes
1answer
47 views

Modify legacy table schemas that have no primary key

I've been tasked with updating our database (SQL Server 2008) so that every table in it has a proper primary key defined. Due to various reasons over the years we have somehow built up some legacy ...
0
votes
2answers
5k views

How can primary keys in a Netezza table be indexed?

What is the best way to index a Netezza fact table with lots of primary keys. For now, let's suppose we have 10 primary keys. This table is mainly used to store meta data about our database. =====...
0
votes
0answers
45 views

MySQL - Database Design : Encrypting user id and creating a mask for further internal references

For a food order delivery system that I am working on, I want to make sure my user's private information is well protected; from system admins too. So I thought of coming up with this concept of ...
0
votes
0answers
26 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 ...
0
votes
1answer
45 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 ...
0
votes
0answers
40 views

primary and foreign keys used in object-relational table

Currently I am designing an object-relational database based on an existing relational database with multiple primary and foreign keys, and was wondering if it is OK to use primary and foreign keys in ...
0
votes
0answers
29 views

unique BIGINT as foreign key instead of primary key in distributed system

I am creating user management database schema. I am using Postgresql as database. Following is my approach. Please suggest if there is any performance issue if I use this structure. Requirement: ...
0
votes
0answers
75 views

SQL Timeout when updating specific rows that goes away when other columns are updated

The data I'm going to provide won't be very complete, so I apologize in advance for that, and will happily supplement with more on request. I mostly just don't know where to begin looking, and don't ...
0
votes
1answer
75 views

Phpmyadmin / mysql error defining primary keys as foreign keys of two separate tables

I am designing a mysql database using phpmyadmin 3.3.10.4. Everything in the database is InnoDB. I have two tables, person with primary key person.id_numberand e-mail_address with pk e-mail_address....
0
votes
0answers
37 views

When should I create a primary key on multiple columns? [duplicate]

I'm interested to know in which case I should create a primary key on multiple columns and why ? To be more specific about my question, I want to know what are the advantages and disadvantages of ...
0
votes
0answers
24 views

Structure of an SQLite database for storing analytics with counters and hits

Before making this post, I referred to this and this which contain extremely complex solutions and hence, this question: I am building a spam filtering app that takes posts from a feed and deletes ...