CODEWITHSUNDEEP
sqloracle-databaseconstraints
Tom Maughan
Tom Maughan

Reputation: 1

Constraint error while creating a table: "Constraint specification not allowed here"

I'm trying to state that the funtom_customer table is a foreign key to the funtom_employee table...

create table Funtom_customer
(
    cust_ID         number(3) constraint cust_ID primary key,
    cust_Name       varchar2(50) constraint cust_Name not null,
    cust_Contact    varchar2(50),   
    cust_Acmanager  number(3) 
         CONSTRAINT cust_Acmanager FOREIGN KEY (funtom_employee),
    cust_Addr1      varchar2(50),
    cust_Addr2      varchar2(50),
    cust_Addr3      varchar2(50),
    cust_Addrpc     varchar2(10)
);

Upvotes: 0

Views: 476

Answers (2)

Alex Poole
Alex Poole

Reputation: 191235

You syntax is malformed in several ways. Assuming the primary key in the funtom_employee table is called emp_id, you can either define the constraints in-line:

create table funtom_customer
(
  cust_id         number(3) primary key,
  cust_name       varchar2(50) not null,
  cust_contact    varchar2(50), 
  cust_acmanager  number(3) references funtom_employee (emp_id),
  cust_addr1      varchar2(50),
  cust_addr2      varchar2(50),
  cust_addr3      varchar2(50),
  cust_addrpc     varchar2(10)
);

... which will cause the system to generate the constraint names, or specify the, out-of-line as named constraints:

create table funtom_customer
(
  cust_id         number(3),
  cust_name       varchar2(50) not null,
  cust_contact    varchar2(50), 
  cust_acmanager  number(3),
  cust_addr1      varchar2(50),
  cust_addr2      varchar2(50),
  cust_addr3      varchar2(50),
  cust_addrpc     varchar2(10),
  constraint pk_funtom_customer primary key (cust_id),
  constraint fk_funtom_customer_acmanager foreign key (cust_acmanager)
    references funtom_employee (emp_id)
);

You can also create table and add the constraints afterwards, as @Thomas has shown.

You can name a not-null constraint but it's unusual; you can also have a named constraint that checks the column is not null, but then it wouldn't be shown as not nullable in the data dictionary (e.g. when you describe the table).

Upvotes: 1

Thomas
Thomas

Reputation: 366

create table Funtom_customer
(
cust_ID       number(3) not null,
cust_Name     varchar2(50) not null,
cust_Contact      varchar2(50), 
cust_Acmanager    number(3)   ,
cust_Addr1    varchar2(50),
cust_Addr2    varchar2(50),
cust_Addr3    varchar2(50),
cust_Addrpc   varchar2(10)
)
;


alter table Funtom_customer add constraint pk_cust_ID  primary key (cust_ID);
alter table Funtom_customer add constraint fk_cust_Acmanager foreign key (cust_Acmanager) references funtom_employee(referenced_col);

Upvotes: 0

Related Questions