what is constraints in dbms ?

Question

hi all i want to know that while we create a forigen key for any table then why we use constraints like..

CREATE TABLE Persons
(
    PersonID int identity(1,1) NOT NULL,
    FirstName nvarchar(20),
    LastName nvarchar(20) NOT NULL,
    CONSTRAINT PrimKeyPeople PRIMARY KEY(PersonID)
);

here all ready PersonID is primary key then why it's need to give another name primkeypeople. why we use constraints name here PrimKeyPeople here what is PrimKeyPeople ?

Answer 1

Every constraint defined in a DBMS has a name. In your example, PrimKeyPeople is the name of the constraint that defines PersonID as primary key.

Constraint names (together with Table name, View names and the name of every other entity existing in the database) are stored in the SQL/Schemata (or Information and Definition Schemas). The SQL/Schemata makes SQL databases and objects self-describing. SQL/Schemata allows, for example, to write SQL updates that remove primary key constraint. How can you remove a constraint? By referencing its name, of course!

Note that if you do not assign a name to a constraint, the DBMS will automatically generate one.

Answer 2

Constraint can be described as rule you must follow.

Several constraints known in DBMS are Primary Key, Foreign Key and Unique. Primary Key and Unique ensures that all value in it is unique. Hence any non-unique value will never be accepted.

Other constraint like foreign key ensures that its value refers to another value in another table. Hence any foreign key value which doesn't refer to another value will never be accepted.

In your case, PrimKeyPeople is a primary key in table Persons. This primary key (unique constraint) will assert that every value in it will be unique. Naming a constraint is a must (for DBMS) but optional for users. This 'naming' is required because sometime we need to disable/enable/remove some constraints. To make it user-friendly, some DBMS provides default name to every constraints so that user doesn't have to declare it explicitly.