SQL- IDs Identity values & PKs.sql

--A brief demo script about IDs, IDENTITY values, and boxes.
USE tempdb; --> we'll work in TempDB because it's quick, easy, and doesn't require any extra permissions

IF (OBJECT_ID('dbo.IdentityIsPK') IS NOT NULL)
	DROP TABLE dbo.IdentityIsPK;

CREATE TABLE dbo.IdentityIsPK (
    ID int IDENTITY(1,1)
        CONSTRAINT PK_IdentityIsPK PRIMARY KEY CLUSTERED
    , Foo varchar(100) NOT NULL
);

IF (OBJECT_ID('dbo.IdentityNotPK') IS NOT NULL)
	DROP TABLE dbo.IdentityNotPK;

CREATE TABLE dbo.IdentityNotPK (
    ID int IDENTITY(1,1)
    , Foo varchar(100) NOT NULL
        CONSTRAINT PK_IdentityNotPK PRIMARY KEY CLUSTERED
);

IF (OBJECT_ID('dbo.ID_IsNotReallyIdentity') IS NOT NULL)
	DROP TABLE dbo.ID_IsNotReallyIdentity;

CREATE TABLE dbo.ID_IsNotReallyIdentity (
    ID int NOT NULL
        CONSTRAINT PK_ID_IsNotReallyIdentity PRIMARY KEY CLUSTERED
    , Foo varchar(100) NOT NULL
    , Code varchar(10) NOT NULL
    , CONSTRAINT AlternateKey_aka_NaturalKey UNIQUE (Code)
);

GO

INSERT INTO dbo.IdentityIsPK (Foo)
SELECT 'Brad';

INSERT INTO dbo.IdentityNotPK (Foo)
SELECT 'Pitt';

INSERT INTO dbo.IdentityIsPK (Foo)
SELECT 'Kevin';

INSERT INTO dbo.IdentityNotPK (Foo)
SELECT 'Spacey';

INSERT INTO dbo.ID_IsNotReallyIdentity (ID, Foo, Code)
SELECT 1, 'See how I had to come up with this ID myself?', '07';
--> because it's not an identity, even though it is the primary key.

GO

SELECT * FROM dbo.IdentityIsPK
SELECT * FROM dbo.IdentityNotPK
SELECT * FROM dbo.ID_IsNotReallyIdentity
GO


--Can I update the ID in 'IdentityIsPK'?
UPDATE dbo.IdentityIsPK SET ID = 3 WHERE ID = 1;
--> Nope!
GO

--Can I update the ID in 'IdentityNotPK'?
UPDATE dbo.IdentityNotPK SET ID = 3 WHERE Foo = 'Spacey';
--> Nope! Not even if you say "WHERE <some other column>"
GO

--Can I update the ID in 'ID_IsNotReallyIdentity'?
UPDATE dbo.ID_IsNotReallyIdentity SET ID = 3 WHERE ID = 1;
--> Yep! Because [ID] is not an IDENTITY (it wasn't created with that property)
SELECT * FROM dbo.ID_IsNotReallyIdentity
GO


--Can I insert a made-up identity into either of the first two tables?
INSERT INTO dbo.IdentityIsPK (ID, Foo)
SELECT 3, 'this will fail, even though we are using the next "identity" value in sequence...'
GO
INSERT INTO dbo.IdentityNotPK (ID, Foo)
SELECT 3, '...SQL says "No, *I* am in charge of that value, silly!"'
GO

--> Yes, but we need to use that "SET IDENTITY_INSERT" statement to enable it.
SET IDENTITY_INSERT dbo.IdentityIsPK ON;

INSERT INTO dbo.IdentityIsPK (ID, Foo)
SELECT 3, 'what''s in' --Notice the double "apostrophe" character, aka the "single quote" -- that's how you insert ONE apostrophe/single-quote, because it's a reserved character in T-SQL, so when you want it literally in a string/varchar you have to double it up. Ask me more if you're curious.

--> remember to turn IDENTITY_INSERT back OFF when you're done!
SET IDENTITY_INSERT dbo.IdentityIsPK OFF;
GO

--> and now we'll do the other table
SET IDENTITY_INSERT dbo.IdentityNotPK ON;

INSERT INTO dbo.IdentityNotPK (ID, Foo)
SELECT 9, 'the box' --We can insert any Id/identity value we want, it doesn't have to be the next in the sequence!

SET IDENTITY_INSERT dbo.IdentityNotPK OFF;
GO

--> see the new records
SELECT * FROM dbo.IdentityIsPK
SELECT * FROM dbo.IdentityNotPK
GO


--And finally, just to finish the joke...
INSERT INTO dbo.ID_IsNotReallyIdentity (ID, Foo, Code)
SELECT 7, 'WHAT''S IN THE *#&$^@ BOX!?!', '07'
--> wait, that didn't work?? Oh, right, because we made "Code" a UNIQUE CONSTRAINT on this table, and it can't be the same as the existing record!
--> and obviously, [Id] needs to be different too, because it's still the primary key, even though WE are managing it and not SQL Server.
GO

--> So let's try that again.
INSERT INTO dbo.ID_IsNotReallyIdentity (ID, Foo, Code)
SELECT 7, 'WHAT''S IN THE *#&$^@ BOX!?!', 'SE7EN'
GO

--> and delete the other record just because
DELETE FROM dbo.ID_IsNotReallyIdentity
WHERE ID = 3 OR Code = '07'; --We can do this either way, on ID or Code, because they're both "unique" -- ID is unique because it's the primary key, while Code is unique because we created a UNIQUE CONSTRAINT on it.
GO

SELECT * FROM dbo.ID_IsNotReallyIdentity
GO


--Now clean up after ourselves
DROP TABLE dbo.IdentityIsPK;
GO
DROP TABLE dbo.IdentityNotPK;
GO
DROP TABLE dbo.ID_IsNotReallyIdentity;
GO