Join the world’s largest interactive community dedicated to Oracle technologies.
※ Download: Alter table set primary key
I assume this means that the restriction to Enterprise or Developer doesn't apply to ALTER TABLE... This controls whether this column is held inline or in a secondary TOAST table, and whether the data should be compressed or not.
RESTRICT Refuse to drop the column or constraint if there are any dependent objects. You can't send emails. But it can also be used in non-partitioned tables too.
Join the world’s largest interactive community dedicated to Oracle technologies. - If you add a default to a new column, existing rows in the table gain the default value in the new column.
You can't alter the existing columns for identity. New table Here you can retain the existing data values on the newly created identity column. Names INSERT INTO dbo. If table is large, there's another option I prefer: use ALTER TABLE... SWITCH to replace the table schema with another version with an IDENTITY column but otherwise identical schema. The advantage of the ALTER TABLE.... SWITCH approach is that it completes quickly under 5 seconds for a billion-row table since no table data needs to be copied or changed. There are caveats and limitations though. See my answer below for details. The reason this works is because IDENTITY is a column property and not a data type, so the SWITCH method validates the schemas between the two tables old and new as being identifiable irrespective of the IDENTITY difference. This is important for large tables where touching every data page can take minutes or hours. Here's the trick: you can use SQL Server's statement to change the schema of a table without changing the data, meaning you can replace a table with an IDENTITY with an identical table schema, but without an IDENTITY column. The same trick works to add IDENTITY to an existing column. Normally, is used to efficiently replace a full partition in a partitioned table with a new, empty partition. But it can also be used in non-partitioned tables too. I've used this trick to convert, in under 5 seconds, a column of a of a 2. Here's a code sample of how it works. CREATE TABLE Test id int identity 1,1 , somecolumn varchar 10 ; INSERT INTO Test VALUES 'Hello' ; INSERT INTO Test VALUES 'World' ; -- copy the table. Zhwiti in his comment below says that it also works in SQL Standard Edition too. I assume this means that the restriction to Enterprise or Developer doesn't apply to ALTER TABLE... There's a good detailing the requirements above. UPDATE - had a comment below that adds important info about this solution. Copying it here to make sure it gets more attention: There's another caveat here that is worth mentioning. Althought the new table will happily receive data from the old table, and all the new rows will be inserted following a identity pattern, they will start at 1 and potentially break if the said column is a primary key. Consider running DBCC CHECKIDENT '' immediately after switching. See for more info. If the table is actively being extended with new rows meaning you don't have much if any downtime between adding IDENTITY and adding new rows, then instead of DBCC CHECKIDENT you'll want to manually set the identity seed value in the new table schema to be larger than the largest existing ID in the table, e. You might be able to include both the ALTER TABLE... SWITCH and the DBCC CHECKIDENT in a transaction or not-- haven't tested this but seems like setting the seed value manually will be easier and safer. Obviously, if no new rows are being added to the table or they're only added occasionally, like a daily ETL process then this race condition won't happen so DBCC CHECKIDENT is fine. Mason - great observation! I updated my answer accordingly. I'd assumed that, because partitions aren't supported in SQL Standard that ALTER TABLE... SWITCH wouldn't be supported either. But given that you can use this command on non-partitioned tables, it makes sense that you can also run it on Standard Edition. Thanks for the correction. My answer above is focused that narrow use-case: how to add the IDENTITY to a column without actually changing any data. The approach I document above is a huge time-saver for large tables. If you need to change data, then you'll need to use other solutions. Althought the new table will happily receive data from the old table, and all the new rows will be inserted following a identity pattern, they will start at 1 and potentially break if the said column is a primary key. Consider running DBCC CHECKIDENT '' immediately after switching. See for more info. Also note that nullability of columns must be the same. So if you need to change a column nullability, you will have to do it at a later step. Same goes for PK constraints. I also change the identity value in the table creation to match the current maximum : IDENTITY maxID+1, 1 — Aug 16 '16 at 20:18 You cannot alter a column to be an IDENTITY column. What you'll need to do is create a new column which is defined as an IDENTITY from the get-go, then drop the old column, and rename the new one to the old name. NewColumn', 'OldColumnName', 'COLUMN' Marc There is cool solution described here: In short edit manually your table in SQL Manager, switch the identity, DO NOT SAVE changes, just show the script which will be created for the changes, copy it and use it later. It is huge time saver, because it the script contains all the foreign keys, indices, etc. IN sql server 2014 I don't know about lower versions you can do this simply, using sequence. I was tasked with moving an entire schema between two databases, so without having a DBA, I had to do it and do it by running scripts, not being able to use the GUI in SQL Server 2008 because I didn't have admin privileges. Everything was moved without issue, however, when running a stored procedure on the new schema. I double checked the script that created the table and it was there, however, SQL Server didn't get it when I ran the script. I was told later by a DBA that he had seen this same problem before. In any event, for SQL Server 2008, these are the steps I took to get this resolved and they worked, so I'm posting this here in the hopes it will be a help to someone. This is what I did as I had FK dependencies on another table that made this more difficult: I used this query to verify the identity was indeed missing and to view dependencies on the table. Create a duplicate, identical new table, except add an identity field on the PK field where it had been before. Disable the identity to move data. SELECT field1, field2, etc... Verify the data is there. This is the best script I found to get all the FK relationships to verify which table s the original table references as dependencies and I came across many, so it is a keeper! Make sure you have all the PK and FK scripts for all the tables involved, before this next step. You can right-click on each key and script this using SQL Server 2008 10. Drop the original table: DROP TABLE dbo. These next steps rely on the scripts you created in SQL Server 2008 in step 9. Verify everything is correct and complete. I used the GUI to look at the tables. Rename the new table to the original tables name. By design there is no simple way to turn on or turn off the identity feature for an existing column. The only clean way to do this is to create a new column and make it an identity column or create a new table and migrate your data. You will see that this is what SQL server in doing in the background. Now the table 'Employee' with 'EmployeeId' is modified for Identity rules along with existing primary key constraint There isn't one, sadly; the IDENTITY property belongs to the table rather than the column. The easier way is to do it in the GUI, but if this isn't an option, you can go the long way around of copying the data, dropping the column, re-adding it with identity, and putting the data back. See for a blow-by-blow account. The table opens in Table Designer. This value will be assigned to the first row in the table. The value 1 will be assigned by default. Although this post is old and I am making an assumption about the requestors need, I felt this additional information could be helpful to users encountering this thread as I believe the conversation could lead one to believe that an existing column can not be set to be a primary key without adding it as a new column first which would be incorrect. As per my current condition, I follow this approach. I want to give identity to a primary table after data inserted via script. As I want to append identity, so it always start from 1 to End of record count that I want. However, you can do it through the Enterprise Manager design view. Alternatively you could create a new row as the identity column, drop the old column, then rename your new column. ALTER TABLE FooTable ADD BarColumn INT IDENTITY 1, 1 NOT NULL PRIMARY KEY CLUSTERED Basically there are four logical steps. Turn on Insert Identity for this new column. There may be some more complexities like working across multiple servers etc. These steps are intended for beginners with less grip on T-SQL.
Note: Adding a constraint using an existing index can be helpful in situations where a new constraint needs to be added without blocking table updates for a long time. If you specify CASCADE, then the column drop should additionally drop other schema objects which have become invalid. After a table rewrite, the table will appear empty to concurrent transactions, if they are using a snapshot taken before the rewrite occurred. However, the VALUE of the primary key is made up of TWO COLUMNS ID + LastName. To do so the column must not be used in a PRIMARY KEY constraint. Derby also raises an error if you specify RESTRICT when you drop a column referenced by the generation-clause of a generated column. All the columns of the index will be included in the constraint. Specify a value of 0 to revert to estimating the number of distinct values normally.
