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

2
votes
1answer
54 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
8k views

Linking Fields From 2 Different Tables (Microsoft Access)

I am trying to link 2 fields from 2 different tables on Access but I will give an example to try and make the issue clearer. I have 1 table called "Employee Details" which contains the field ...
2
votes
0answers
112 views

Storing URLs in a database

I am thinking about storing URL's in my database, but I am not sure whether the way I use is an efficient way to do things or not. Table name : url url_id (Primary Key & Auto Increment) url_value ...
2
votes
1answer
929 views

unique constraint violated (ORA-00001) in concurrent insert [closed]

I have a procedure that is called from concurrent transactions: //Some actions here INSERT INTO table1 (table1_id, table1_val1, table1_val2, table1_val3) VALUES ...
2
votes
2answers
582 views

Replacing composite key with surrogate

I have the following table that has a couple of million rows in it and is 99% fragmented virtually all of the time. My plan was to insert a IDENTITY field as a surrogate key to replace the current ...
2
votes
0answers
467 views

Why are runtimes different for a table including a primary key versus a table with a clustered unique index added after population [closed]

When I create a temporary table with a PK defined from the outset like this I get great performance in subsequent joins on that key: create table #temp ( field1 int not null field2 int ...
1
vote
2answers
5k views

Is it recommended to use Identity as an alternative to primary key?

