Alter table to add primary key and alter another table to add foreign key : Add Foreign Key « Constraints « Oracle PL / SQL






Alter table to add primary key and alter another table to add foreign key

   
SQL>
SQL>
SQL> create table subjects (
  2  subject_id    number not null,
  3  subject_name  varchar2(30) not null,
  4  description   varchar2(4000)
  5  )
  6  tablespace users;

Table created.

SQL>
SQL> alter table subjects
  2  add constraint pk_subjects primary key (subject_id);

Table altered.

SQL>
SQL> create table courses (
  2  course_id   number not null,
  3  course_name varchar2(60) not null,
  4  subject_id  number not null,
  5  duration    number(2),
  6  skill_lvl   varchar2(12) not null
  7  )
  8  tablespace users;

Table created.

SQL>
SQL> alter table courses
  2  add constraint pk_courses
  3  primary key (course_id);

Table altered.

SQL>
SQL> alter table courses
  2  add constraint fk_course_subj
  3  foreign key (subject_id) references subjects (subject_id);

Table altered.

SQL>
SQL> alter table courses
  2  add constraint ck_level check(
  3  skill_lvl in ('BEGINNER', 'INTERMEDIATE', 'ADVANCED')
  4  );

Table altered.

SQL>
SQL> drop table courses cascade constraints;

Table dropped.

SQL> drop table subjects cascade constraints;

Table dropped.

SQL>

   
    
  








Related examples in the same category

1.Add Foreign Primary Key
2.syntax to reference foreign key
3.Many to many using a primary-key and foreign-key relationship
4.Three foreign keys in a table
5.One to many using a primary-key and foreign-key relationship