Constraints are of two types. They are
i) Table Level Constraints – The constraints can be specified after all the columns are defined. This is called table-level definition.
ii) Column Level Constraints - The constraints can be specified immediately after the column definition. This is called column-level definition.
1) Primary key
- It is a unique identifier.
- Ignores null values,
- It is entity integrity.
- It will not accept duplicate values.
- Only one primary key is allowed in a table.
- It automatically generates unique index.
Example
To add a primary key using ‘create’ command
CREATE TABLE WORKERS(
WMNO VARCHAR2(6),
WMNAME VARCHAR(20),
WMDES VARCHAR(20),
CONSTRAINT X1 PRIMARY KEY(WMNO)
);
To add a primary key using ‘create’ command without using constraint name
CREATE TABLE WORKERS(
WMNO VARCHAR2(6) PRIMARY KEY,
WMNAME VARCHAR(20),
WMDES VARCHAR(20)
);
To add a primary key using ‘alter’ command
ALTER TABLE WORKERS ADD CONSTRAINT X1 PRIMARY KEY(WMNO);
To drop a primary key which has constraint name
ALTER TABLE WORKERS DROP CONSTRAINT X1;
To enable a primary key
ALTER TABLE WORKERS ENABLE CONSTRAINT X1;
To disable a primary key
ALTER TABLE WORKERS DISABLE CONSTRAINT X1;
2) Unique key
- It is unique identifier.
- It accepts null values and can accept more than 1 null value.
- It is entity integrity.
- It automatically generates unique index.
Example
To add unique key using ‘create’ command with constraint name
CREATE TABLE WORKERS(
WMNO VARCHAR2(6),
WMNAME VARCHAR(20),
WMDES VARCHAR(20),
CONSTRAINT X1 UNIQUE(WMNO)
);
To add unique key using ‘create’ command without constraint name
CREATE TABLE WORKERS(
WMNO VARCHAR2(6) UNIQUE,
WMNAME VARCHAR(20),
WMDES VARCHAR(20)
);
To drop unique key using ‘alter’ command
ALTER TABLE WORKERS ADD CONSTRAINT X1 UNIQUE(WMNO);
To drop unique key
ALTER TABLE WORKERS DROP CONSTRAINT X1;
3) Foreign key
- It is referential integrity.
- It refers a primary (or) unique constraint of another table.
- It accepts null values.
- It accepts duplicate values.
Example
To create foreign key using ‘create’ command from primary key
CREATE TABLE WORKERS(
WMNO VARCHAR2(6),
WMNAME VARCHAR(20),
WMDES VARCHAR(20),
CONSTRAINT X1 PRIMARY KEY(WMNO)
);
CREATE TABLE LOC(
WMNO VARCHAR2(6),
WLN NUMBER(20),
CONSTRAINT X2 FOREIGN KEY(WMNO) REFERENCES WORKERS(WMNO)
);
To create foreign key using ‘create’ command from unique key
CREATE TABLE WORKERS(
WMNO VARCHAR2(6),
WMNAME VARCHAR(20),
WMDES VARCHAR(20),
CONSTRAINT X1 UNIQUE(WMNO)
);
CREATE TABLE LOC(
WMNO VARCHAR2(6),
WLN NUMBER(20),
CONSTRAINT X2 FOREIGN KEY(WMNO) REFERENCES WORKERS(WMNO)
);
To create foreign key using ‘alter’ command
ALTER TABLE LOC ADD CONSTRAINT X2 FOREIGN KEY(WMNO) REFERENCES WORKERS(WMNO)
To drop foreign key
ALTER TABLE LOC DROP CONSTRAINT X2;
4) Not null
- It is domain integrity.
- Ignores null values.
Example
To add not null using ‘create’ command
CREATE TABLE WORKERS(
WMNO VARCHAR2(6) NOT NULL,
WMNAME VARCHAR(20),
WMDES VARCHAR(20)
);
To add not null using ‘alter’ command
ALTER TABLE WORKERS MODIFY WMNO VARCHAR2(6) NOT NULL;
To drop not null
ALTER TABLE WORKERS MODIFY WMNO VARCHAR2(6) NULL;
5) Check
- It is domain integrity.
- It should satisfy a condition.
Example
To add check constraint using ‘create’ command with constraint name
CREATE TABLE WORKERS(
WMNO VARCHAR2(6),
WMNAME VARCHAR(20),
WMDES VARCHAR(20),
WSAL NUMBER(8),
CONSTRAINT X1 CHECK(WSAL BETWEEN 10000 AND 20000)
);
To add check constraint using ‘create’ command without constraint name
CREATE TABLE WORKERS(
WMNO VARCHAR2(6),
WMNAME VARCHAR(20),
WMDES VARCHAR(20),
WSAL NUMBER(8) CHECK(WSAL BETWEEN 10000 AND 20000)
);
To add check constraint using ‘alter’ command
ALTER TABLE WORKERS ADD CONSTRAINT X1 CHECK(WSAL BETWEEN 10000 AND 20000);
To drop check constraint.
ALTER TABLE WORKERS DROP CONSTRAINT X1;
When you use number
CHECK(WSAL BETWEEN 10000 AND 20000)
When you use character
CHECK(WLOC='CHENNAI');
X1 CHECK(WMDES IN ('CHENNAI','HYDREBAD'))
Example
To see list of columns names, data type, table name…etc in entire database
SELECT * FROM USER_TAB_COLUMNS;
To see list of constraints names, constraint types, table name…etc in entire database
SELECT * FROM ALL_CONSTRAINTS;
No comments:
New comments are not allowed.