Rectangle 27 15

I spent some time digging through the alembic source code, and this doesn't seem to be supported. You can specify primary keys when creating a table, but not when adding columns. In fact, it specifically checks and won't let you:

# from alembic.operations.add_column, line 284
for constraint in t.constraints:
  if not isinstance(constraint, schema.PrimaryKeyConstraint):
    self.impl.add_constraint(constraint)

I looked around, and adding a primary key to an existing table may result in unspecified behavior - primary keys aren't supposed to be null, so your engine may or may not create primary keys for existing rows. See this SO discussion for more info: Insert auto increment primary key to existing table

I'd just run the alter query directly, and create primary keys if you need to.

op.execute("ALTER TABLE mytable ADD id INT PRIMARY KEY AUTO_INCREMENT;")

If you really need cross-engine compatibility, the big hammer would be to (1) create a new table identical to the old one with a primary key, (2) migrate all your data, (3)delete the old table and (4) rename the new table.

sqlalchemy - Adding primary key to existing MySQL table in alembic - S...

sqlalchemy alembic
Rectangle 27 2

I was able to adapt these instructions take a table with an existing non-increment primary key, and add an incrementing primary key to the table and create a new composite primary key with both the old and new keys as a composite primary key using the following code:

DROP TABLE  IF EXISTS SAKAI_USER_ID_MAP;

CREATE TABLE SAKAI_USER_ID_MAP (
       USER_ID             VARCHAR (99) NOT NULL,
       EID                 VARCHAR (255) NOT NULL,
       PRIMARY KEY (USER_ID)
);

INSERT INTO SAKAI_USER_ID_MAP VALUES ('admin', 'admin');
INSERT INTO SAKAI_USER_ID_MAP VALUES ('postmaster', 'postmaster');

ALTER TABLE  SAKAI_USER_ID_MAP 
  DROP PRIMARY KEY, 
  ADD _USER_ID INT AUTO_INCREMENT NOT NULL FIRST, 
  ADD PRIMARY KEY ( _USER_ID, USER_ID );

When this is done, the _USER_ID field exists and has all number values for the primary key exactly as you would expect. With the "DROP TABLE" at the top, you can run this over and over to experiment with variations.

What I have not been able to get working is the situation where there are incoming FOREIGN KEYs that already point at the USER_ID field. I get this message when I try to do a more complex example with an incoming foreign key from another table.

#1025 - Error on rename of './zap/#sql-da07_6d' to './zap/SAKAI_USER_ID_MAP' (errno: 150)

I am guessing that I need to tear down all foreign keys before doing the ALTER table and then rebuild them afterwards. But for now I wanted to share this solution to a more challenging version of the original question in case others ran into this situation.

mysql - Insert auto increment primary key to existing table - Stack Ov...

mysql primary-key
Rectangle 27 2

I was able to adapt these instructions take a table with an existing non-increment primary key, and add an incrementing primary key to the table and create a new composite primary key with both the old and new keys as a composite primary key using the following code:

DROP TABLE  IF EXISTS SAKAI_USER_ID_MAP;

CREATE TABLE SAKAI_USER_ID_MAP (
       USER_ID             VARCHAR (99) NOT NULL,
       EID                 VARCHAR (255) NOT NULL,
       PRIMARY KEY (USER_ID)
);

INSERT INTO SAKAI_USER_ID_MAP VALUES ('admin', 'admin');
INSERT INTO SAKAI_USER_ID_MAP VALUES ('postmaster', 'postmaster');

ALTER TABLE  SAKAI_USER_ID_MAP 
  DROP PRIMARY KEY, 
  ADD _USER_ID INT AUTO_INCREMENT NOT NULL FIRST, 
  ADD PRIMARY KEY ( _USER_ID, USER_ID );

When this is done, the _USER_ID field exists and has all number values for the primary key exactly as you would expect. With the "DROP TABLE" at the top, you can run this over and over to experiment with variations.

What I have not been able to get working is the situation where there are incoming FOREIGN KEYs that already point at the USER_ID field. I get this message when I try to do a more complex example with an incoming foreign key from another table.

