what do u mean by constrains of a system . Explain
Answers
Constraints enable the RDBMS enforce the integrity of the database automatically, without needing you to create triggers, rule or defaults.
Types of constraints:
· PRIMARY KEY
UNIQUE
· FOREIGN KEY
· CHECK
· NOT NULL
A PRIMARY KEY constraint is a unique identifier for a row within a database table. Every table should have a primary key constraint to uniquely identify each row and only one primary key constraint can be created for each table. The primary key constraints are used to enforce entity integrity.
A UNIQUE constraint enforces the uniqueness of the values in a set of columns, so no duplicate values are entered. The unique key constraints are used to enforce entity integrity as the primary key constraints.
A FOREIGN KEY constraint prevents any actions that would destroy link between tables with the corresponding data values. A foreign key in one table points to a primary key in another table. Foreign keys prevent actions that would leave rows with foreign key values when there are no primary keys with that value. The foreign key constraints are used to enforce referential integrity.
A CHECK constraint is used to limit the values that can be placed in a column. The check constraints are used to enforce domain integrity.
A NOT NULL constraint enforces that the column will not accept null values. The not null constraints are used to enforce domain integrity, as the check constraints.
You can create constraints when the table is created, as part of the table definition by using the CREATE TABLE statement.
Examples
The following example creates a check_sale CHECK constraint on an employee table:
CREATE TABLE employee(
EmployeeId INT NOT NULL,
LName VARCHAR(30) NOT NULL,
FName VARCHAR(30) NOT NULL,
Address VARCHAR(100) NOT NULL,
HireDate DATETIME NOT NULL,
Salary MONEY NOT NULL CONSTRAINT check_sale CHECK (salary > 0)
)
You can add constraints to an existing table by using the ALTER TABLE statement. The following example adds a pk_employee primary key constraint on an employee table:
ALTER TABLE employee
ADD CONSTRAINT pk_employee PRIMARY KEY (EmployeeId)
You can add the primary or unique key constraint into an existing table only when there are no duplicate rows in the table. You can drop constraints in an existing table by using the ALTER TABLE statement. The following example drops the pk_employee primary key constraint in the employee table:
ALTER TABLE employee
DROP CONSTRAINT pk_employee
Sometimes you need to perform some actions that require the FOREIGN KEY or CHECK constraints be disabled, for example, your company do not hire foreign employees, you made the appropriate constraint, but the situation was changed and your boss need to hire the foreign employee, but only this one. In this case, you need to disable the constraint by using the ALTER TABLE statement. After these actions will be performed, you can re-enable the FOREIGN KEY and CHECK constraints by using the ALTER TABLE statement.
The following example disables the check_sale constraint in the employee table and enables this constraint later:
-- disable the check_sale constraint in the employee table
ALTER TABLE employee NOCHECK CONSTRAINT check_sale
-- enable the check_sale constraint in the employee table
ALTER TABLE employee CHECK CONSTRAINT check_sale