CODEWITHSUNDEEP
sqloracle-databaseforeign-keyscreate-table
Federico Ctr
Federico Ctr

Reputation: 21

How to check foreign key constraint exists in oracle sql

Thank you very much first for your help. I'm here since I don't know how to program a constraint for the Video table, so that it checks if a category exists in the 'Categories' table before creating a new line inside the Video table.

Categories Table:

CREATE TABLE Categories(
cat_id integer,
cat_name varchar2(128)
CONSTRAINT pk_cat PRIMARY KEY (cat_id, cat_name) 
);

Video Table:

CREATE TABLE Video(
video_id integer PRIMARY KEY,
title varchar2(128) NOT NULL,
description varchar2(128) NOT NULL,
categorie varchar2(32),
CONSTRAINT fk_vid_cat FOREIGN KEY (categorie) REFERENCES Categories,
--CONSTRAINT ck_cat_exists CHECK categorie EXISTS (cat_name) REFERENCES Categories
) ;

I don't know if it can be done directly from here, thank you very much for your help again ...

Upvotes: 2

Views: 582

Answers (2)

GMB
GMB

Reputation: 222402

What you ask for is just what the foreign key contraint functionality provides.

I would start by changing the definition of the categories table so it uses cat_id only as primary key (while you also had cat_name in the primary key, which does not really make sense):

create table categories(
    cat_id integer,
    cat_name varchar2(128)
    constraint pk_cat primary key (cat_id) 
);

Then you can reference it in the video table:

create table video(
    video_id integer,
    title varchar2(128) not null,
    description varchar2(128) not null,
    cat_id integer,
    constraint pk_video primary key (video_id),
    constraint fk_vid_cat foreign key (cat_id) references categories(cat_id),
) ;

Note that I renamed the column and changed its datatype so it is consistent with the referenced column.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269483

You want the id not the name:

CREATE TABLE Videos (
    video_id integer PRIMARY KEY,
    title varchar2(128) NOT NULL,
    description varchar2(128) NOT NULL,
    cat_id integer,
    CONSTRAINT fk_vid_cat FOREIGN KEY (cat_id) REFERENCES Categories(cat_id)
) ;

If you want to ensure that each video has a category, then declare cat_id to be NOT NULL.

The purpose of having a primary key is so that you are not repeating the string values all over the database.

Upvotes: 0

Related Questions