Page 174 - IT-802_class_12
P. 174
Emp_Name VARCHAR(30) ,
Address VARCHAR(30)
);
CREATE TABLE DEpARTmENT
(
Dept_iD iNTEGER pRimARy KEy,
Emp_No iNTEGER,
Designation VARCHAR(15),
FOREiGN KEy (Emp_No) REFERENCES EmpLOyEE(Emp_iD)
);
Now, if you see the structure of the DEPARTMENT table, the following output appears:
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| Dept_iD | int | NO | pRi | NULL | |
| Emp_No | int | yES | mUL | NULL | |
| Designation | varchar(15) | yES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
As you can see in the above table structure, the key column for Emp_No contains MUL which indicates multiple
occurrences of a given value can appear within the column. This is also because it is a foreign key.
Notes
The referenced table must be created before it is referred in any other table.
You can also specify the action to be taken in case the foreign key attribute or the primary key attribute value is
changed, as that may result in a violation of the referential integrity constraint.
1.6 naMing of constRaint
The CREATE TABLE command allows for the naming of constraints. The advantage is that using the ALTER TABLE command,
specified constraints can be quickly modified or eliminated. When naming a constraint, use the keyword CONSTRAINT,
then the constraint’s name and its definition. For example,
CREATE TABLE STUDENT
(
Roll_No iNTEGER,
Name VARCHAR(30),
Gender CHAR(1),
Date_of_Birth DATE,
Address VARCHAR(50),
CONSTRAiNT STUDENT_pK pRimARy KEy (Roll_No, Date_of_Birth)
);
In the above table, the primary key constraint is named as STUDENT_PK.
172 Touchpad Information Technology-XII