#1025 - Error on rename of './zap/#sql-da07_6d' to './zap/SAKAI_USER_ID_MAP' (errno: 150)

I am guessing that I need to tear down all foreign keys before doing the ALTER table and then rebuild them afterwards. But for now I wanted to share this solution to a more challenging version of the original question in case others ran into this situation.

mysql - Insert auto increment primary key to existing table - Stack Ov...

mysql primary-key
Rectangle 27 2

When we add and identity column in an existing table it will automatically populate no need to populate it manually.

SQL Server add auto increment primary key to existing table - Stack Ov...

sql-server sql-server-2008 primary-key alter-table
Rectangle 27 2

When we add and identity column in an existing table it will automatically populate no need to populate it manually.

SQL Server add auto increment primary key to existing table - Stack Ov...

sql-server sql-server-2008 primary-key alter-table
Rectangle 27 5

If you add an new id column to an existing table and make it AUTO_INCREMENT PRIMARY KEY, it will automatically populate it with incrementing values.

mysql> ALTER TABLE TheTable ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY;
mysql> SELECT id FROM TheTable;
-- outputs values 1, 2, 3, etc.

If you made an id column but didn't declare it AUTO_INCREMENT PRIMARY KEY, you can populate the column like this:

mysql> SET @id := 0;
mysql> UPDATE TheTable SET id = (@id := @id+1);

You are awesome! Thanks for the help! Works a treat

sql - What MySQL statement needed to populate a field with incremental...

mysql sql database ms-access
Rectangle 27 196

An ALTER TABLE statement adding the PRIMARY KEY column works correctly in my testing:

ALTER TABLE tbl ADD id INT PRIMARY KEY AUTO_INCREMENT;

On a temporary table created for testing purposes, the above statement created the AUTO_INCREMENT id column and inserted auto-increment values for each existing row in the table, starting with 1.

+1, works in 5.1.51-ndb-7.1.9a-log, on table with engine MyISAM

FIRST
ALTER TABLE tbl1 ADD id INT PRIMARY KEY AUTO_INCREMENT FIRST;

mysql - Insert auto increment primary key to existing table - Stack Ov...

mysql primary-key
Rectangle 27 192

An ALTER TABLE statement adding the PRIMARY KEY column works correctly in my testing:

ALTER TABLE tbl ADD id INT PRIMARY KEY AUTO_INCREMENT;

On a temporary table created for testing purposes, the above statement created the AUTO_INCREMENT id column and inserted auto-increment values for each existing row in the table, starting with 1.

+1, works in 5.1.51-ndb-7.1.9a-log, on table with engine MyISAM

FIRST
ALTER TABLE tbl1 ADD id INT PRIMARY KEY AUTO_INCREMENT FIRST;

mysql - Insert auto increment primary key to existing table - Stack Ov...

mysql primary-key
Rectangle 27 20

There is no option in pgAdmin to add a column to an existing table and make it the primary key at the same time, because this is hardly possible.

A primary key column needs to hold unique non-null values. Upon adding a column to an existing table, it holds NULL values. So you have to enter unique values before you can add a UNIQUE or PRIMARY KEY constraint.

There is an exception to that rule, though: If you add a serial column, unique values are inserted automatically. In this case, you can also define it PRIMARY KEY right away:

ALTER TABLE student ADD COLUMN student_number serial PRIMARY KEY;

This works in PostgreSQL 9.1. I am not sure it does in older versions, too.

pgAdmin does not incorporate this special case for serial columns in the "New column..." dialog at this time (version 1.14).

see Victor Barrantes answer,n it seems Mr theeth discover pgadmin so there is few chance he fall in the scenario you describe here, despite your answer is very interressant.

postgresql - Primary & Foreign Keys in pgAdmin - Stack Overflow

postgresql foreign-keys primary-key pgadmin
Rectangle 27 31

suppose you don't have column for auto increment like id, no, then you can add using following query:

ALTER TABLE table_name ADD id int NOT NULL AUTO_INCREMENT primary key FIRST

If you've column, then alter to auto increment using following query:

