SQL - Missing right bracket

Question

I am trying to run this script in Oracle 11g and get the following error, I don’t know where I am missing the paratytes or what error is, kindly help me figure this out.

Script:

CREATE TABLE User_Role ( user_role_id INT NOT NULL , Users_user_id INT FOREIGN KEY REFERENCES Users(user_id), User_Types_user_type VARCHAR(20) FOREIGN KEY REFERENCES User_Types(user_type), PRIMARY KEY(user_role_id) ) 

Error:

ORA-00907: missing right bracket

Answer 1

Delete FOREIGN KEY . Rewrite your CREATE TABLE statement as follows:

 CREATE TABLE User_Role ( user_role_id INT NOT NULL , Users_user_id INT REFERENCES Users(user_id), User_Types_user_type VARCHAR(20) REFERENCES User_Types(user_type), PRIMARY KEY(user_role_id) ) 

In this case, the constraint names will be generated by Oracle. If you want to give them more meaningful names, you can write the CREATE TABLE statement as follows:

  CREATE TABLE User_Role1 ( user_role_id INT NOT NULL , Users_user_id INT , User_Types_user_type VARCHAR(20) , constraint PK_YourTable PRIMARY KEY(user_role_id), constraint FK_Table_1 foreign key(Users_user_id) REFERENCES Users(user_id), constraint FK_Table_2 foreign key(User_Types_user_type) REFERENCES User_Types(user_type) ) 

Answer 2

It seems to me that you need to first define the columns and then add the FOREIGN KEY in the same way as you add the PRIMARY KEY , as shown below:

  CREATE TABLE User_Role ( user_role_id INT NOT NULL , Users_user_id INT, User_Types_user_type VARCHAR(20), FOREIGN KEY (Users_user_id) REFERENCES Users(user_id), FOREIGN KEY (User_Types_user_type) REFERENCES User_Types(user_type), PRIMARY KEY(user_role_id) ) 

Answer 3

remove int size and recompile

Example: -

 integer (20) not null integer not null User_Types_user_type VARCHAR(20), User_Types_user_type VARCHAR, 

Answer 4

You can specify inline foreign keys, you just need to remove the foreign key keyword:

 CREATE TABLE User_Role ( user_role_id INT NOT NULL , Users_user_id INT REFERENCES Users, User_Types_user_type VARCHAR(20) REFERENCES User_Types, PRIMARY KEY(user_role_id) ); 

SQLFiddle example: http://sqlfiddle.com/#!4/4ca9f/1

The list of primary key columns in the links section is optional. If you prefer, you can also write REFERENCES Users(user_id)

This format also has the disadvantage that constraint names will be generated by Oracle (with a meaningless name). To be able to correctly specify the constraint name for a foreign key, you need to use the syntax in the accepted answer.