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

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 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 26

If you have the column and the sequence, you first need to populate a new key for all the existing rows. Assuming you don't care which key is assigned to which row

UPDATE table_name
   SET new_pk_column = sequence_name.nextval;

Once that's done, you can create the primary key constraint (this assumes that either there is no existing primary key constraint or that you have already dropped the existing primary key constraint)

ALTER TABLE table_name
  ADD CONSTRAINT pk_table_name PRIMARY KEY( new_pk_column )

If you want to generate the key automatically, you'd need to add a trigger

If you are on an older version of Oracle, the syntax is a bit more cumbersome

CREATE TRIGGER trigger_name
  BEFORE INSERT ON table_name
  FOR EACH ROW
BEGIN
  SELECT sequence_name.nextval
    INTO :new.new_pk_column
    FROM dual;
END;

i have done the above code and it works fine. but the column id value is changed the last inserted column got last column id. but in case if some of our colleagues insert data into table they start with inserting primary key column value then it will show error. Is there any way to change the column id.

@mallikarjun - I'm having a hard time understanding what it is you're asking. What, exactly, is someone doing? What, exactly, is the error?

@mallikarjun - What does "column id" mean? What, exactly, are you trying to change? Are you trying to change the order of columns in a table?

The second trigger query (SELECT INTO) is necessary for 10gR2

Add a auto increment primary key to existing table in oracle - Stack O...

oracle
Rectangle 27 26

If you have the column and the sequence, you first need to populate a new key for all the existing rows. Assuming you don't care which key is assigned to which row

UPDATE table_name
   SET new_pk_column = sequence_name.nextval;

Once that's done, you can create the primary key constraint (this assumes that either there is no existing primary key constraint or that you have already dropped the existing primary key constraint)

ALTER TABLE table_name
  ADD CONSTRAINT pk_table_name PRIMARY KEY( new_pk_column )

If you want to generate the key automatically, you'd need to add a trigger

If you are on an older version of Oracle, the syntax is a bit more cumbersome

CREATE TRIGGER trigger_name
  BEFORE INSERT ON table_name
  FOR EACH ROW
BEGIN
  SELECT sequence_name.nextval
    INTO :new.new_pk_column
    FROM dual;
END;

i have done the above code and it works fine. but the column id value is changed the last inserted column got last column id. but in case if some of our colleagues insert data into table they start with inserting primary key column value then it will show error. Is there any way to change the column id.

@mallikarjun - I'm having a hard time understanding what it is you're asking. What, exactly, is someone doing? What, exactly, is the error?

@mallikarjun - What does "column id" mean? What, exactly, are you trying to change? Are you trying to change the order of columns in a table?

The second trigger query (SELECT INTO) is necessary for 10gR2

Add a auto increment primary key to existing table in oracle - Stack O...

oracle
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 8

I had this issue, but couldn't use an identity column (for various reasons). I settled on this:

DECLARE @id INT
SET @id = 0 
UPDATE table SET @id = id = @id + 1

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

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

I had this issue, but couldn't use an identity column (for various reasons). I settled on this:

DECLARE @id INT
SET @id = 0 
UPDATE table SET @id = id = @id + 1

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

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

Not sure why this answer it is marked as correct and has 7 because the active model needs a primary key to work by design and you won't have it in a db you can try the Joshua tip though

@open-ecommerce.org just because Joshua completed 4 years later the answer, and Yii versions have been updated.

php - Calling a database view in Yii using Active Record - Stack Overf...

php mysql activerecord yii
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 283

To add a foreign key (grade_id) to an existing table (users), follow the following steps:

ALTER TABLE users ADD grade_id SMALLINT UNSIGNED NOT NULL DEFAULT 0;
ALTER TABLE users ADD CONSTRAINT fk_grade_id FOREIGN KEY (grade_id) REFERENCES grades(id);

Reasons help me understand and remember. This is because you can't add a foreign key to an non-unsigned field, correct?

@PixMach, the answer is no. You can have signed integers as foreign keys. As N.B. noted on the question, the type and sign of the fields need to match. So if your primary key in the lookup table is UNSIGNED, then the foreign key field must also be UNSIGNED. If the primary key field is SIGNED, then foreign key field must also be signed. Think of it this way: whatever the column in the one table is defined as in a SHOW CREATE TABLE, it needs to have the same definition in the other table.

Note that this could also be done with a single query (might be better in case of failure etc)

And do not forget to derive your index column on your refrence table, if you re using phpmyadmin mysql as MariaDb as database storege i guess it work for oracle mysql as well

I had to run "SET FOREIGN_KEY_CHECKS=0;" before running the ADD CONSTRAINT command or SQL would complain "Cannot add or update a child row: a foreign key constraint fails".

mysql - Add Foreign Key to existing table - Stack Overflow

mysql foreign-keys
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 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 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 2

If the column already exists in your table and it is null, you can update the column with this command (replace id, tablename, and tablekey ):

UPDATE x
SET x.<Id> = x.New_Id
FROM (
  SELECT <Id>, ROW_NUMBER() OVER (ORDER BY <tablekey>) AS New_Id
  FROM <tablename>
  ) x

You saved me time with this one! Good addendum to @gbn's answer.

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

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

If the column already exists in your table and it is null, you can update the column with this command (replace id, tablename, and tablekey ):

UPDATE x
SET x.<Id> = x.New_Id
FROM (
  SELECT <Id>, ROW_NUMBER() OVER (ORDER BY <tablekey>) AS New_Id
  FROM <tablename>
  ) x

You saved me time with this one! Good addendum to @gbn's answer.

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

sql-server sql-server-2008 primary-key alter-table