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
1answer
119 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
389 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
39 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
2answers
6k views

Update datatype of primary key without deleting the table-SQL Server [closed]

I have created a table State which my other table, Addresses references. CREATE TABLE State ( StateId varchar(20) PRIMARY KEY NOT NULL, Text varchar(40) NOT NULL ) I want to change its ...
0
votes
3answers
704 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
53 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
329 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
21 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
62 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
411 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
213 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
535 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
24 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
50 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
109 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
125 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 ...
0
votes
1answer
121 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
31 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
191 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 ...
0
votes
1answer
74 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
222 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
138 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
462 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
39 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
28 views

Can I rely purely on an auto-generated unique primary key for CRUD? [on hold]

I am in the process of developing a large web-based application that uses a SQL server database for data storage. There will be multiple accounts all sharing one database for generic account related ...
0
votes
0answers
25 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
38 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
27 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
67 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
64 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 ...
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 ...
0
votes
1answer
76 views

multi valued attribute as primary key

I am designing a database system for my current project and got this situation. I am having a functional dependency as D -> E Here D is multi valued attribute. D can be(i,j,k) or (i,j) or any ...
0
votes
0answers
90 views

Finding the closure and super keys of a schema?

I am working on a problem in which I have to look at the relation R with schema R(w, x, y, z), and the three functional dependencies: w -> z ; x,y -> w ;z -> y For every non-empty subset X of ...
0
votes
0answers
36 views

Should Primary Keys be arbitrary?

I have a lookup table PerformanceID (incremental integer - Primary Key - Identity Column) PerformanceRating (Integers from 1 to 5) Description (100 char description of what the rating means) ...
0
votes
1answer
58 views

Is unique index combined with a “not null” constraint equivalent to a PK constraint?

I have these legacy table definitions (from here): CREATE TABLE [dbo].[JobItems] ( [ItemId] UNIQUEIDENTIFIER NOT NULL, -- lots of other columns CONSTRAINT [PrimaryKey_GUID_HERE] ...
0
votes
0answers
56 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 ...