CODEWITHSUNDEEP
sqloracle-databasedatabase-sequence
Alex McGraw
Alex McGraw

Reputation: 109

Can't figure out what datatype is incorrect in my SQL table

I am trying to create a SQL table, but I keep getting this error.

Error report -
ORA-00902: invalid datatype
00902. 00000 -  "invalid datatype"

Here is my code.

CREATE TABLE viewers
(
    user_id     SEQUENCE    PRIMARY KEY,
    first_name  VARCHAR2(30),
    last_name   VARCHAR2(40) NOT NULL,
    email       VARCHAR2(40) CHECK(LENGTH(email) > 8),
    DOB         DATE,
    CONSTRAINT contact_email UNIQUE (email)
);

CREATE SEQUENCE user_id_seq
    START WITH 100000   INCREMENT BY 1
    MINVALUE 100000     MAXVALUE 999999;

Upvotes: 1

Views: 155

Answers (1)

Sujitmohanty30
Sujitmohanty30

Reputation: 3316

Your intention is correct but the usage syntactically is not.

You need to create the sequence as first step,

create sequence seq_user_id;

CREATE TABLE viewers
(
    user_id     number default seq_user_id.nextval PRIMARY KEY,
    first_name  VARCHAR2(30),
    last_name   VARCHAR2(40) NOT NULL,
    email       VARCHAR2(40) CHECK(LENGTH(email) > 8),
    DOB         DATE,
    CONSTRAINT contact_email UNIQUE (email)
);

P.S. If you are using 12c and above then consider using identity column which is a nice feature. I am providing you the link.

Upvotes: 4

Related Questions