CODEWITHSUNDEEP
sqloracle-database
Ahtisham
Ahtisham

Reputation: 10116

ORA-01748: only simple column names allowed here in Oracle

What I am trying to do ?

I am trying to create two tables and at the same time i am trying to link them together using foreign and primary keys. However I successfully create my parent table ( Student with primary key ) but failed to create child table ( Attendence with foreign key ).

What is the problem ?

I get the following error while creating Attendence table:

ERROR at line 5: ORA-01748: only simple column names allowed here

My code:

Student table:

create table Student (

            ST_ROLLNO  NUMBER(6) constraint s_pk primary key,
            ST_NAME    VARCHAR(30) not null,
            ST_ADDRESS  varchar(35) not null
);

Attendence table:

create table Attendence (

            ST_ROLLNO  NUMBER(6),
            ST_DATE    VARCHAR(30) not null,
            ST_PRESENT_ABSENT  varchar(1) not null,
            constraint f_pk Attendence.ST_ROLLNO foreign key references Student(ST_ROLLNO)
);

Upvotes: 3

Views: 31532

Answers (3)

user28639326
user28639326

Reputation: 1

Attendence.ST_ROLLNO - WRONG.

 But without using table name i am getting **invalid identifier** how it will be resolved

Upvotes: 0

Alex Poole
Alex Poole

Reputation: 191275

Your foreign key constraint syntax is wrong; it should be:

constraint f_pk foreign key (ST_ROLLNO) references Student(ST_ROLLNO)

You are preceding the FK column name with the table name, which is wrong in itself, but also have it in the wrong place.

create table Student (

            ST_ROLLNO  NUMBER(6) constraint s_pk primary key,
            ST_NAME    VARCHAR(30) not null,
            ST_ADDRESS  varchar(35) not null
);  

Table STUDENT created.

create table Attendence (

            ST_ROLLNO  NUMBER(6),
            ST_DATE    VARCHAR(30) not null,
            ST_PRESENT_ABSENT  varchar(1) not null,
            constraint f_pk foreign key (ST_ROLLNO) references Student(ST_ROLLNO)
);

Table ATTENDENCE created.

Upvotes: 5

Kaushik Nayak
Kaushik Nayak

Reputation: 31656

According to oracle documentation,

ORA ERR

ORA-01748 only simple column names allowed here

The following is the cause of this error:

This SQL statement does not allow a qualified column name, such as username.table.column or table.column.

Action you can take to resolve this issue: Remove the qualifications from the column and retry the operation.

In your case, you are trying to refer to the table name while defining a constraint -

Attendence.ST_ROLLNO - WRONG.

It must contain a simple name without the table name or schema name.

Upvotes: 5

Related Questions