SQL Primary Key- Tutorial using Oracle
Creating a student table with a primary key
SQL> create table student(
2 regno varchar2(10) constraint student_regno_pr primary key,
3 name varchar2(10),
4 marks number);
Output
Table created.
Dropping a primary key constraint using the constraint name
SQL> alter table student drop constraint student_regno_pr;
Table altered.
SQL> desc student;
Output
Name Null? Type
----------------------------------------- -------- ----------------------------
REGNO VARCHAR2(10)
NAME VARCHAR2(10)
MARKS NUMBER
SQL>
Adding a primary key constraint to an existing table
SQL> alter table student add constraint student_regno_pr primary key(regno);
Table altered.
SQL> create table student1(
2 regno varchar2(10),
3 name varchar2(10),
4 constraint student1_name_pr primary key(regno));
Table created.
Applying a primary key constraint at the column level
SQL> create table student1(
2 regno varchar2(10),
3 name varchar2(10),
4 constraint student1_name_pr primary key(regno));
Output
Table created.
Creating a table by name registration
SQL> create table registration(student_regno varchar2(10),course_id varchar2(10));
Output
Table created.
inserting values into the registration table
SQL> insert into registration values ('satish','dbms');
1 row created.
SQL> insert into registration values ('satish','os');
1 row created.
SQL> insert into registration values ('satish','networks');
1 row created.
SQL> insert into registration values ('ram','networks');
1 row created.
SQL> insert into registration values ('ram','dbms');
1 row created.
SQL> insert into registration values ('ram','os');
1 row created.
SQL> insert into registration values ('sam','networks');
1 row created.
SQL> insert into registration values ('sam','dbms');
1 row created.
SQL> select * from registration;
Output
STUDENT_RE COURSE_ID
---------- ----------
satish dbms
satish os
satish networks
ram networks
ram dbms
ram os
sam networks
sam dbms
8 rows selected.
Defining a composite primary key for the registration table
SQL> alter table registration add constraint registration_regcourse_pr primary key(student_regno,course_id);
Output
Table altered.