Create Table

Nakuru1234
Nakuru1234 used Ask the Experts™
on
Hello,

I am trying to create a new table but I keep getting this error message "No Columns were selected in the Primary key constraint" I have never experienced this error before, please can you help me?

TIA,
N
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
David VanZandtOracle Database Administrator III

Commented:
Post the statement and database version.  It's very difficult to triage an Oracle error without the ORA- number that comes with it.
Are you sure that's a valid error? I have never seen it either...

Author

Commented:
11.2.0.2

CREATE TABLE UTG.TRIGGER
(
  TG_ID           VARCHAR[30]                                                                                 NOT NULL,
  TG_TYPE         VARCHAR[30]                                                                                NULL,
                                                                     
 
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING;


ALTER TABLE UTG.TRIGGER ADD (
  CONSTRAINT TG_ID_PK
 PRIMARY KEY
 (TG_ID));
OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
You cannot use TRIGGER as a talbe name in Oracle.  It is a reserved word.  Also, Oralce does not use square brackets.

Assuming you deleted a few coulumns that accounts for the comma, the following code runs for me in 11.2.0.2.

You should also use VARCHAR2, not VARCHAR.

CREATE TABLE myTRIGGER
(
  TG_ID           VARCHAR2(30)      NOT NULL,
  TG_TYPE         VARCHAR2(30)    NULL
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
NOMONITORING;


ALTER TABLE myTRIGGER ADD (
  CONSTRAINT TG_ID_PK
 PRIMARY KEY
 (TG_ID));

Open in new window

Author

Commented:
This is the correct statement...

CREATE TABLE UTG.TRIGGER
(
  TG_ID           VARCHAR[30]                                                                                NOT NULL,
  TG_TYPE         VARCHAR[30]                                                                                NOT NULL,
 
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING;


ALTER TABLE UTG.TRIGGER ADD (
  CONSTRAINT TRIGGER_PK
 PRIMARY KEY
 (TG_ID));
Most Valuable Expert 2011
Top Expert 2012
Commented:
you have several syntax errors there

TRIGGER is not a valid table name

[]  should be ()

you have an extra comma after the last null


not "wrong" but not recommended  is the use of VARCHAR

oracle recommends using VARCHAR2
David VanZandtOracle Database Administrator III
Commented:
Perhaps instead of "VARCHAR[30] ", "VARCHAR2(30)"
Most Valuable Expert 2011
Top Expert 2012
Commented:
after fixing all of those errors, the script you provided runs fine for me  

I used triggerx as the name in my test

Author

Commented:
Ok. I understand. I will try your method. Thank you.

TIA,
N

Author

Commented:
I changed the table name and used "VARCHAR2(30)" as you recommended and it worked! Thank you!

TIA,
N

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial