SQL Server : Primary Key | Foreign Key Constraint

Erukulla's July 24, 2015

Primary Key Constraint :

A primary key constraint is defined on a column or a set of columns whose value uniquely identify the rows in a table. These columns are referred to as primary key columns. A primary key column cannot contain NULL values since it is used to uniquely identify rows in a table.

While defining a PK constraint, you need to specify a name for the constraint, If no name is specified, SQL Server automatically assigns a unique name to the constraint.

Any column or set of columns that uniquely identifies a row in a table can be a candidate for the primary key. These set of columns are referred to as candidate keys. One of the candidate keys is chosen to be the primary key, based on familiarity and greater usage. the other key, which is not chosen as the primary key is called as alternate key.

A table should contain only one primary key.

Creating a primary key is very much similar to the unique constraint. In the syntax of unique, simply replace the keyword unique with a primary key.

Syntax :



  CREATE TABLE table name (ColumnName1 datatype CONSTRAINT constraint_name Primary Key,
                          ColumnName2 datatype......................................)

( Or)

  ALTER TABLE table name ADD CONSTRAINT constraint_name PRIMARY KEY (field name)
Example : 


 1. create table dept (dno int constraint pk_dept_dno primary key, 
                       dname char(10), dsize int)

 2. insert into dept values(10, 'HR', 30)
 3. insert into dept values(10, 'Admin', 25)
    ---- Error, duplicate dno value
 4. insert into dept (dname, dsize) values('Tester', 30)
    ---- Error, cannot enter a null value into dno column.

Adding a Primary Key :

It is not possible to directly add a primary key to an existing table's column if it is having any data. At first, we must add a 'NOT NULL' option to that column. Then only, we can add the primary key.


 1. create table emp1 (eno int, ename char(10))
 2. insert into emp1 values(100, 'A')
 3. insert into emp1 values(101, 'B')
 4. alter table emp1 add constraint pk_emp1_eno primary key(eno)
    --- Error, since there is no NOT NULL option for the eno column
 5. alter table emp1
    alter column eno int not null
    --- Specifying the NOT NULL option to the eno column
 6. Execute command no.4 again. Now, it will execute successfully.

Composite Primary Key :

in some tables it is not possible to provide the primary key on any single column of the table. Then check the table data to identify any column combination, by using which, we can identify different records and other data of the table. If it is found, make that column as the primary key called as 'Composite Primary Key'. A maximum of 16 columns can be combined using this constraint.



 Example :
 =======
  
   CREATE TABLE OrderDetails(ordid int, ino char(2), orddate datetime, cno int, Qty int, constraint pk_od_ordid_ino Primary Key(ordid, ino))

Foreign Key Constraint :

A foreign key is a column or combination of columns whose values match the primary key of another table. A foreign key does not have to be unique. However, foreign key values must be copies of the primary key values of the master table. The foreign key can be given on a column which is having the same data type, size and data as of the primary key column. The table with foreign key is called as 'Referenceing/Child' table and the table with primary key is called as 'Regerenced/Parent/ table.

Once we apply a relation between the table, lot of restrictions will be applied to the table operations. Some of them are:

  1. The data to be entered in a foreign key column must heve a matching value in the primary key column of the parent table.
  2. It is not possible to delete the parent table record if it is having a reference in the child table


Syntax :
======
  
   CREATE TABLE < TableName >
    (<ColName1> < Datatype > [(size)] [CONSTRAINT <ConstraintName>] REFERNCES <ParentTable> (7lt;PrimarykeyCol>) 
    [ON DELETE CASCADE], 
     <ColName2>------------------------------------------)

Example :-
=======
 1. create table emp(empid int, ename char(10), sal int, dno int constriant fk_emp_dno references dept(dno))
 2. insert into emp values (100, 'A', 5000, 20)
 3. insert int oemp values (101, 'B', 4500, 50)
    --- Error, dno 50 is not having any reference in the parent table
 4. insert into emep
    Select 101, 'B', 3500, 10, union all
    Select 102, 'C', 4500, 20, union all
    Select 103, 'D', 5000, 30
    --- Entering multipple values at a time into the table.

 5. delete form dept where dno=20
    --- Error, Since this record is having some referenced in the child table

The rules regarding the creation of a Foregin key constraint are as follows:

  • The no. of columns specified in the foreign key statement must match the no. of columns in the references clause and must be specified in the same order.
  • The foreign key can reference the primary key or a UNIQUE constraint of another table.
  • The foreign key constraint that is created using the WITH NONCHECK option will prevent the foreign key constraint from validating existing data.

ON DELETE CASCADE :

In general it is not possible to delete a record from the parent table if is having any references in the child table. In this case, at first, we have to remove the child table records and then the parent table record.

With the use of 'ON DELETE CASCADE', whenever, we remove the parent table record, that record and all its child table records will be removed automatically.

Adding a Foreign Key :



  ALTER TABLE < Tablename >
   Add Constraint 7lt;ConstraintName>
   Foreign Key(<ColName>)
   REFERENCES <ParentTable>(<PKColumn>)
   [ON DELETE CASCADE]

Example :-
=======

  1. alter table emp drop constraint fk_emp_dno
      --- removing existing constraint
  2. alter table emp add constraint fk_emp_dno foreign key(dno) reference dept(dno_) on delete cascade
      --- adding the foreign key with new option
  3. delete from dept where dno=20
      --- Deletes the records with dno 20 both from dept and emp table

Constraints Vs Triggers :

Each category of data integrity is best enforced through the following constraints.

Entity integrity is best enforced through the PRIMARY KEY constraint, UNIQUE constraint and the IDENTITY property.

The IDENTITY property creates an identity column in the table. An identity column is a primary key column for which numeric values are generates automatically. Each generated value is different from the previous value, thus ensuring uniqueness of data in each row at the table. The IDENTITY property also ensures that while inserting data, the user need not explicitly insert a value for the identity column. Domain integrity is best enforced through the DEFAULT constraint, CHECK constraint and the FOREIGN KEY constraint.

Previous Articles - SQL Server : Constraint

Post a Comment

0 Comments