SQL建表IDENTITY报错，可用什么替换？附完整代码求助

Hey there, I feel your pain—spending 3 days on this is no fun, let's get this sorted out right away.

First off, the IDENTITY keyword is only supported in Microsoft SQL Server (and Sybase). If you're using MySQL, PostgreSQL, Oracle, SQLite, or any other database system, that's exactly why you're getting the "IDENTITY is not valid input" error. Below are the replacements for the most common databases, plus fixes for other syntax bugs in your original code that would've caused issues too.

1. Replace IDENTITY Based on Your Database #

MySQL/MariaDB #

Use AUTO_INCREMENT instead, placed at the end of the column definition:

CustomerID Int NOT NULL AUTO_INCREMENT

For the INVOICE table starting at 35000, set the starting value separately after creating the table:

ALTER TABLE INVOICE AUTO_INCREMENT = 35000;

PostgreSQL #

Use standard SQL's GENERATED AS IDENTITY (recommended for modern PostgreSQL) or the older SERIAL shorthand:

-- Modern approach (PostgreSQL 10+)
CustomerID Int NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1)
-- Older shorthand
CustomerID SERIAL NOT NULL

For the INVOICE table starting at 35000:

InvoiceNumber Int NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 35000 INCREMENT BY 1)

Oracle (12c+) #

Oracle supports standard GENERATED AS IDENTITY too:

CustomerID Int NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1)

If you're on a pre-12c Oracle version, you'll need to create a sequence and trigger manually—just let me know if you need that code.

SQLite #

SQLite simplifies this with INTEGER PRIMARY KEY AUTOINCREMENT (note: INTEGER PRIMARY KEY auto-increments by default; AUTOINCREMENT adds a strict uniqueness constraint):

CustomerID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT

2. Fix Other Syntax Errors in Your Original Code #

Your schema has several small typos that would've caused failures even after fixing IDENTITY:

• Missing commas after column definitions (e.g., StreetAddress Char(35) NULL in the Customer table)
• Duplicate foreign key constraint name in the CONTACT table (plus a wrong reference to SEMINAR(CustomerID) instead of SEMINAR(SeminarID))
• Extra parentheses in the PRODUCT table's CHECK constraint
• Missing commas between foreign key constraints in SEMINAR_CUSTOMER and LINE_ITEM tables

Corrected Schema Example (MySQL Version) #

Here's your full schema with IDENTITY replaced by AUTO_INCREMENT and all typos fixed:

CREATE TABLE Customer(
    CustomerID Int NOT NULL AUTO_INCREMENT,
    LastName Char(25) NOT NULL,
    FirstName Char(25) NOT NULL,
    EmailAddress VarChar(100) NOT NULL,
    EncryptedPassword VarChar(50) NULL,
    Phone Char(12) NOT NULL,
    StreetAddress Char(35) NULL,
    City Char(35) NULL DEFAULT 'Dallas',
    [State] Char(2) NULL DEFAULT 'TX',
    ZIP Char(10) NULL DEFAULT '75201',
    CONSTRAINT CUSTOMER_PK PRIMARY KEY(CUSTOMERID),
    CONSTRAINT CUSTOMER_EMAIL UNIQUE(EMAILADDRESS)
);

CREATE TABLE SEMINAR(
    SeminarID INT NOT NULL AUTO_INCREMENT,
    SeminarDate Date NOT NULL,
    SeminarTime Time NOT NULL,
    Location VarChar(100) NOT NULL,
    SeminarTitle VarChar(100),
    CONSTRAINT SEMINAR_PK PRIMARY KEY(SeminarID)
);

CREATE TABLE SEMINAR_CUSTOMER(
    SeminarID Int NOT NULL,
    CustomerID Int NOT NULL,
    CONSTRAINT S_C_PK PRIMARY KEY(SeminarID, CustomerID),
    CONSTRAINT S_C_SEMINAR_FK FOREIGN KEY(SeminarID) REFERENCES SEMINAR(SeminarID) ON UPDATE NO ACTION ON DELETE NO ACTION,
    CONSTRAINT S_C_CUSTOMER_FK FOREIGN KEY (CustomerID) REFERENCES CUSTOMER(CustomerID) ON UPDATE NO ACTION ON DELETE NO ACTION
);

