Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Error ORA-00984: Column Not Allowed Here - Where Did i Go Wrong? - Oracle Relational DB

Posted on 2009-12-18
11
Medium Priority
?
1,283 Views
Last Modified: 2013-12-18
I can not run the code below, i keep getting this error:
ORA-00984: column not allowed here

I don't see any mistake in there! Can you please help me...
CREATE TABLE Client
(
        clientID                NUMBER(7)           		   , CONSTRAINT client_clientid_pk                         PRIMARY KEY(clientID)                                                ,
        firstName               VARCHAR2(50)                     CONSTRAINT client_firstname_nn                        NOT NULL                                                             ,
        lastName                VARCHAR2(50)                     CONSTRAINT client_lastname_nn                         NOT NULL                                                             ,
        email	                VARCHAR2(50)    		   					   	                                                                                                            ,
        address1                VARCHAR2(100)                    CONSTRAINT client_address1_nn                         NOT NULL                                                             ,
        address2                VARCHAR2(100)                                                                                                                                               ,
        cityID                  NUMBER(7)           		     CONSTRAINT client_cityid_nn	                       NOT NULL                                                             ,
															     CONSTRAINT client_cityid_fk                           FOREIGN KEY(cityID) REFERENCES City(cityID)			                ,			  
        provinceID              NUMBER(7)    	                 CONSTRAINT client_provinceid_nn                       NOT NULL                                                             ,
															     CONSTRAINT client_provinceid_fk                       FOREIGN KEY(provinceID) REFERENCES Province(provinceID)              ,
        postalCode              VARCHAR2(50)                     CONSTRAINT client_postalcode_nn                       NOT NULL                                                             ,
		countryID               NUMBER(7)       		         CONSTRAINT client_countryid_nn	                       NOT NULL                                                             ,
															     CONSTRAINT client_countryid_fk                        FOREIGN KEY(countryID) REFERENCES Country(countryID)                 ,
		gender		            CHAR(1)      				     CONSTRAINT client_gender_ck                           CHECK(gender IN ('M', 'F'))                                          ,
		clientStatus            VARCHAR2(10)    		         CONSTRAINT client_clientstatus_nn         			   NOT NULL                                                             ,
		numOfFamilyMembers      NUMBER(1)		DEFAULT 0  	     CONSTRAINT client_numoffamilymembers_nn   	           NOT NULL                                                             ,
		isStayingIn             BOOLEAN    		DEFAULT FALSE    CONSTRAINT client_isstayingin_nn                      NOT NULL
);

Open in new window

0
Comment
Question by:F-J-K
  • 6
  • 4
11 Comments
 
LVL 18

Expert Comment

by:sventhan
ID: 26082654
Is CONSTRAINT one of your column name? Its a oracle reserved word. Can you change to something else and try?
0
 
LVL 18

Expert Comment

by:sventhan
ID: 26082716
The create table syntax is wrong. The statement is messed up with command and constraint key word.
 
0
 
LVL 18

Expert Comment

by:sventhan
ID: 26082747
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 18

Expert Comment

by:sventhan
ID: 26082774
ooops sorry. I take all my comments back. There is something else going on.
0
 
LVL 18

Accepted Solution

by:
sventhan earned 1000 total points
ID: 26082861
Okay --

    isStayingIn     BOOLEAN
            DEFAULT FALSE CONSTRAINT client_isstayingin_nn NOT NULL

BOOLEAN is not allowed when you creating the table. Its only for PL/SQL.


0
 
LVL 15

Assisted Solution

by:Franck Pachot
Franck Pachot earned 1000 total points
ID: 26083748
Hi,
When you run that statement from sqlplus you will see that error occurs at FALSE. Oracle thinks that it is a column name because it is not a sql word and raises an error. If you change that then you will have an error on BOOLEAN as it is not a datatype.
Booleans are often implemented as CHAR(1) that has 'Y'/'N' values, or as a number with 0/1
Regards,
Franck.
0
 
LVL 1

Author Comment

by:F-J-K
ID: 26083809
Thanks for your response. What is the alternative? I couldn't find any
0
 
LVL 1

Author Comment

by:F-J-K
ID: 26083817
frankpachot:

Thanks. I got it
0
 
LVL 18

Expert Comment

by:sventhan
ID: 26083837
What are the values you planning to store into that BOOLEAN column?

0
 
LVL 1

Author Comment

by:F-J-K
ID: 26084352
Yes or No ... Y or N ....

0
 
LVL 1

Author Closing Comment

by:F-J-K
ID: 31667861
Both responses have helped me
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question