Creating a Table in SQL with constraints

Question

I am trying to create a table. I am unable to compile it successfully. Could someone please point me in the right direction

Here is my code:

 CREATE TABLE TESTING_TODAY
 (
 EMP_NUM NUMBER(5, 0) NOT NULL, 
 RTG_CODE CHAR(5 BYTE) NOT NULL,
 EARNED_DATE DATE NOT NULL
 CONSTRAINT PK_TESTING_TODAY PRIMARY KEY CLUSTERED 
 (
   [EMP_NUM] ASC,
   [RTG_CODE] ASC
 )
 );
 COMMIT;

If you're "unable to compile it", presumably you're getting some sort of error. It would be awfully helpful to specify that error. You haven't told us what database you're connected to. You've tagged this for SQL Developer which would imply an Oracle database but the SQL you've posted definitely isn't Oracle DDL. — Justin Cave
– Justin Cave, Commented May 5, 2014 at 22:37
@Justin CaveI am using Oracle SQL Developer as I tagged it. The error is "Error at Command Line : 6 Column : 12 Error report - SQL Error: ORA-02250: missing or invalid constraint name 02250. 00000 - "missing or invalid constraint name" *Cause: The constraint name is missing or invalid. *Action: Specify a valid identifier name for the constraint name." — TitanC
– TitanC, Commented May 5, 2014 at 22:39
In future, read the error message. (i.e. learn to fish, you'll feed yourself for a lifetime) — Jeffrey Kemp
– Jeffrey Kemp, Commented May 7, 2014 at 3:21

user330315 · Accepted Answer · 2014-05-05 22:41:25Z

3

CLUSTERED is invalid in Oracle and [,] are invalid in SQL identifiers:

CREATE TABLE TESTING_TODAY
(
   EMP_NUM NUMBER(5, 0) NOT NULL, 
   RTG_CODE CHAR(5 BYTE) NOT NULL,
   EARNED_DATE DATE NOT NULL,
   CONSTRAINT PK_TESTING_TODAY PRIMARY KEY (EMP_NUM,RTG_CODE)
);
COMMIT;

More details about valid SQL identifiers:
http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements008.htm#SQLRF00223
Full documentation for the CREATE TABLE statement:
http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_7002.htm#SQLRF01402

answered May 5, 2014 at 22:41

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

TitanC Over a year ago

Thank you. I was pointed in the wrong direction earlier by a friend. Thanks for the clarification and good articles.

Collectives™ on Stack Overflow

Creating a Table in SQL with constraints

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related