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

1
vote
1answer
8k views

MySQL Subquery returns more than one row

I have a table namely tbl_plays. It has the following columns and data id(PK)| gameid | drawid | etc ---------------------------------------------- 1 | 1509 | 73 | ...
1
vote
0answers
36 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 dont get it... why? Why not only {EF}.. it got less ...
1
vote
1answer
197 views

Are these custom module Drupal tables lacking foreign keys? mysqldump with table structures without data is included

I am troubleshooting a some tables in MySQL used by a custom Drupal module. Documentation for these tables is poor so I used mysqldump to get the structures in the Drupal database without the data. I ...
1
vote
0answers
80 views

Is it possible to change the data-type of a primary key column in SQL Azure? [duplicate]

Possible Duplicate: How can I alter an existing Primary Key on SQL Azure? OK, so say I have a table: 'Contact' and a primary key: 'ContactID'. The primary key is an INT but I want to change ...
1
vote
5answers
3k views

How should I save apache logs into a mysql table?

This is a php script to view apache logs. I want to save apache logs into a mysql database. Then add some rules for tagging urls using mysql REGEXP search, like: SET tag='some tag' WHERE url REGEXP '...
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
315 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
39 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
122 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
391 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
37 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
791 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
182 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
350 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
28 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
70 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
437 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
220 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
553 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
26 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
55 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
202 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
175 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
133 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
234 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
30 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
78 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
91 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
230 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
141 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
470 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
9 views

Issue with Relationships in Access Overview

I am currently working within Access 2013. I have a simple task that has been really driving me crazy these past few days, as I thought my relationships where correct. But when adding more data into ...
0
votes
0answers
31 views

Normalization of an actual paper form | STUCK

I need your help and advice for my Normalization steps of an actual paper form otherwise, I will be doing mistakes again and again, unfortunately. My class fellows are not so great to help me either. ...
0
votes
0answers
25 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 ...
0
votes
0answers
46 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 ...