SQL 错误："ORA-00947: not enough values"

Question

为什么我没有得到足够的值错误，因为我使用自动递增，而不是在插入中添加任何值?

CREATE TABLE Customer(
customerId INTEGER GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1) primary key,
firstName VARCHAR2(25) not null,
middleName VARCHAR2(25),
lastName VARCHAR2(25) not null,
dateOfBirth DATE not null,
phoneNumber NUMBER(10) not null,
billingAddress VARCHAR2(35) not null
);
INSERT INTO Customer VALUES
('Sam', null, 'Siqi', (TO_DATE('18/12/1999', 'DD/MM/YYYY')),12345678, 
'Smith Street NSW');

Answer 1

这是您创建的一个表。它包含7列:

SQL> CREATE TABLE Customer
2  (
3     customerId       INTEGER
4                        GENERATED ALWAYS AS IDENTITY
5                           (      START WITH 1 INCREMENT BY 1)
6                        PRIMARY KEY,
7     firstName        VARCHAR2 (25) NOT NULL,
8     middleName       VARCHAR2 (25),
9     lastName         VARCHAR2 (25) NOT NULL,
10     dateOfBirth      DATE NOT NULL,
11     phoneNumber      NUMBER (10) NOT NULL,
12     billingAddress   VARCHAR2 (35) NOT NULL
13  );
Table created.

插入尝试失败:

SQL> INSERT INTO Customer
2       VALUES ('Sam',
3               NULL,
4               'Siqi',
5               (TO_DATE ('18/12/1999', 'DD/MM/YYYY')),
6               12345678,
7               'Smith Street NSW');
INSERT INTO Customer
*
ERROR at line 1:
ORA-00947: not enough values

为什么会失败?因为插入了6个值，而表有7列． Always, butAlways指定所有涉及的列。这包括你写的select(即避免select *)和-在这种情况下-insert。所以:

SQL> INSERT INTO Customer (firstname,
2                        middlename,
3                        lastname,
4                        dateofbirth,
5                        phonenumber,
6                        billingaddress)
7       VALUES ('Sam',
8               NULL,
9               'Siqi',
10               (TO_DATE ('18/12/1999', 'DD/MM/YYYY')),
11               12345678,
12               'Smith Street NSW');
1 row created.
SQL>

现在可以了

自动生成customerid并不意味着您可以跳过它。

---

是的，你可以使用default(如Anand的回答所建议的)，但这是-在我看来-不好的做法。就像我说的:总是给你正在处理的所有列命名。

SQL> INSERT INTO customer
2       VALUES (DEFAULT,
3               'Sam',
4               NULL,
5               'Siqi',
6               (TO_DATE ('18/12/1999', 'DD/MM/YYYY')),
7               12345678,
8               'Smith Street NSW');
1 row created.
SQL>

Answer 2

试试这个:

INSERT INTO Customer VALUES
(DEFAULT,'Sam', null, 'Siqi', (TO_DATE('18/12/1999', 'DD/MM/YYYY')),12345678, 
'Smith Street NSW');