ALTER TABLE table_name MODIFY column_name datatype(length) AUTO_INCREMENT PRIMARY KEY

What does the FIRST at the end does?

It will make the new id column the first in the table as opposed to last, which is the default behaviour.

mysql - Insert auto increment primary key to existing table - Stack Ov...

mysql primary-key
Rectangle 27 31

suppose you don't have column for auto increment like id, no, then you can add using following query:

ALTER TABLE table_name ADD id int NOT NULL AUTO_INCREMENT primary key FIRST

If you've column, then alter to auto increment using following query:

ALTER TABLE table_name MODIFY column_name datatype(length) AUTO_INCREMENT PRIMARY KEY

What does the FIRST at the end does?

It will make the new id column the first in the table as opposed to last, which is the default behaviour.

mysql - Insert auto increment primary key to existing table - Stack Ov...

mysql primary-key
Rectangle 27 11

Create the column, but allow NULL. Populate the column with the correct data from the foreign key table. Alter the column add not null. Add the foreign key constraint.

:( I was so hoping there was a "cool" way to do it! But thank you for the information anyway... I will leave this post "unanswered" for a little while longer to see if there are any other thought provoking ideas.

You could add the column with a default, then copy over the correct data, then drop the default constraint. Different path, same number of steps. I don't think it's going to get any "cooler".

sql server - How do you add a NOT NULL FOREIGN KEY column to an existi...

sql-server sql-server-2005 foreign-keys constraints nullable
Rectangle 27 8

Alter table table_name modify column_name datatype(length) AUTO_INCREMENT PRIMARY KEY

You should add primary key to auto increment, otherwise you got error in mysql.

mysql - How to add AUTO_INCREMENT to an existing column? - Stack Overf...

mysql auto-increment
Rectangle 27 3

The AUTO_INCREMENT column shouldn't necessarily conform to any particular order it's just arbitrary integers. What will happen down the road, for example, when you add some new item to the menu? It will "belong" in the middle of the table, but its AUTO_INCREMENT value will be a newly assigned value, not subject to your current ordering.

Instead of worrying about the order of the AUTO_INCREMENT values, simply apply the correct ORDER BY clause when selecting data from your table:

SELECT *
FROM tablename
ORDER BY parentId ASC;

Even with an AUTO_INCREMENT column you have to apply an ORDER BY clause to ensure the order of results, so you'd might as well use the one that makes the most semantic sense.

mySQL - Ordering table as desired, before Adding Primary Key column - ...

mysql
Rectangle 27 12

Method to add AUTO_INCREMENT to a table with data while avoiding Duplicate entry error:

Make a copy of the table with the data using INSERT SELECT:

CREATE TABLE backupTable LIKE originalTable; 
INSERT backupTable SELECT * FROM originalTable;
TRUNCATE TABLE originalTable;
  • To add AUTO_INCREMENT and PRIMARY KEY ALTER TABLE originalTable ADD id INT PRIMARY KEY AUTO_INCREMENT;

Copy data back to originalTable (do not include the newly created column (id), since it will be automatically populated)

INSERT originalTable (col1, col2, col3) 
SELECT col1, col2,col3
FROM backupTable;
DROP TABLE backupTable;

More on the duplication of tables using CREATE LIKE:

If you have a large table...or you want to do things a little smarter replace step 1 with: CREATE TABLE backupTable LIKE originalTable; RENAME TABLE originalTable TO originalTable.old, backupTable TO originalTable; Skip step 2 or you will loose all your data.

mysql - How to add AUTO_INCREMENT to an existing column? - Stack Overf...

mysql auto-increment
Rectangle 27 1

That error message means you have values in newcol that do not exist in othertable id column. By adding a column of INT NOT NULL, you set all the initial values for that column to 0. If you do not have an id of 0 in othertable, you have a key mismatch

SELECT  newcol 
FROM    test
WHERE   newcol NOT IN (
    SELECT  id
    FROM    othertable
)

if you get any results returned, then you have key values in newcol that violate the FK restraints

what should i do to fix it so that i add foreign key

you need to update the test newcol values so that they correspond to the values within othertable id column.

MySQL Add foreign key to existing table PHP - Stack Overflow

mysql foreign-keys relational-database
Rectangle 27 3

You need to add the CATEGORY_NAME column on ITEM TABLE, or map the foreign key to another existing column in ITEM.

ALTER TABLE ITEM
ADD CATEGORY_NAME VARCHAR(35) NOT NULL;

ALTER TABLE ITEM
ADD CONSTRAINT FK_CATEGORY_NAME
FOREIGN KEY (CATEGORY_NAME)
REFERENCES CATEGORY (CATEGORY_NAME);
ALTER TABLE ITEM
ADD CONSTRAINT FK_CATEGORY_NAME
FOREIGN KEY (SOME_OTHER_EXISTING_COLUMN)
REFERENCES CATEGORY (CATEGORY_NAME);

mysql - How to add a foreign key to an existing table? - Stack Overflo...

mysql sql database foreign-keys
Rectangle 27 2

Other solution could be, add a another table with primary key of the original table, along with your new column.

Populate your primary key onto the new table and populate values for new column in your new table, and modify your query to join this table for select operations and you also need to insert, update separately for this column value.

When you able to get downtime, you can alter the original table, modify your DML queries and drop your new table created earlier

Else, you may go for clustering method, replication, pt-online-schema tool from percona

sql - ALTER TABLE without locking the table? - Stack Overflow

sql mysql ddl alter-table
Rectangle 27 14

@Adrian Cornish's answer is correct. However, there is another caveat to dropping an existing primary key. If that primary key is being used as a foreign key by another table you will get an error when trying to drop it. In some versions of mysql the error message there was malformed (as of 5.5.17, this error message is still

alter table parent  drop column id;
ERROR 1025 (HY000): Error on rename of
'./test/#sql-a04_b' to './test/parent' (errno: 150).

If you want to drop a primary key that's being referenced by another table, you will have to drop the foreign key in that other table first. You can recreate that foreign key if you still want it after you recreate the primary key.

Also, when using composite keys, order is important. These

1) ALTER TABLE provider ADD PRIMARY KEY(person,place,thing);
and
2) ALTER TABLE provider ADD PRIMARY KEY(person,thing,place);

