0

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)
);
3
  • 1
    That syntax is just wrong. Which table and column is the primary key on, that your foreign key is supposed to reference? Commented Apr 22, 2016 at 13:16
  • Are you sure it's not falling over on constraint cust_Name not null. You've not allowed to name not null constraints, I'd have guessed you were looking to introduce maybe a unique constraint and left it half finished? Commented Apr 22, 2016 at 13:24
  • @Damien_The_Unbeliever - actually, you can; I didn't think you could either but that part is valid. It doesn't even mind the constraint name being the same as the column name. Commented Apr 22, 2016 at 14:38

2 Answers 2

1

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).

Sign up to request clarification or add additional context in comments.

Comments

0
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);

3 Comments

on the 2nd alter I now get an error saying missing left parenthesis? thanks for the help!
You need to specify the column name in the in the local table for the FK too.
i dont know your referenced column name, that is why i named it "referenced_col"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.