In SQL Server you actually shouldn't need to make any changes to the outlying code or add an INSTEAD OF trigger to make this work. Here is a quick example, tested on SQL Server 2012, but should work fine on 2005 as well:
CREATE TABLE dbo.source(bar INT, x UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID());
GO
CREATE TABLE dbo.[target](bar INT, x UNIQUEIDENTIFIER PRIMARY KEY);
GO
INSERT dbo.source(bar) SELECT 1;
GO
INSERT dbo.[target] SELECT * FROM dbo.source;
GO
ALTER TABLE dbo.[target] ADD TargetID INT IDENTITY(1,1);
GO
TRUNCATE TABLE dbo.source;
INSERT dbo.source(bar) SELECT 1;
GO
INSERT dbo.[target] SELECT * FROM dbo.source;
GO
SELECT * FROM dbo.[target];
Results:
bar x TargetID
--- ------------------------------------ --------
1 EFF8DAC4-FB3E-4734-80BE-6DC229846203 1
1 5036688D-C04A-45FC-920E-FF44D7D501D1 2
Now I can also change the primary key and repeat the process:
ALTER TABLE [dbo].[target] DROP CONSTRAINT PK__target__3BD019E50386B4EA;
ALTER TABLE [dbo].[target] ADD CONSTRAINT PK__target__3BD019E50386B4EA
PRIMARY KEY (targetID);
TRUNCATE TABLE dbo.source;
INSERT dbo.source(bar) SELECT 1;
GO
INSERT dbo.[target] SELECT * FROM dbo.source;
GO
SELECT * FROM dbo.[target];
Results:
bar x TargetID
--- ------------------------------------ --------
1 EFF8DAC4-FB3E-4734-80BE-6DC229846203 1
1 5036688D-C04A-45FC-920E-FF44D7D501D1 2
1 41FE97FF-7D45-46EB-8A0D-B2C3BA1E67EA 3
So I haven't had to change my bad code that uses insert/select without any column lists, as long as the source table doesn't also change and assuming that the only change to the target is the addition of an identity column.
PS here is how you can automate the generation of the IDENTITY columns (assuming you will want <tablename>ID
):
DECLARE @sql NVARCHAR(MAX);
SET @sql = N'';
SELECT @sql = @sql + '
ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(t.[object_id]))
+ '.' + QUOTENAME(t.name) + ' ADD ' + t.name + 'ID INT IDENTITY(1,1);'
FROM sys.tables AS t
WHERE name IN (...) -- you will need to fill in this part
AND NOT EXISTS
(
SELECT 1 FROM sys.columns WHERE [object_id] = t.[object_id]
AND (is_identity = 1 OR name = t.name + 'ID')
);
SELECT @sql;
-- EXEC sp_executesql @sql;
(Note that the SELECT
output will show you roughly what the command looks like, but due to output limitations in SSMS and depending on how many tables you have, it won't necessarily show you the full command that will get executed when you uncomment the EXEC
.)
And the drop / re-create of the primary keys:
DECLARE @sql NVARCHAR(MAX);
SET @sql = N'';
SELECT @sql = @sql + '
ALTER TABLE ' +
+ QUOTENAME(OBJECT_SCHEMA_NAME(t.[object_id]))
+ '.' + QUOTENAME(t.name) + ' DROP CONSTRAINT ' + k.name + ';
ALTER TABLE ' +
+ QUOTENAME(OBJECT_SCHEMA_NAME(t.[object_id]))
+ '.' + QUOTENAME(t.name) + ' ADD CONSTRAINT '
+ k.name + ' PRIMARY KEY (' + t.name + 'ID);'
FROM sys.key_constraints AS k
INNER JOIN sys.tables AS t
ON k.parent_object_id = t.[object_id]
WHERE k.[type] = 'PK'
AND t.name IN (...); -- again, you'll want to identify the list of tables
SELECT @sql;
-- EXEC sp_executesql @sql;
And you'll want to do this while the database is in SINGLE_USER
mode or while the application(s) are otherwise not able to connect to the database. You'll also want to test all this on a QA or dev system before unleashing any of it on production.
Now, this still isn't exactly best practice - I highly recommend you stop embedding SQL code in your apps, especially SQL code that does insert/select without specifying column lists.
Postgres has the serial
datatype which matches SQL Server's IDENTITY
or MySQL's AUTO_INCREMENT
.
Internally it is shorthand for a SEQUENCE
but does that matter? It acts like IDENTITY
/AUTO_INCREMENT
:
The data types serial
and bigserial
are not true types, but merely a notational convenience for creating unique identifier columns (similar to the AUTO_INCREMENT
property supported by some other databases). In the current implementation, specifying:
CREATE TABLE tablename (
colname SERIAL
);
is equivalent to specifying:
CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename (
colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;
Edit,
I think what OP means is "is there SCOPE_IDENTITY or such" in PostgreSQL. Yes.
You'd need currval or another one
Best Answer
This is to implement the feature found in the standard. (copied from a draft, date: 2011-12-21):
It is a property of a column which basically says that the values for the column will be provided by the DBMS and not by the user and in some specific manner and restrictions (increasing, decreasing, having max/min values, cycling if the max/min value is reached).
Sequence generators (usually called just "sequences") are a related SQL standard feature: it's a mechanism that provides such values - and can be used for identity columns.
Note the subtle difference: a
SEQUENCE
is an object that can be used to provide values for one or more identity columns or even at will.The various DBMS have so far implemented similar features in different ways and syntax (MySQL:
AUTO_INCREMENT
, SQL Server:IDENTITY (seed, increment)
, PostgreSQL:serial
usingSEQUENCE
, Oracle: using triggers, etc) and only recently added sequence generators (SQL Server in version 2012 and Oracle in 12c).Up to now Postgres has implemented sequence generators (which can be used to provide values for column, either with the special macros
serial
andbigserial
or withnextval()
function) but has not yet implemented the syntax for identity columns, as it is in the standard.Defining identity columns (and the slight difference from
serial
columns) and various syntax (eg.GENERATED ALWAYS
,NEXT VALUE FOR
, etc) from the SQL standard is what this feature is about. Some changes / improvements may need to be done on the implementation of sequences as well, as identity columns will be using sequences.If you follow the link identitity columns (from the page you saw), you'll find:
Update 2017, September: seems like the feature will be in Postgres 10, which is to be released in a few days/weeks: What's New In Postgres 10: Identity Columns
Oracle have also implemented identity columns and sequences, in version 12c. The syntax is according to the standard, as far as I checked:
Identity Columns in Oracle Database 12c Release 1 (12.1)