Sql Server Auto Increment a Column Without Primary Key

SQL Server auto increment a column without primary key

Yes. There is no requirement that IDENTITY columns be made a primary key.

CREATE TABLE T
(
X INT PRIMARY KEY,
Y INT IDENTITY(1,1)
)

Though I'm not sure when this would be useful. If you have a natural key that you want to use as the PK then you would probably want to put a unique constraint on the surrogate alternate key anyway.

For purposes of setting up FK relationships SQL Server doesn't care if the column(s) is the PK or not it just requires a unique index on it/them.

How to Auto-Increment Non-Primary Key? - SQL Server

You can't have more than one identity column per table. I think your best bet would be to pull the PO data into a separate table, then relate the two with a FK column.

SupplierQuote
-------------
supplierQuoteID (PK/identity)
purchaseOrderID (FK to PurchaseOrder.purchaseOrderID)
otherColumn1

PurchaseOrder
-------------
purchaseOrderID (PK/identity)
otherColumn1

MySQL InnoDB: autoincrement non-primary key

Yes you can. You just need to make that column be an index.

CREATE TABLE `test` (
  `testID` int(11) NOT NULL,
  `string` varchar(45) DEFAULT NULL,
  `testInc` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`testID`),
  KEY `testInc` (`testInc`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

insert into test(
  testID,
 string
)
values (
1,
    'Hello'
);

insert into test( 
testID,
 string
)
values (
2,
    'world'
);

Will insert rows with auto-incrementing values for 'testInc'. However this is a really dumb thing to do.

You already said the right way to do it:

"Make the comment_id PK and enforce integrity through a unique index on book_id, timestamp, user_id."

That's exactly the way that you should be doing it. Not only does it provide you with a proper primary key key for the table which you will need for future queries, it also satisfies the principle of least astonishment.

PostgreSQL - create an auto-increment column for non-primary key

You may try making the item_id column SERIAL. I don't know whether or not it's possible to alter the current item_id column to make it serial, so we might have to drop that column and then add it back, something like this:

ALTER TABLE yourTable DROP COLUMN item_id;
ALTER TABLE yourTable ADD COLUMN item_id SERIAL;

If there is data in the item_id column already, it may not make sense from a serial point of view, so hopefully there is no harm in deleting it.

SQL-How to Insert Row Without Auto incrementing a ID Column?

If you are in Microsoft SQL Server, you can "turn off" the autoIncrementing feature by issuing the statement Set Identity_Insert [TableName] On, as in:

  Set Identity_Insert [TableName] On
  -- --------------------------------------------
  Insert TableName (pkCol, [OtherColumns])
  Values(pkValue, [OtherValues])
  -- ---- Don't forget to turn it back off ------
  Set Identity_Insert [TableName] Off

Can you use auto-increment in MySql with out it being the primary Key

A GUID value is intended to be unique across tables and even databases so, make the auto_increment column primary index and make a UNIQUE index for the GUID

MS SQL Auto Increment non Identity Column

This is after Update as discussed in the comments. It simply increments Version of this row.

ALTER TRIGGER triggerIncrementUpdate
ON Table1
AFTER UPDATE

AS
BEGIN

UPDATE Table1 SET Version += 1 FROM Table1 INNER JOIN INSERTED As I ON Table1.ID = I.ID

END

---

Related Topics