are not the the same thing. They both enforce uniqueness on that set of three fields, however from an indexing standpoint there is a difference. The fields are indexed from left to right. For example, consider the following queries:

A) SELECT person, place, thing FROM provider WHERE person = 'foo' AND thing = 'bar';
B) SELECT person, place, thing FROM provider WHERE person = 'foo' AND place = 'baz';
C) SELECT person, place, thing FROM provider WHERE person = 'foo' AND place = 'baz' AND thing = 'bar';
D) SELECT person, place, thing FROM provider WHERE place = 'baz' AND thing = 'bar';

B can use the primary key index in ALTER statement 1 A can use the primary key index in ALTER statement 2 C can use either index D can't use either index

A uses the first two fields in index 2 as a partial index. A can't use index 1 because it doesn't know the intermediate place portion of the index. It might still be able to use a partial index on just person though.

mysql - ALTER TABLE to add a composite primary key - Stack Overflow

mysql sql primary-key alter-table composite-primary-key
Rectangle 27 23

Indexes of two types can be added: when you define a primary key, MySQL will take it as index by default

Primary key as index

Consider you have a TBL_STUDENT table and you want STUDENT_ID as primary key:

Above statement adds a primary key, which means that indexed values must be unique and cannot be NULL.

Above statement will create an ordinary index with student_index name.

Here, student_unique_index is the index name assigned to STUDENT_ID and creates an index for which values must be unique (here null can be accepted).

ALTER TABLE `tbl_student` ADD FULLTEXT student_fulltext_index (`student_id`)

Above statement will create the Fulltext index name with student_fulltext_index, for which you need MyISAM Mysql Engine.

DROP INDEX `student_index` ON `tbl_student`
SHOW INDEX FROM `tbl_student`

optimization - How do I add indices to MySQL tables? - Stack Overflow

mysql optimization indexing row