CODEWITHSUNDEEP
oracle-databaseforeign-keysrelational-database
diya
diya

Reputation: 3

Create table field with foreign key constraint

I want to create a table department:

COLUMN NAME   DATATYPE    SIZE   CONSTRAINT

dept_id       number      4      Primary key
prod_id       number      4      Foreign key

I tried this:

CREATE TABLE Department(
dept_id number(4) primary key,
prod_id number(4) foreign key);

It shows error. How can I add a foreign key constraint to this table?

Upvotes: 0

Views: 279

Answers (2)

APC
APC

Reputation: 146209

A foreign key defines a relationship between your table DEPARTMENT and another table with a primary key. It means, you cannot create a row in DEPARTMENT with a PROD_ID of 1234 unless there is a pre-existing row in the designated parent table with a value of 1234 as its primary key.

So do you have such an existing parent table? if so you need to include its name in the foreign key definition. Otherwise you must create it.

Let's say the parent table is PRODUCT:

create table product (
    prod_id number(4) primary key
    , name varchar2(32) not null 
);

Then you can create DEPARTMENT with a foreign key like this: 

CREATE TABLE Department(
    dept_id number(4) primary key,
    prod_id references PRODUCT );

Yep, that's all the syntax you need: it automatically creates a column PROD_ID with the same datatype and precision as the primary key column of the referenced table. More verbose syntax is available. Read the Oracle SQL documentation to find out more.

Upvotes: 2

Ely
Ely

Reputation: 11152

I assume that the other table is named other_table_name and that it contains a primary key named prod_id.

CREATE Department (
dept_id number(4) primary key,
prod_id number(4) REFERENCES other_table_name (prod_id)
);

or a different syntax

CREATE Department (
dept_id number(4) primary key,
prod_id number(4)

...
CONSTRAINT fk_prod_id
  FOREIGN KEY (prod_id)
  REFERENCES other_table_name (prod_id)
);

Upvotes: 0

Related Questions