CREATE TABLE CONTACT(
    CustomerID Int NOT NULL,
    ContactNumber Int NOT NULL,
    ContactDate Date NOT NULL,
    ContactType VarChar(30) NOT NULL,
    SeminarID Int NULL,
    CONSTRAINT CONTACT_PK PRIMARY KEY(CustomerID, ContactNumber),
    CONSTRAINT CONTACT_ContactType CHECK (ContactType IN ('Seminar', 'WebAccountCreation', 'WebPurchase', 'EmailAccountMessage', 'EmailSeminarMessage', 'EmailPurchaseMessage', 'EmailMessageExchange', 'FormLetterSeminar', 'PhoneConversation')),
    CONSTRAINT CONTACT_SEMINAR_FK FOREIGN KEY(SeminarID) REFERENCES SEMINAR(SeminarID) ON UPDATE NO ACTION ON DELETE NO ACTION,
    CONSTRAINT CONTACT_CUSTOMER_FK FOREIGN KEY(CustomerID) REFERENCES CUSTOMER(CustomerID) ON UPDATE NO ACTION ON DELETE NO ACTION
);

CREATE TABLE PRODUCT(
    ProductNumber Char(35) NOT NULL,
    ProductType Char(24) NOT NULL,
    ProductDescription VarChar(100) NOT NULL,
    UnitPrice Numeric(9,2) NOT NULL,
    QuantityOnHand Int NULL,
    CONSTRAINT PRODUCT_PK PRIMARY KEY(ProductNumber),
    CONSTRAINT PRODUCT_ProductType CHECK (ProductType IN ('Video', 'Video Companion', 'Book'))
);

CREATE TABLE INVOICE(
    InvoiceNumber Int NOT NULL AUTO_INCREMENT,
    InvoiceDate Date NOT NULL,
    CustomerID Int NOT NULL,
    PaymentType Char(25) NOT NULL DEFAULT 'Cash',
    SubTotal Numeric(9,2) NULL,
    Shipping Numeric(9,2) NULL,
    Tax Numeric(9,2) NULL,
    Total Numeric(9,2) NULL,
    CONSTRAINT INVOICE_PK PRIMARY KEY (InvoiceNumber),
    CONSTRAINT INVOICE_PaymentType CHECK (PaymentType IN ('VISA', 'MasterCard', 'American Express', 'PayPal', 'Check', 'Cash')),
    CONSTRAINT INVOICE_CUSTOMER_FK FOREIGN KEY(CustomerID) REFERENCES CUSTOMER(CustomerID) ON UPDATE NO ACTION ON DELETE NO ACTION
);
-- Set starting value for InvoiceNumber
ALTER TABLE INVOICE AUTO_INCREMENT = 35000;

CREATE TABLE LINE_ITEM(
    InvoiceNumber Int NOT NULL,
    LineNumber Int NOT NULL,
    ProductNumber Char(35) NOT NULL,
    Quantity Int NOT NULL,
    UnitPrice Numeric(9,2) NULL,
    Total Numeric(9,2) NULL,
    CONSTRAINT LINE_ITEM_PK PRIMARY KEY (InvoiceNumber, LineNumber),
    CONSTRAINT L_I_INVOICE_FK FOREIGN KEY (InvoiceNumber) REFERENCES INVOICE(InvoiceNumber) ON UPDATE NO ACTION ON DELETE NO ACTION,
    CONSTRAINT L_I_PRODUCT_FK FOREIGN KEY (ProductNumber) REFERENCES Product (ProductNumber) ON UPDATE NO ACTION ON DELETE NO ACTION
);

Just swap out the auto-increment syntax for whichever database you're using, and this should run smoothly.

内容的提问来源于stack exchange，提问作者Raygan Dunlap