{"id":3991,"date":"2025-06-07T09:06:41","date_gmt":"2025-06-07T09:06:41","guid":{"rendered":"https:\/\/w3buddy.com\/?post_type=cposts&#038;p=3991"},"modified":"2025-06-07T09:06:42","modified_gmt":"2025-06-07T09:06:42","slug":"sql-auto_increment-identity","status":"publish","type":"cposts","link":"https:\/\/w3buddy.com\/blog\/notes\/sql\/sql-auto_increment-identity\/","title":{"rendered":"SQL AUTO_INCREMENT \/ IDENTITY"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">To auto-generate unique values (usually for primary keys), databases offer built-in features like <code>AUTO_INCREMENT<\/code> (MySQL), <code>IDENTITY<\/code> (SQL Server), and <code>GENERATED AS IDENTITY<\/code> (PostgreSQL, Oracle).<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">\ud83d\udd39 <strong>Basic Usage by DBMS<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code><code>-- MySQL\nCREATE TABLE employees (\n  id INT AUTO_INCREMENT PRIMARY KEY,\n  name VARCHAR(100)\n);\n\n-- SQL Server\nCREATE TABLE employees (\n  id INT IDENTITY(1,1) PRIMARY KEY, -- (start, increment)\n  name VARCHAR(100)\n);\n\n-- PostgreSQL &amp; Oracle (modern versions)\nCREATE TABLE employees (\n  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n  name VARCHAR(100)\n);<\/code><\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">\ud83d\udd39 <strong>Insert Example<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code><code>INSERT INTO employees (name) VALUES ('Alice');\n-- No need to provide `id`; it auto-generates<\/code><\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">\ud83d\udd39 <strong>Controlling Identity (Optional)<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>MySQL<\/strong>: You can set <code>AUTO_INCREMENT = value<\/code> to restart or offset.<\/li>\n\n\n\n<li><strong>SQL Server<\/strong>: Use <code>DBCC CHECKIDENT<\/code> to reseed identity values.<\/li>\n\n\n\n<li><strong>PostgreSQL<\/strong>: Use sequences behind the scenes (can <code>ALTER SEQUENCE<\/code>).<\/li>\n\n\n\n<li><strong>Oracle<\/strong>: Uses sequences automatically with <code>IDENTITY<\/code>.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">\ud83e\udde0 <strong>Quick Recap<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-table has-small-font-size\"><table><thead><tr><th>Key Point<\/th><th>Explanation<\/th><\/tr><\/thead><tbody><tr><td>Purpose<\/td><td>Auto-generate unique IDs (usually for primary key)<\/td><\/tr><tr><td>MySQL Syntax<\/td><td><code>AUTO_INCREMENT<\/code><\/td><\/tr><tr><td>SQL Server<\/td><td><code>IDENTITY(start, increment)<\/code><\/td><\/tr><tr><td>PostgreSQL\/Oracle<\/td><td><code>GENERATED AS IDENTITY<\/code><\/td><\/tr><tr><td>Insert Simplicity<\/td><td>No need to provide the ID during insert<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">\ud83d\udca1 Use auto-increment\/identity columns to simplify primary key management and ensure uniqueness without manual effort.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>To auto-generate unique values (usually for primary keys), databases offer built-in features like AUTO_INCREMENT (MySQL), IDENTITY (SQL Server), and GENERATED AS IDENTITY (PostgreSQL, Oracle). \ud83d\udd39 Basic Usage by DBMS \ud83d\udd39 Insert Example \ud83d\udd39 Controlling Identity (Optional) \ud83e\udde0 Quick Recap Key Point Explanation Purpose Auto-generate unique IDs (usually for primary key) MySQL Syntax AUTO_INCREMENT SQL Server [&hellip;]<\/p>\n","protected":false},"template":"","meta":{"googlesitekit_rrm_CAowu461DA:productID":""},"categories":[952,979],"class_list":["post-3991","cposts","type-cposts","status-publish","hentry","category-notes","category-sql"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts\/3991","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/cposts"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=3991"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=3991"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}