primary-key

How can I add a column to the primary key of a MySQL InnoDB table?

I have tables foo and bar: create table foo(a int, b varchar(10), primary key (a)); create table bar(a int, c int, d int, primary key (a,c), foreign key(a) references foo(a)); Now I have a new column e that needs to participate in the primary key of bar. How can I do this? It seems I...

Please tell me how to get primary key name in SQL Server 2005

I need a primary key name and primary key column name of a table please tell me what query should I write.. ...

Hibernate creates two primary keys when I only want one...

I have a Hibernate class called Expression (simplified here for your viewing pleasure): @Entity public class Expression { @Id @GeneratedValue private long id; private String data; @OneToMany(fetch=FetchType.EAGER) @Cascade({CascadeType.MERGE, CascadeType.PERSIST}) private Set<Expression> dependencies; } T...

Help defining tables/Primary Keys

I'm designing a database for a franchiser. My skill level is intermediate at best (I just work in the franchiser's office). This database must define store locations and franchisees. So I know I will need a "store" table and a "franchisee" table. The "store number" will be the primary key in the store table. One franchisee can own mu...

composite primary key using newid and newsequentialid

I know that using GUID generated by newid isn't a good candidate for primary key for performance issues. How about a composite primary key with {newsequentialid(), newid()} so a new GUID is guranteed to be greater than the one generated previously? Is there a performance issue here too? You may think why would anyone do this but i am ...

MySQL: Table structure for a user's "views"

I've got a question to which I've had opposing pieces of advice, would appreciate additional views. My site has users, each with a user_id. These users can view products, and I need to keep track of the unique instances of users viewing specific products. To record a view in a separate views table, I've currently got two options: OPTIO...

adding primary key to sql view

Reading that http://stackoverflow.com/questions/894610/how-to-do-hibernate-mapping-for-table-or-view-without-a-primary-key I am wondering how to add a primary key to my view as it is basically just a stored query...? PS: oracle 10g thx ...

How to alter length of varchar in composite primary key?

In MSSQL I have a table created like this: CREATE TABLE [mytable] (fkid int NOT NULL, data varchar(255) CONSTRAINT DF_mytable_data DEFAULT '' NOT NULL); ALTER TABLE [mytable] ADD CONSTRAINT PK_mytable_data PRIMARY KEY (fkid, data); Now I want to increase the length of the 'data' column from 255 to 4000. If I just try: ALTER TABLE [...

keys and unique rows in appengine datastore

I'm still fairly new to working with java and the google appengine datastore. I can put data in and get it out of the datastore, and I am trying to make it so that a user cannot be entered twice. As there is no unique index on the datastore, I'm setting a hash of the users email address as a primarykey. Strangely, when I enter the sa...

SQL - SQLDataAdapter Update causing primary key violation

I need to be able to change the primary keys in a table. The problem is, some of the keys will be changing to existing key values. E.g. record1.ID 3=>4 and record2.ID 4=>5. I need to keep these as primary keys as they are set as foreign keys (which cascade up update) Is there a reasonable way to accomplish this, or am I attempting sql...

Update query with 'not exists' check causes primary key violation

The following tables are involved: Table Product: product_id merged_product_id product_name Table Company_Product: product_id company_id (Company_Product has a primary key on both the product_id and company_id columns) I now want to run an update on Company_Product to set the product_id column to a merged_ product_id. This update co...

Details of impact for indexes, primary keys, unique keys

I like to think I know enought theory, but I have little experience optimizing DB in real world. I would like to know points of view, thoughts or experiences. Let's imagine a scenario like: Table A Key: c1, c2, c3, c4 Index: c7, c3, c2 Table B Key: c1, c2, c3, c4 Index: c1, c5 All are non-clustered. The tables have 40+ fields. They a...

using primary key in asp.net mvc urls

i keep hearing that i shouldn't be using primary keys in my asp.net mvc url for example: /Users/Edit/1243 what is the issue here? what is the alternative, putting in user names? as you want it to be unique, it seems like primary key is the cleanest option thoughts? ...

PRIMARY and INDEX keys for one FOREIGN column in MySQL

I used MySQL Workbench to prepare a database layout and exported it to my database using phpMyAdmin. When looking at one table, I got the following warning: PRIMARY and INDEX keys should not both be set for column gid gid is a foreign index which is the primary key of a different table, and which is also part of the primary key of ...

Remove Primary Key in MySQL

I have the following table schema which maps user_customers to permissions on a live MySQL database: mysql> describe user_customer_permission; +------------------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------------+---------+------+-----+---------+...

How to retrieve the last autoincremented ID from a SQLite table?

I have a table Messages with columns ID (primary key, autoincrement) and Content (text). I have a table Users with columns username (primary key, text) and Hash. A message is sent by one Sender (user) to many recipients (user) and a recipient (user) can have many messages. I created a table Messages_Recipients with two columns: MessageID...

How to make a primary key start from 1000?

create table tablename ( id integer unsigned not null AUTO_INCREMENT, .... primary key id ); I need the primary key to start from 1000. I'm using MySQL. ...

What's a good data structure for a multiple-value primary key object?

This question can best be asked by example. Say I have a database table called "Car" with the following columns: (Make*, Model*, NumberOfDoors*, Description, Price, Mileage, Color) The 3-tuple of Make, Model, and NumberOfDoors makes up the unique primary key in the database. What I've done is made a "Car" class for each line item, bu...

primary key duplicate record bypass to next insert

weird question. i am inserting 10,000 records or so in a table and the primary key is not an Identity field. so when inserting all 10,000 if some are duplicate, is there a way to skip to next record in sql server insert and make sure the non-duplicates go in? i really dont care bout the duplicates not getting inserted. ...

Are usernames a valid candidate for a primary key?

I know surrogate primary keys are generally recommended over natural primary keys but are the arguments in favor of the surrogate primary keys valid when it comes to usernames? ...