Page 151 - Informatics_Practices_Fliipbook_Class12
P. 151
While writing SQL statements, the symbol # marks the beginning of a comment that extends to the end of the line.
A C-style comment is enclosed between /* and */. A comment may also begin with -- (double hyphen), followed by
a whitespace or control character such as space, tab, or end of line (EOL), and extends to the end of the line.
1. The primary key can be defined as a constraint as shown below:
CONSTRAINT EMPLOYEE_Key PRIMARY KEY(ID)
2. Syntax for the constraint statement is given below:
CONSTRAINT ConstraintName PRIMARY KEY(attribute_1, [attribute_2],...)
3. Note that the primary key constraint includes the name of at least one attribute included in the primary key. In
general, several attributes may be included in the primary key constraint.
CREATE TABLE EMPLOYEE (
ID INT NOT NULL UNIQUE,
FName VARCHAR(20) NOT NULL,
LName VARCHAR(20) NOT NULL,
Gender CHAR(1) NOT NULL,
Address VARCHAR(30),
City VARCHAR(20),
Pin_Code CHAR(6),
DOB DATE,
Salary INT NOT NULL,
Dept_No SMALLINT,
CONSTRAINT EMPLOYEE_Key PRIMARY KEY(ID)
); #EMPLOYEE
Note that the EMPLOYEE_Key is the name of the primary key constraint on ID. A named constraint may be
modified or dropped later by referring to its name.
Next, we discuss some more SQL constraints:
4.4.3 DEFAULT constraint
A default value for an attribute may be specified using the DEFAULT constraint. For example, if most of the employees
have a salary of 10000, it would be wise to set it as the default salary for an employee as follows:
Salary INT NOT NULL DEFAULT 10000
The revised CREATE TABLE statement for the table EMPLOYEE would appear as follows:
CREATE TABLE EMPLOYEE (
ID INT PRIMARY KEY,
FName VARCHAR(20) NOT NULL,
LName VARCHAR(20) NOT NULL,
Gender CHAR(1) NOT NULL,
Address VARCHAR(30),
City VARCHAR(20),
Pin_Code CHAR(6),
DOB DATE,
Salary INT NOT NULL DEFAULT 10000,
Dept_No SMALLINT
);#EMPLOYEE
Database Query using SQL 137

