Skip to main content

REFERENTIAL INTEGRITY & SQL CONSTRAINTS

 

RDBMS CONCEPT

REFERENTIAL INTEGRITY 

Referential Integrity is a system of rules that a DBMS uses to ensure that relationships between records in related table are valid, and that users don't accidentally delete or change related data.

Conditions to set Referential Integrity:

✓ The matching field from the primary table is a primary key or has a unique index

✓ The related fields have the same data type

✓ Both tables belong to the same database

When referential integrity is enforced, given rules should be followed:

✓ One can't enter a value in the foreign key field of the related table that doesn't exist in the primary key of the primary table.

✓ One can't delete a record from a primary table, if matching records exist in related table.

✓ One can't change a primary key value in the primary table, if that record has related records.


For example, suppose Table B has a foreign key that points to a field in Table A. 

Referential integrity would prevent you from adding a record to Table B that cannot be linked to Table A.

 In addition, the referential integrity rules might also specify that whenever you delete a record from Table A, any records in Table B that are linked to the deleted record will also be deleted. This is called cascading delete.

Finally, the referential integrity rules could specify that whenever you modify the value of a linked field in Table A, all records in Table B that are linked to it will also be modified accordingly. This is called cascading update.


CREATE TABLE Department (dept_id INT NOT NULL,
                         dept_name VARCHAR(256),                      
                         PRIMARY KEY (dept_id));

CREATE TABLE Employee (emp_id INT NOT NULL,
                       emp_name VARCHAR(256),
                       dept_id INT,
               FOREIGN KEY (dept_id) REFERENCES Department(dept_id));

Above SQL statements will create both Department and Employee table. dept_id is now foreign key in Employee table. 



SQL Constraints

SQL constraints are used to specify rules for the data in a table.

Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the table. If there is any violation between the constraint and the data action, the action is aborted.

Constraints can be column level or table level. Column level constraints apply to a column, and table level constraints apply to the whole table.

CREATE TABLE Persons (

    ID int NOT NULL,

    LastName varchar(255) NOT NULL,

    FirstName varchar(255) NOT NULL,

    Age int

);


CREATE TABLE Persons (

    ID int NOT NULL UNIQUE,

    LastName varchar(255) NOT NULL,

    FirstName varchar(255),

    Age int

);


CREATE TABLE Persons (

    ID int ,

    Sid integr check(sid>10),

    LastName varchar(255) NOT NULL,

    rol integer,

    Qoh integer,

    Age int,

    Grade char(2), DEFAULT ‘A1’,

    Check(ROL>QOH),

    UNIQUE (ID)

);


Comments

Post a Comment

Popular posts from this blog

CS - SORTING/SEARCHING ALGORITHMS

  SORTING ALGORITHMS                       SORTING ALGORITHM PDF LINK #Bubble Sort          ·        The outer loop iterates through the entire array. ·        The inner loop compares adjacent elements and swaps them if they are out of order. ·        The outer loop runs n times, and each pass moves the largest element to its correct position. arr=[3,8,5,2,1] n = len(arr) print(n) for i in range(n):  #traverse through all the elements         # Last i elements are already sorted, no need to check them         for j in range(0, n-i-1):              # Swap if the element found is greater than the next element              if arr[j] > arr[j+1]:               ...
Post a Comment
Read more

GRADE XI - NESTED FOR LOOP

                                                         NESTED FOR LOOP 1. for var1 in range(3):      print(var1,"OUTER LOOP")          # 0 outer loop       1 outer loop      2 outer loop          for var2 in range(2):                  print(var2+1,"INNER LOOP")    #1  2 inner loop     1  2  inner loop   1 2 inner loop  2. Print the following pattern using for loop: 1  1 2  1 2 3  1 2 3 4  Sol: for r in range(1,5):   #ROWS     for c in range(1,r+1):   #COLUMNS         print(c,end=" ")     print() 3. Print the following pattern using for loop: @  @ @  @ @ @...
Post a Comment
Read more

LIST IN PYTHON - 14TH AUG

  LIST IN PYTHON BEGINNERS PRACTICE QUESTIONS 1. Creating List by accepting data from user List1=[] #Empty list n=int(input("Enter number of elements"))    i=0 while i < n:     num= int(input("Enter element " + str(i) + ": "))     List1+=[num]       i+=1 print(List1) OR #Creating List by accepting data from user List1=[ ] n=int(input("Enter number of elements"))   i=0 while i < n:     List1+=[int(input("Enter the element"))]       i+=1     print(List1) 2.Write a program to input the size and the list of elements from user. Count the number of elements  which are divisible by 5 list1= [ ] s =int (input("Enter no of elements")) for i in range(s):     list1 += [int (input("Enter the Elements"))] count=0 for num in list1:         if num % 5 ==0:             count = count+1 print("No. of elements divisible by 5--",c...
Post a Comment
Read more