What are constraints? When tables are created, it is common for one or more columns to have constraints associated with them. A constraint is basically a rule associated with a column that the data entered into that column must follow. For example, a "unique" constraint specifies that no two records can have the same value in a particular column. They must all be unique. The other two most popular constraints are "not null" which specifies that a column can't be left blank, and "primary key". A "primary key" constraint defines a unique identification of each record (or row) in a table. All of these and more will be covered in the future Advanced release of this Tutorial. Constraints can be entered in this SQL interpreter, however, they are not supported in this Intro to SQL tutorial & interpreter. They will be covered and supported in the future release of the Advanced SQL tutorial - that is, if "response" is good.
It's now time for you to design and create your own table. You will use this table throughout the rest of the tutorial. If you decide to change or redesign the table, you can either drop it and recreate it or you can create a completely different one.
particular column or a set of columns meets particular criteria. When you insert or update a row, the constraint is applied without respect to any other row in the table. The focus is on the data that is in the column. These kinds of constraints will resurface when we deal with Check constraints, Default constraints and rules and defaults.
Entity Constraints: Entity constraints are all about individual rows. This constraint ignores the column as a whole and focuses on a particular row. This can be best exemplified by a constraint that requires every row to have a unique value for a column or a combination of columns. This is to ensure that for a particular row, the same value does not already exist in some other row. We’ll see this kind of constraint in dealing with Primary key and Unique constraints.
Referential Integrity Constraints: Referential integrity constraints are created when a value in one column must match the value in another column. It can either be in the same table or more typically, a different table. For example, we are taking orders for a product, and we accept credit payment. But we will accept only a few standard credit card companies like Visa, MasterCard, Discover, and American Express. Referential integrity constraints allow us to build what we would call a domain table. A domain table is table whose sole purpose is to provide a limited list of acceptable values. In our case we have a CreditCard table with CreditCardID, and CreditCard as fields. We can then build one or more tables that reference the CreditCardID column of our domain table. With referential integrity, any table that is defined as referencing our CreditCard table will have to have a column that matches up to the CreditCardID column of our CreditCard table. For each row we insert into the referencing table, it will have a value that is in our domain list. We will see more of this when we learn about Foreign key constraints.
By default, a column can hold NULL. If you not want to allow NULL value in a column, you will want to place a constraint on this column specifying that NULL is now not an allowable value.
This constraint will not accept null values
CREATE TABLE EMP (ENO INT CONSTRAINT EMP_ENO_NN NOT NULL, ENAME VARCHAR (20), SAL INT)
Giving a not null constraint in already existing column
ALTER TABLE EMP ALTER COLUMN SAL INT NOT NULL
Removing a NOTNULL constraintALTER TABLE EMP ALTER COLUMN ENO INT NULL
Specify a Condition
CREATE TABLE EMP (ENO INT, ENAME VARCHAR (20), SAL INT CONSTRAINT EMP_SAL_CK CHECK (SAL BETWEEN 3000 AND 4000)
Adding Check Constraints
ALTER TABLE EMP ADD CONSTRAINT EMP_ENO_CK CHECK (ENO>100)
Dropping Check constraints
ALTER TABLE EMP DROP CONSTRAINT EMP_ENO_CK
Insert a default Value
CREATE TABLE EMP(ENO INT, ENAME VARCHAR(20), SAL INT, BRANCH VARCHAR(18) CONSTRAINT EMP_DF DEFAULT(‘
UNIQUE CONSTRAINTS
This constraint will not accept duplicate values. But it will accept null value but once.
(The UNIQUE constraint ensures that all values in a column are distinct.)
CREATE TABLE EMP(ENO INT CONSTRAINT EMP_ENO_UK UNIQUE, ENAME VARCHAR(20). SAL INT)
A primary key is used to uniquely identify each row in a table. It can either be part of the actual record itself , or it can be an artificial field (one that has nothing to do with the actual record). A primary key can consist of one or more fields on a table. When multiple fields are used as a primary key, they are called a composite key.
This constraint will not accept duplicate or null values. Table created with this constraint is called parent table. This can be referenced by other table.
CREATE TABLE DEPT (DEPTNO INT CONSTRAINT DEPT_PK PRIMARY KEY, DNAME VARCHAR(20))
A foreign key is a field (or fields) that points to the primary key of another table. The purpose of the foreign key is to ensure referential integrity of the data. In other words, only values that are supposed to appear in the database are permitted.
Table created with this constraint is called child table.
CREATE TABLE EMP (ENO INT, ENAME VARCHAR(20), SAL INT, DEPTNO INT CONSTRAINT EMP_PK FOREIGN KEY (DEPTNO) REFERENCES DEPTNO(DEPTNO)
Table 1 -EMP & Table 2 - DEPT
| ENO | ENAME | SAL | DEPTNO |
| 1 | HARI | 22000 | 10 |
| 2 | ANU | 12000 | 20 |
| 3 | PAUL | 6000 | 20 |
| 4 | ANIL | 8000 | 10 |
| 5 | BAIJU | 5000 | 20 |
| DEPT NO | DNAME |
| 10 | ACCOUNTS |
| 20 | SALES |
| 30 | HRD |
Note: While inserting records into the child tables foreign key column, ensure that the record exist in parent tables primary key column. It is not possible to delete a parent table record if it is having corresponding records in the child table.
If you give the command with foreign key declaration, it becomes possible to delete a parent table record even if it is having records in the child table. What happens is that along with the parent table record, corresponding records from the child table also get declared.
CREATE TABLE EMP(ENO INT, ENAME VARCHAR(20), SAL INT, DEPTNO INT CONSTRAINT EMP_FK FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO) ON DELETE CASCADE)
Eg:
.png)
.png)
