CODEWITHSUNDEEP
sqloracle-databasesyntax-errorddlcreate-table
Mohammed Arbaz
Mohammed Arbaz

Reputation: 11

SQL create 2 tables with constraints

Starting with first table: create the two tables with constraints

CREATE TABLE department
(
    depid varchar2(3) CONSTRAINT PKdepid PRIMARY KEY,
    dname varchar2(10) NOT NULL
);

First table is created.

Starting second table:

CREATE TABLE employee
(
    eid number CONSTRAINT PKEID PRIMARY KEY, 
    ename varchar2(10), 
    depid varchar2(3) 
        CONSTRAINT FKDEPID 
             FOREIGN KEY REFERENCE dep(depid),
    designation varchar2(10),
    salary number CHECK (salary > 10000),
    doj date
);

CREATE TABLE employee
(
    eid number CONSTRAINT PKEID PRIMARY KEY, 
    ename varchar2(10), 
    depid varchar2(3) 
        CONSTRAINT FKDEPID 
            FOREIGN KEY REFERENCE dep(depid),
    designation varchar2(10),
    salary number CHECK (salary > 10000),
    doj date
)

ERROR at line 1:

ORA-02253: constraint specification not allowed here

And in second condition applied

CREATE TABLE employee
(
    eid number CONSTRAINT PKEID PRIMARY KEY, 
    ename varchar2(10), 
    depid varchar2(3) 
         CONSTRAIN FKDEPID 
             FOREIGN KEY REFERENCE dep(depid),
    designation varchar2(10),
    salary number CHECK (salary > 10000),
    doj date
);

CREATE TABLE employee
(
    eid number CONSTRAINT PKEID PRIMARY KEY, 
    ename varchar2(10), 
    depid varchar2(3) 
         CONSTRAIN FKDEPID 
             FOREIGN KEY REFERENCE dep(depid),
    designation varchar2(10),
    salary number CHECK (salary > 10000),
    doj date
)

ERROR at line 1:

ORA-00907: missing right parenthesis

I just wanted to create table

Upvotes: 0

Views: 504

Answers (1)

Mureinik
Mureinik

Reputation: 311308

You shouldn't specify foreign key in an inline foreign key definition, just references (not reference as you currently have). Also, note your statement references the table dep while your other table is in fact called department:

CREATE TABLE employee (
  eid NUMBER CONSTRAINT pkeid PRIMARY KEY, 
  ename VARCHAR2(10), 
  depid VARCHAR2(3) CONSTRAINT fkdepid REFERENCES department(depid), -- Here!
  designation VARCHAR2(10),
  salary NUMBER CHECK(salary>10000),
  doj DATE);

Upvotes: 1

Related Questions