We can declare an Identity like id_num so that id_num will have an increment of unique numbers. CREATE TABLE new_employees ( id_num int IDENTITY(1,1), fname varchar (20), minit char(1), lname ...
1
vote
2answers
167 views

How do I create a rule for a Primary Key

I am using SQL Server 2005 and I need to create a primary key with an alpha character and 3 numbers together: p001 b201 I know I need to create a rule and bind that rule to the primary ...
1
vote
2answers
2k views

VARCHAR primary key - MySQL

Currently, I have a categories table with 2 columns - category VARCHAR(50) NOT NULL PRIMARY KEY and parent VARCHAR(50). The parent column is a foreign key (FK) to the category column. This seems like ...
1
vote
3answers
1k views

Normalization: 'zip code' as a primary key

I've seen frequently that you should not have all address fields (zip, street, city, state, country, etc) in the same table as your user info table (pk_id, name, email... ). This would be in ...
1
vote
3answers
7k views

What could cause an auto increment primary key to skip numbers? [duplicate]

I have a table created with the following T-SQL: CREATE TABLE [dbo].[TableName]( [id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, ...
1
vote
1answer
157 views

Is it require to fill all tables inside of the database?

Now,I created upper that table and in order to test I will give 3 textboxes values as below. SqlCommand cmd = new SqlCommand ("Insert Into dbo.PrnInf(PrnName, PrnSurName, PrnEgn ) Values ...
1
vote
3answers
787 views

does the mere existence of a primary key increase query speed for large data sets?

So, I have a table with about 2.4M rows and ~30 columns. There is no unique column within the data. The data is transaction logs from stores where each transaction number is only unique on a per store ...
1
vote
2answers
215 views

What and How is the ID antipattern?

What is the ID antipattern and why / under which circumstances is it considered an antipattern? The id antipattern is having a unique ID column in each table. The ID is generated for each new ...
1
vote
1answer
116 views

Is it ok to create additional column for helping other column to be a PK?

Let's say a Person can have more than one nickname. He(she) must enter his(her) nicknames alongside with other details: Here is the table columns: User_Id, Nickame, Age Of course, for now there ...
1
vote
1answer
117 views

Shorthand for on primary key?

Is there a shorthand for merging rows based on the primary key? For example: MERGE INTO [MyDatabase].[MySchema].[MyTable] AS target USING [MyLinkedServer].[MyDatabase].[MySchema].[MyTable] AS source ...
1
vote
2answers
100 views

What Columns to create Indexes on?

I have a table , which contains upto 20 columns. The data is selected, inserted, and deleted by 3 columns: Username, StartDate, and EndDate. These 3 columns are NOT unique. The table is a ...
1
vote
1answer
83 views

When do you add your foreign keys?

I'm scripting out a fairly simple database so I can simply hand off a script to someone else for an application to use. I'm curious about where best practice/the community stand on this: When should ...
1
vote
1answer
2k views

Changing primary key from varchar to int

I have a user table where I am use varchar as primary key. Now from a different place I got the performance tip that int is better than varchar for primary key. So I would like to change my primary ...
1
vote
1answer
211 views

Multiple primary keys

I am new to database design and have been looking at some example data models here. One thing I don't understand is if you have a table like client_addresses from the link above: ...
1
vote
1answer
39 views

Need advice on Primary and Clustering Keys of a table

I need advise on a table which is part of a development project I'm involved. A brief summary on the use of the table: Data from multiple databases will be consolidated in this table for the purpose ...
1
vote
3answers
462 views

Copy table with separate sequences for serial columns

While creating a column management tool I came across the need to quickly replicate tables in PostgreSQL so I would not test new tools with non-test tables. To effectively test the new column tool ...
1
vote
1answer
241 views

How to include in the result of a `SELECT … GROUP BY …` all the other columns that are functionally dependent on the grouping ones?

I'll base this question on a toy example. Let this be table A: A ------------------------- U | V | W | X | Y | Z ------------------------- a | b | c | 1 | 6 | 8.3 a | b | c | 1 | 4 | ...
1
vote
1answer
3k views

Auto Increment Primary ID inserting random values

Today I was asked to look into this problem and I have never seen anything like this before. A table in our MySQL database, with a primary IDs column, which is set to autoincrement int, had some weird ...
1
vote
1answer
3k views

Candidate key = Key = Minimal Superkey?

I got a little confused by all these keys. As I understand A key of a relational schema = The minimal subset of a superkey that is still a key = A candidate key Is this correct or not?
1
vote
2answers
998 views

serial field issue

Q: I use a serial as a datatype for my primary keys in my tables. I insert a lot of data then truncate these data one after one many times to test the data entry. My question is: Is there any ...
1
vote
1answer
33 views

Chance of duplicate on single select max into statement

Against my will, I am told to use the following method to generate a new pk value. INSERT INTO SERVER_MONITERING(SM_SEQ, CPU_USE, MEMORY_BASE, MEMORY_USE, DISK_BASE, DISK_USE, ...
1
vote
3answers
75 views

Is it a good idea to use a personal number as a primary key for a customer table?

We are designing a new system to handle customers at our firm and me and another developer are split on wether we should use the customers personal numbers as a primary key. My preference would be to ...
1
vote
1answer
83 views

PostgreSQL Primary key disappears from test table

I have a somewhat convoluted scenario where a test table I had created with a primary key won't return the primary key. pgAdmin III reports there are no constraints. I have the entire PostgreSQL query ...
1
vote
1answer
92 views

“Transitive” keys

I have a central table, A, which will have a key that I'll call A_ID. Now, I have a table B in a "one to many" relationship with A (many B's map to one A). Each link will, of course, have a link ...
1
vote
2answers
421 views

Relationship with a table without unique/primary key

I have a database with 3 tables. Table Work, with a field named workName. Table SubWorks, which have a field subWorkName and work with a relationship with Work.workName. And table Activity with ...
1
vote
1answer
729 views

How do I validate all my constraints in postgresql?

It seems like I have been bitten by the first bug described in the release notes of postgresql 9.3.4: http://www.postgresql.org/docs/devel/static/release-9-3-4.html I now have e.g. duplicate primary ...
1
vote
2answers
242 views

Column suitable for natural key?

I have read many articles now about natural vs surrogate primary keys, and came to the conclusion there is no single best practice. I have a table that will have around 2000 definite unique values, ...
1
vote
1answer
354 views

MySQL, temporary on disk table on primary key retrieval?

MySQL 5.5.24, Windows 7 64-bit. I've InnoDB table with primary key defined on three columns: CREATE TABLE `common_descriptions` ( `object_id` mediumint(8) unsigned NOT NULL DEFAULT '0', ...
1
vote
1answer
372 views

MySQL auto_increment delivers different primary key in MyISAM and InnoDB

I have a table with auto incremental primary key. If I want to change a row and add new rows I got different primary keys depending on engine MyISAM and InnoDB. The original table is: pk.. ...
1
vote
1answer
376 views

MySQL looking up more rows than needed (indexing issue)

In our MySQL 5.5 database, we have following InnoDB table with 30M+ rows: +----------------+-------------+-----------------+--------+ | participant_id | question_id | given_answer_id | status | ...
1
vote
1answer
306 views

Is there a way to extract a report of or script all of the primary keys in a database?

I have just discovered that deployed copies of one of our databases have no primary keys. This is probably the fault of the SSMS Import/Export wizard, which has done the same to me. Our master copy of ...
1
vote
1answer
878 views

How to alter primary key constraint for 2 columns in same table in SQL Server?

In SQL Server, I already created a table called tblDatabase with some columns. I want to add a primary key on the two columns DatabaseName and Servername. I tried to add: ALTER TABLE tblDatabase ...
1
vote
1answer
2k views

Does the order of keys in compound Primary key make any difference?

Package Package ------- PackageID PackageName CityID PRIMARY KEY (PackageID) UNIQUE KEY (PackageName) FOREIGN KEY (CityID) REFERENCES City (CityID) Package versions: PackageVersion ...
1
vote
1answer
2k views

updating mysql primary keys

I need to be able to swap values that are used in a primary key. Meaning, in one update statement, I need to change id 1 to id 2, and id 2 to id 1. In other relational databases, (Sql Server and ...
1
vote
1answer
22 views

Find insert/update/delete queries that reference a partitioned view

I have just given a SQL Server with performance problems. The database is very large and use distributed views with a very large primary clustered key. (24 bytes). I would like to change this primary ...
1
vote
1answer
30 views

Trying to understand how an auto increment primary key is better than no primary key and some other primary key questions

I am trying to understand primary keys better, how to use them effectively in table design and in queries. First, are the primary keys themselves used in WHERE clauses? For example, if I have a table ...
1
vote
1answer
31 views

What is the best approach to find all fields that has (Foreign Key and Primary Key) constraints in a Mysql database?

I have a mysql database that has 250 tables. I would like to run a query to retrieve: tableName ; FieldName ; ConstaintName (Primary or foreign key). I could use ...
1
vote
1answer
435 views

Rename the constraint of a possibly referenced primary key in PostgreSQL

Is it safe to use this command just like that, even though the table might be referenced from other tables as foreign key? ALTER INDEX foo_pkey RENAME TO pk__foo__id; I have just tried it and it ...
1
vote
3answers
102 views

Best practice - Update Primary Keys

What's you opinion about Update a primary Key in SQL? I have a "link" table with 3 key from 3 other tables. Should the administration let people to edit one of the keys? I usely said no, dont change ...
1
vote
1answer
514 views

Non-sequential Primary Key performance in PostgreSQL 9.3

Due to requirement to support distributed environment, each of my clients has been assigned unique node id, it appends the node id with running number as primary key and stores at local database. E.g. ...
1
vote
1answer
279 views

How to insert values into a junction table?

I add values into my two tables using the code below, but I also have a junction table called book_main_cover and I want to add data into this table to make a connection between book_id and ...
1
vote
1answer
708 views

How can one drop all default values and allow NULL for all columns of a table?

I'm working on a PHP script to modify the primary key (PK) of a table. My current code is: $q = "SHOW KEYS FROM $db.$table WHERE Key_name='PRIMARY';"; $keys = Query ( $q); if ( $keys) { $q = ...
1
vote
2answers
82 views

compound primary key with separate id field for reference

I am trying to build locations into a database like so: Country -> Region -> Town Countries will be populated with a list of pre-defined worldwide countries, regions & towns are input by users. ...
1
vote
1answer
53 views

one to one relationship

This is my database structure: Users {user_id, username} Profiles {profile_id, user_id, user_views} My question is: If I do it the way mentioned above, should I to set user_id as primary key or ...