[go: up one dir, main page]
Scribd
Upload
21 views3 pages

Practical 6

The document outlines the syntax and methods for including data integrity constraints, such as NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, and CHECK, in database tables. It provides examples of SQL commands to create and alter tables, add constraints, and confirm their existence. Additionally, it includes specific queries for managing employee and department tables, including adding a salary column to the employee table.

Uploaded by

itsdeva2608
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
21 views3 pages

Practical 6

The document outlines the syntax and methods for including data integrity constraints, such as NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, and CHECK, in database tables. It provides examples of SQL commands to create and alter tables, add constraints, and confirm their existence. Additionally, it includes specific queries for managing employee and department tables, including adding a salary column to the employee table.

Uploaded by

itsdeva2608
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 3

Practical no.

6:

Including Constraints (primary key) on Tables

SYNTAX:
Data Integrity Constraints
Constraint Description
NOT NULL Specifies that this column may not contain a null value
UNIQUE Specifies a column or combination of columns whose values must be
unique for all rows in the table
PRIMARY Uniquely identifies each row of the table
KEY
FOREIGN Establishes and enforces a foreign key relationship between the column and
KEY a column of the referenced table.
CHECK Specifies a condition that must be true.
Defining Constraints
CREATE TABLE [ schema.] table
(column datatype [DEFAULT expr]
[column_constraint],
…
[table_constraint] [,…] );
In the syntax:
schema is the same as the owner’s name
table is the name of the table
DEFAULT expr specifies a default value if a value is omitted in the INSERT statement
column is the name of the column
datatype is the column’s datatype and length
column_constraint is an integrity constraint as part of the column definition
table_constraint is an integrity constraint as part of the table definition
If the constraints are to be defined when a table is already existing, ALTER TABLE command has to
be used.
ALTER TABLE table
ADD [ CONSTRAINT constraint ] type (column);

In the syntax:
table is the name of the table
constraint is the name of the constraint
type is the constraint type
column is the name of the column affected by the constraint

QUERIES :
6.1 Add a table-level PRIMARY KEY constraint to the EMPLOYEE table using the ID column.
The constraint should be named at creation.

ALTER TABLE employee


ADD CONSTRAINT employee_id_pk PRIMARY KEY (id);

6.2 Create a PRIMARY KEY constraint on the DEPARTMENT table using the ID column.
The constraint should be named at creation.

ALTER TABLE department


ADD CONSTRAINT department_id_pk PRIMARY KEY ( id);

6.3 Add a foreign key reference on the EMPLOYEE table that will ensure that the employee
is not assigned to a nonexistent department.

ALTER TABLE employee


ADD CONSTRAINT employee_dept_id_fk FOREIGN KEY (dept_id)
REFERENCES department(id);

6.4 Confirm that the constraint were added by querying USER_CONSTRAINTS.

SELECT constraint_name, constraint_type


FROM user_constraints
WHERE table_name IN(‘EMPLOYEE’, ‘DEPARTMENT’);

6.5 Display the object names and types from the USER_OBJECTS data dictionary view for
EMPLOYEE and DEPARTMENT tables. You may want to format the columns for
readability.

COLUMN object_name FORMAT A30


COLUMN object_type FORMAT A30
SELECT object_name, object_type
FROM user_objects
WHERE object_name LIKE ‘EMPLOYEE%’
OR object_name LIKE ‘DEPARTMENT%’;

6.6 Modify the EMPLOYEE table. Add a SALARY column of NUMBER datatype, precision 7.

ALTER TABLE employee


ADD ( salary NUMBER(7) );

You might also like