primary-key

Best Primary Key for a friendship table.

I have read a number of solutions for a mysql facebook friendship table and have decided on a fairly simple table with two fields user_a and user_b. I would then using a query with a UNION to get a list of all of a users friends (as they could be in user_a or user_b). My question now is... is it better to have a auto incrementing unique ...

Removal of table primary key in MySQL

Hello, I've removed the primary key of one table of my MySQL database, but now, when I use the MySQL Administrator and try to edit some data of this table, it doesn't allow me to do this. The button edit that appears in the bottom of the table keeps visible, but disabled to click. ...

Fastest way to modify a decimal-keyed table in MySQL?

I am dealing with a MySQL table here that is keyed in a somewhat unfortunate way. Instead of using an auto increment table as a key, it uses a column of decimals to preserve order (presumably so its not too difficult to insert new rows while preserving a primary key and order). Before I go through and redo this table to something more s...

How to export primary keys on data-dump?

Hello, When I export my database with doctrine:data-dump, I encounter 2 problems: * the primary keys are not exported * instead of foreign keys columns correct name, it uses the name of the foreign table. For example, here are my tables: # schema.yml Planet: connection: doctrine tableName: planet columns: planet_id: ty...

Future proof Primary Key design in postgresql

I've always used either auto_generated or Sequences in the past for my primary keys. With the current system I'm working on there is the possibility of having to eventually partition the data which has never been a requirement in the past. Knowing that I may need to partition the data in the future, is there any advantage of using UUID...

sql swap primary key values

Hi, is it possible to swap primary key values between two datasets? If so, how would one do that? ...

Using Spring's KeyHolder with programmatically-generated primary keys

Hello, I am using Spring's NamedParameterJdbcTemplate to perform an insert into a table. The table uses a NEXTVAL on a sequence to obtain the primary key. I then want this generated ID to be passed back to me. I am using Spring's KeyHolder implementation like this: KeyHolder key = new GeneratedKeyHolder(); jdbcTemplate.update(Constan...

Hibernate/JPA and PostgreSQL - Primary Key?

I'm trying to implement some basic entities using Hibernate/JPA. Initially the code was deployed on MySQL and was working fine. Now, I'm porting it over to use PostgreSQL. In MySQL, my entity class defines its primary key as an auto-incrementing long value with the following syntax: @Id @GeneratedValue(strategy = GenerationTy...

SQL Server Composite Primary Keys

I am attempting to replace all records for a give day in a certain table. The table has a composite primary key comprised of 7 fields. One such field is date. I have deleted all records which have a date value of 2/8/2010. When I try to then insert records into the table for 2/8/2010, I get a primary key violation. The records I am att...

SQL Server: Primary Key Schema Largely Guid but Sometimes Integer Types...

OK, this may be a silly question but... I have inherited a project and am tasked with going over the primary key relationships. The project largely uses Guids. I say "largely" because there are examples where tables use integral types to reflect enumerations. For example, dbo.MessageFolder has MessageFolderId of type int to reflect ...

MySQL primary/foreign key size?

I seem to see a lot of people arbitrarily assigning large sizes to primary/foreign key fields in their MySQL schemas, such as INT(11) and even BIGINT(20) as WordPress uses. Now correct me if I'm wrong, but even an INT(4) would support (unsigned) values up to over 4 billion. Change it to INT(5) and you allow for values up to a quadrillio...

Problem with auto increment primary key (MySQL).

I have 2 tables each using other's primary key as a foreign key. The primary keys for both are set to auto_increment. The problem is, when I try to create and entry into one of the tables, I have no idea what the primary key of the entry is and can't figure out what to put in the other table as a foreign key. What should I do? Do I drop...

How to identify composite primary key in any Mysql Database table?

How to identify composite primary key in any Mysql Database table? or EDIT 2 what sql query should be used to display the indees of any table who contains the composite primary keys? I have many tables in mysql database which are having composite keys of 2 or 3 primary keys, I am using phpmyadmin, and I have to code a php scr...

Can't find which row is causing conversion error

I have the following table: CREATE TABLE [dbo].[Accounts1]( [AccountId] [nvarchar](50) NULL, [ExpiryDate] [nvarchar](50) NULL ) I am trying to convert nvarchar to datetime using this query: select convert(datetime, expirydate) from accounts I get this error: Conversion failed when converting datetime from character string....

oracle primary_key and foreign_key

Is it possible to maintain relationship between two tables without primary key and foreign key if it is possible then how? ...

mysql add auto increment and a additional key

Hey all I am trying to alter a table with adding a new column setting it as auto increment and with a key. The table already has one key and this one will be an addition. The error I get is the following. error : Multiple primary key defined My code is: alter table user add column id int (11) NOT NULL AUTO_INCREMENT PRIMARY KEY FIR...

When i should use primary key or index?

When i should use primary key or index? Which are the differences between primary key and index and which is the best? ...

Database Design Question: GUID + Natural Numbers

For a database I'm building, I've decided to use natural numbers as the primary key. I'm aware of the advantages that GUID's allow, but looking at the data, the bulk of row's data were GUID keys. I want to generate XML records from the database data, and one problem with natural numbers is that I don't want to expose my database key's t...

primary key datatype in sql server database

i see after installing the asp.net membership tables, they use the data type "uniqueidentifier" for all of the primary key fields. I have been using "int" data type and doing increment by one on inserts and declaring the column as IDENTITY. Is there any particular benefits to using the uniqueIdentifier data type compared to my current ...

SQL multiple primary keys - localization

I am trying to implement some localization in my database. It looks something like this (prefixes only for clarification) tbl-Categories ID Language Name tbl-Articles ID CategoryID Now, in my tbl-Categories, I want to have primary keys spanning ID and language, so that every combination of ID and language is unique. In tbl-Articles...