Tech Point Fundamentals

Sunday, May 22, 2022

C# Interview Questions and Answers - Part 01

C# Interview Questions and Answers - Part 01

csharp-interview-questions-and-answers-part1

Are you preparing for the C# Interview? If yes, then you are at the right place. This is the C# Interview Questions and Answers article series. Here we will see top 150+ C# Interview Questions with Answers. 

Please visit our YouTube Channel for Interviews and other videos by below link:


Please read the complete Design Patterns Interview Questions and Answers series here.


Introduction


This is the 1st part of this C# Interview Questions and Answers article series. Each part will contain 10 C# Interview Questions with Answers. I will highly recommend to please read the previous parts always before reading the current part.  





C# Interview Questions and Answers - Part 01


Q001. What are the 4 pillars of OOPS?

There are four pillars of any object-oriented programming language:


1. Abstraction:

Abstraction is the process of hiding the internal complex details and showing only the minimum required functionality to the external users. Both abstract class and interface are used for data abstraction. It ensures that only the required information is visible to the user and the rest of the information is hidden from the external world.

Please read the abstract class article hereC#8 interface here, an abstract class vs interface here for more details.


2. Encapsulation:

Encapsulation is the process of binding the data members (properties or variables) to the methods. It wraps the member functions and data members into a single unit. The encapsulation hides or restricts the internal data member from accessing the other external classes. Therefore it is also known as data hiding.  

Encapsulation is achieved by declaring all the variables in the class as private and using properties in the class to set and get the values of variables. Please watch the variable vs properties video here for more details.




3. Inheritance:

Inheritance is a way to access and use the properties and methods of other classes. The original class is known as the base class and the class which inherits the functionality of the base class is the derived class. Inheritance provides code reusability. C# does not support multiple class inheritance due to the diamond problem but multiple interfaces inheritance is allowed.

Please read the Multiple Interface Inheritance and Diamond Problem Resolution in C#8 here for more details.


4. Polymorphism:

The term "polymorphism" means "having many forms". So in OOPS, it means that an object can have multiple functionalities. There are two types of polymorphism in OOPS.

Static Polymorphism or Early Binding involves linking a method with an object during compile time, hence also known as Compile Time BindingMethod Overloading and Operator Overloading are the way to achieve Static Polymorphism. Please read the Static Polymorphism article here for more details

Dynamic Polymorphism or Late Binding involves linking a method with an object during run time, hence also known as Run Time-BindingMethod Overriding is a way to achieve Dynamic Polymorphism. Please read the Dynamic Polymorphism article here for more details.



Q002. What is the difference between Abstraction and Encapsulation?

Both seem very similar but totally different in concept and implementation. In OOPS abstraction hides the code complexity while encapsulation hides the internal working from the outside world.

Abstraction is a way to only show the essential data to the user. It solves an issue at the design level. For abstraction, abstract classes and interfaces are used.

On the other hand, encapsulation hides the code and data into a single entity or unit so that the data can be protected from the outside world. It is an implementation-level process. It is achieved by different access modifiers like private, public, protected. The getters and setters methods of properties are used to hide the data.

Please read more about the abstract class here and abstract class vs interface here.



Q003. What is the difference between Class and Structure in C#?

  1. All the structs are value types while classes are reference types.
  2. Structs cannot have destructors, but classes can have destructors. 
  3. Structs cannot have an explicit default constructor while a class can have a default constructor.
  4. Structs do not support inheritance while classes support inheritance.
  5. Instance field declarations for a struct are not permitted to include variable initializers while a class can initialize the field members.
  6. In C# user can copy one structure object into another one using the assignment operator (=) but a class cannot be.
  7. A structure cannot have the abstract, virtual, or sealed members while a class can have.
  8. A structure cannot have a sealed method but a class can have sealed methods.
  9. A structure can only override the Object class virtual methods while a class can override any base class virtual method and abstract method.


Please read the complete C# Structure article here.




Q004. What are the different Access Modifiers in C#?

Access modifiers specify the accessibility of an object and all of its members. All the C# types (class, interface, structure, etc) have access modifiers implemented, even if they are not stated i.e default access modifiers.

There are six access modifiers in C#, four core types of access modifiers: private, public, protected, internal, and two combined access types: protected-internal and private-protected. 

All the access modifiers are valid for all types and members in all contexts. In some cases, the accessibility of a type member is constrained by the accessibility of its containing type as well.

1. Private: A private type or member can be accessed only by code in the same class or struct. Objects that are specified with private access modifiers are accessible only inside that class, interface, or structure. So nobody can access them outside the declaring class they are created.

2. Public: A public type or member can be accessed by any other code in the same assembly or another assembly that references it. The accessibility level of public members of a type is controlled by the accessibility level of the type itself. Objects that specified public access modifiers are accessible from everywhere in the project and assemblies. So there are no accessibility restrictions at all.



3. Protected: A protected type or member can be accessed only by code in the same class, or in a class that is derived from that class. Objects that specified protected access modifiers are accessible within the declaring class and inheriting class only. So the protected keyword implies that the object is accessible inside the class and in all classes that derive from that class.

4. Internal: A internal type or member can be accessed by any code in the same assembly, but not from another assembly. In other words, internal types or members can be accessed from code that is part of the same compilation. The internal keyword specifies that the object is accessible only inside its own assembly but not in other assemblies. 

5. Protected Internal: A protected internal type or member can be accessed by any code in the assembly in which it's declared, or from within a derived class in another assembly. The protected internal access modifier is a combination of protected and internal. Therefore it has both features. So you can access a protected internal member only in the same assembly or in a derived class of other assemblies or projects as well.

6. Private Protected: A private protected type or member can be accessed by types derived from the containing classes that are declared within its containing assembly. The private protected access modifier is a combination of private and protected keywords. Therefore it also has both features. So you can access private protected members-only inside the declaring class or in the derived class, but only in the same assembly(project). If you try to access it from another assembly, you will get an error.



Default Accessibility: 

  1. All the Classes, records, and structs declared directly within a namespace(non-nested) can be either public or internal. The internal is the default if no access modifier is specified. 
  2. Class and struct members, including nested classes and structs, have private access by default.
  3. All private nested types aren't accessible from outside the containing type.
  4. Derived classes can't have greater accessibility than their base types. So you can't declare a public class B that derives from an internal class A.
  5. Struct members can't be declared as protected, protected internal, or private protected because structs don't support inheritance.
  6. Normally, the accessibility of a member isn't greater than the accessibility of the type that contains it. However, a public member of an internal class might be accessible from outside the assembly if the member implements interface methods or override virtual methods that are defined in a public base class.
  7. User-defined operators must always be declared as public and static.
  8. Destructors can't have any accessibility modifiers.
  9. The type of any member field, property, or event must be at least as accessible as the member itself. Similarly, the return type and the parameter types of any method, indexer, or delegate must be at least as accessible as the member itself.
  10. Interfaces declared directly within a namespace can be public or internal and, just like classes and structs, interfaces default to internal access.
  11. Interface members are public by default because the purpose of an interface is to enable other types to access a class or struct. Interface member declarations may also include any access modifier after C#8. 
  12. Enumeration members are always public, and no access modifiers can be applied.
  13. Since delegates behave like classes and structs. So by default, they have internal access when declared directly within a namespace, and private access when nested.



Q005. What is Polymorphism in C#? What are the different types of Polymorphism?

Polymorphism is often referred to as the third pillar of object-oriented programming, after encapsulation and inheritance.  The term "polymorphism" means "having many forms". So in OOPS, it means that an object can have multiple functionalities. 

There are two types of polymorphism in OOPS.

1. Static Polymorphism:

Static Polymorphism or Early Binding involves linking a method with an object during compile time, hence also known as Compile Time Binding. Method Overloading and Operator Overloading are the way to achieve Static Polymorphism. 

Please read the Static Polymorphism article here for more details. You can also read the Static Polymorphism Rules here.



2. Dynamic Polymorphism:

Dynamic Polymorphism or Late Binding involves linking a method with an object during run time, hence also known as Run Time-Binding. Method Overriding is a way to achieve Dynamic Polymorphism. 

At run time, objects of a derived class may be treated as objects of a base class in places such as method parameters and collections or arrays. When this polymorphism occurs, the object's declared type is no longer identical to its run-time type.

Please read the Dynamic Polymorphism article here for more details.



Q006. What are the different ways to implement Polymorphism in C#?

Static Polymorphism can be achieved by overloading. If we create two or more members having the same name but different signatures, it is known as member overloading.

Following are the different ways to implement static polymorphism: 
  • Method Overloading
  • Operator Overloading
  • Constructor Overloading
  • Indexer Overloading

Please read about the Static Polymorphism implementation here for more details.

Dynamic Polymorphism can be achieved by method overriding. Base classes may define and implement virtual methods, and derived classes can override them, which means they provide their own definition and implementation. 

At run-time, when the client code calls the method, the CLR looks up the run-time type of the object and invokes that override of the virtual method. In your source code, you can call a method on a base class, and cause a derived class's version of the method to be executed.

Please read the Dynamic Polymorphism implementation article here



Q007. What is the difference between Early Binding and Late Binding in C#? or What is the difference between Compile Time Polymorphism and Run-Time Polymorphism in C#?

In Early Binding, the compiler knows about what kind of object it is, what are all the methods and properties it contains. As soon as you declared the object, .NET Intellisense will populate its methods and properties.  If a method or property does not exist or has data type problems then the compiler automatically throws an exception during compile time.

On the other hand in Late Binding compiler does not know what kind of object it is, what are all the methods and properties it contains. Everything will be known and loaded at the run time. When an object is dynamic or not known which will only bind during runtime is called Late binding. 

Please read about the Early Binding here and Late Binding here for more details.



Q008. What is Shadowing in C#? or  What is the Method Hiding in C#? How can you achieve that?

Shadowing is also known as hiding. In C# you can hide the methods of the base class from the derived class, this concept is known as Method Hiding or Method Shadowing. The term "Shadowing" is normally used in VB.Net which is known as "Hiding" in C#.Net. 

When you say "shadow" you're usually talking about scope while when you say "hiding" you're usually talking about inheritance.

When two-member use the same name, one of them can hide, or shadow, the other one. By shadowing the child class can create its own version of the base class method or property. 

A method of the base class is available to the child class with a new definition or implementation without using the override keyword. 

The "new" keyword is used to perform shadowing, but if it is not used the compiler generates a warning. The compiler hides the method or property of the base class. 

Please read the Shadowing article here for more details.



Q009. What is the abstract method? How an abstract method is different from the virtual method?

A method without body definition and having an "abstract" modifier is known as an abstract method. The keyword "abstract" can only be used just before the return type of the method. Before C# 8 only abstract classes can have abstract methods, but with the introduction of C# 8 interfaces can also have abstract members. 

The abstract modifier indicates that the type being modified has a missing or incomplete implementation. So the inheriting class must have to implement all the abstract members with the "override"  keyword unless the inheriting class is also an abstract class because all the abstract methods are virtual implicitly.

Abstract Method vs Virtual Method:

  1. An abstract method cannot contain body definition while a virtual method must have a body definition.
  2. Overriding all the abstract methods in the inheriting concrete class is mandatory while overriding the virtual methods is optional.
  3. The "abstract" modifier can be used while overriding the abstract method but the "virtual" modifier cannot be used while overriding a virtual method.
  4. An abstract method can only be declared in an abstract class while a virtual method can be defined in a non-abstract class as well.
  5. A virtual method can be overridden to abstract for achieving re-abstraction but an abstract method cannot be overridden to virtual.

Please read the Abstract Method complete article here



Q010. What is the difference between Overloading and Overriding in C#?

  1. Method Overloading is static polymorphism while Method Overriding is dynamic polymorphism. So Method Overloading executes at compile-time while Method Overriding executes at run-time.
  2. Method Overloading is generally defined in the same class while Method Overriding only is used in inheritance.
  3. For Method Overriding the base class method must be virtual (abstract in abstract class) while it is not required in the case of Method Overloading.
  4. In Method Overriding you can not change the method signature in the derived class while Method Overloading is only possible with different method signatures.
  5. Method Overriding requires forcefully rewriting the method definition with the same signature in the derived class while Method Overloading is more flexible by allowing different signatures for different overloading methods.
  6. The Overriding Method is not considered as an overloaded member of the derived class, while other methods with the same name and different signatures are considered as the overloaded methods.

Please read the complete article here for more details.

To Be Continued Part-02...


Recommended Articles






Thanks for visiting this page. Please follow and join us on LinkedIn, Facebook, Telegram, Quora, YouTube, Twitter, Pinterest, Tumbler, VK, and WhatsApp for regular updates.


Friday, May 20, 2022

Design Patterns Interview Questions - Part 12

Design Patterns Interview Questions and Answers - Part 12

design-pattern-interview-questions



In Software Industry the Design Patterns and Principles are always followed. So this is the most common topic for the interview. In this article, we will see the most frequently asked 90+ Design Patterns and Principles Interview Questions with Answers

Please visit our YouTube Channel for Interviews and other videos by below link:


Please read the complete C# Interview Questions and Answers article series here.



Introduction


This is the 12th part of this Design Patterns and Principles Interview Questions and Answers article series. Each part contains five Design Pattern Interview Questions. Please read all the Design Patterns and Principles Interview Questions list here.

I will highly recommend to please read the previous parts over here before continuing the current part:





Design Patterns Interview Questions - Part 12


Q89. What are the GRAPS Principles? 

GRASP stands for General Responsibility Assignment Software Patterns.  Apart from SOLID Principles,  there is another, less well-known set of rules regarding object-oriented programming called GRASP.  It is a set of nine fundamental principles in object design and responsibility assignment first published by Craig Larman in his 1997 book Applying UML and Patterns.

Following is the list of 9 GRASP Principles that answers the fundamental programming questions.

1. Information Expert: 

What is a basic principle by which to assign responsibilities to objects? 

Assign responsibility to the class that has the information needed to fulfill it.



2. Creator: 

Who creates object A?

Assign class B the responsibility to create object A if one of these is true (more is better). B contains or compositely aggregates A. B has the initializing data for A. B closely uses A and B records A.

3. Controller:

What the first object beyond the UI layer receives and coordinates “controls” a system operation?

Assign the responsibility to an object representing one of these choices:

  • Represents the overall “system”, “root object”, device that the software is running within, or a major subsystem (these are all variations of a facade controller)
  • Represents a use case scenario within which the system operation occurs (a use case or session controller)



4. Low Coupling:

How to reduce the impact of change? How to support low dependency and increased reuse?

Assign responsibilities so that (unnecessary) coupling remains low. Use this principle to evaluate alternatives. 

Coupling is a measure of how one element is related to another. The higher the coupling, the greater the dependence of one element to another. 

The low coupling means our objects are more independent and isolated. If something is isolated we can change it not worrying that we have to change something else or whether we would break something



5. High Cohesion:

How to keep objects focused, understandable, manageable, and as a side effect support Low Coupling?

Assign a responsibility so that cohesion remains high. Use this to evaluate alternatives.
Cohesion is a measure of how strongly all responsibilities of the element are related. In other words, what is the degree to which the parts inside an element belong together? Classes with low cohesion have unrelated data and/or unrelated behaviors.

6. Indirection:

Where to assign a responsibility to avoid direct coupling between two or more things?

Assign the responsibility to an intermediate object to mediate between other components or services so that they are not directly coupled. This is where Mediator Pattern comes into play.



7. Polymorphism:

How handle alternatives based on type?

When related alternatives or behaviors vary by type (class), assign responsibility for the behavior (using polymorphic operations) to the types for which the behavior varies. In this context, the principle is strongly connected with (among others) Strategy patterns.

8. Pure Fabrication:

What object should have the responsibility, when you do not want to violate High Cohesion and Low Coupling but solutions offered by other principles are not appropriate?

Assign a highly cohesive set of responsibilities to an artificial or convenience class that does not represent a problem domain concept.

9. Protected Variations:

How to design objects, subsystems, and systems so that the variations or instability in these elements do not have an undesirable impact on other elements?

Identify points of predicted variation or instability, assign responsibilities to create a stable interface around them.



Q90. What is Dependency Hell Problem?

Dependency hell is a colloquial term for the frustration of some software users who have installed software packages that have dependencies on specific versions of other software packages. The dependency issue arises when several packages have dependencies on the same shared packages or libraries, but they depend on different and incompatible versions of the shared packages.

Dependency hell is a term used to define the problems faced by software developers, publishers, and users in general when the software or a software package is dependent on other software. Dependency hell occurs when software works abnormally or displays errors and bugs due to an integrated software/application developed by a third party. Dependency hell is a common problem found in software/applications that are built using an add-on software package or that rely on one for complete functionality.



The "dependency hell" often goes by a local specific name, generally the name of components.

  1. DLL Hell Problem: This is a form of dependency hell occurring on Microsoft Windows.
  2. JAR Hell Problem: This is a form of dependency hell occurring in the Java Runtime Environment before build tools like Apache Maven solved this problem back in 2004.
  3. RPM Hell Problem: This is a form of dependency hell occurring in the Red Hat distribution of Linux and other distributions that use RPM as a package manager.
  4. Extension Conflict Problem: It is a form of dependency hell occurring on the classic Mac OS.

Forms of Dependency Hell Problem: 

  • Too many dependencies
  • Multiple package managers
  • The package or version you need isn’t in your package manager
  • Monkey patching a dependency
  • Breaking changes on a minor or patch version.
  • Circular dependencies
  • Conflicting dependencies
  • The diamond dependency problem



Q91. What is Software Bloat Problem?

Software bloat is a process whereby successive versions of a computer program become perceptibly slower, use more memory, disk space or processing power, or have higher hardware requirements than the previous version, while making only dubious user-perceptible improvements or suffering from feature creep. 

The term "bloatware" is also used to describe unwanted pre-installed software or bundled programs. In computer programming, code bloat refers to the presence of program code (source code or machine code) that is perceived as unnecessarily long, slow, or otherwise wasteful of resources.

Library bloat refers to libraries that are declared or used in the build script while they are not necessary for executing the software application. A library that becomes bloated in a project is very likely to remain bloated in future evolutions of the project, which artificially increases maintenance effort since developers will maintain these bloated dependencies.



Q92. What is Technical Debt or Code Debt in Software Industry?

Technical Debt is also known as Design Debt or Code Debt. In software development, technical debt is the implied cost of additional rework caused by choosing an easy (limited) solution now instead of using a better approach that would take longer.

If technical debt is not repaid, it can accumulate "interest" the same as normal monetary debt, making it harder to implement changes. However technical debt is not necessarily a bad thing and sometimes is required to move projects forward. 



Q93. What is the Big Ball of Mud Problem in the Software Industry?

A big ball of mud is a software system that lacks a perceivable architecture. Although undesirable from a software engineering point of view, such systems are common in practice due to business pressures, developer turnover, and code entropy. 

They are a type of design anti-pattern. The term was popularized in Brian Foote and Joseph Yoder's 1997 paper of the same name.



Q94. What is Spaghetti Code? Why do they happen?

Spaghetti code is a pejorative phrase for unstructured and difficult-to-maintain source code. Spaghetti code can be caused by several factors, such as volatile project requirements, lack of programming style rules, and software engineers with insufficient ability or experience. 

In a 1980 publication by the United States National Bureau of Standards, the phrase spaghetti program was used to describe older programs having "fragmented and scattered files".

For example, a code that overuses GOTO statements rather than structured programming constructs, resulting in convoluted and unmaintainable programs, is often called spaghetti code.

Spaghetti code can also describe an anti-pattern in which object-oriented code is written in a procedural style, such as by creating classes whose methods are overly long and messy or forsaking object-oriented concepts like polymorphism. The presence of this form of spaghetti code can significantly reduce the comprehensibility of a system.



Q95. What is SONARQube?

SonarQube is an open-source platform developed by SonarSource for continuous inspection of code quality to perform automatic reviews with static analysis of code to detect bugs, code smells. It can be used in 20+ programming languages. 

SonarQube offers reports on duplicated code, coding standards, unit tests, code coverage, code complexity, comments, bugs, and security recommendations. SonarQube is available for free under the GNU Lesser General Public License. SonarQube can record metrics history and provides evolution graphs. 

SonarQube provides fully automated analysis and integration with Maven, Ant, Gradle, MSBuild, and continuous integration tools like Jenkins, Azure DevOps, Bamboo, etc. Some most common supporting languages include Java, C#, C, C++, JavaScript, TypeScript, Python, Go, Swift, COBOL, Apex, PHP, Kotlin, Ruby, Scala, HTML, CSS, ABAP, Flex, Objective-C, PL/I, PL/SQL, RPG, T-SQL, VB.NET, VB6, and XML. 

SonarQube integrates with Eclipse, Visual Studio, Visual Studio Code, and IntelliJ IDEA development environments through the SonarLint plug-ins, and also integrates with external tools like LDAP, Active Directory, GitHub, and others. SonarQube is expandable with the use of plug-ins.





To Be Continued Part-13...


Recommended Articles






Thanks for visiting this page. Please follow and join us on LinkedInFacebookTelegramQuoraYouTubeTwitterPinterestTumbler, and VK, for regular updates.

    



External Reference:


  1. Wikipedia
  2. RefacturingGuru
  3. SourceMaking
  4. Grasp


Sunday, May 15, 2022

SQL Constraint | Types of SQL Constraint

SQL Constraint | Types of SQL Constraint

sql-constraints

SQL Constraint is the most important key factor for any table or schema designing. Keeping this in mind the interviewer can ask different questions on Constraints. In this article, we will walk through SQL Constraints and Types of Constraints.

Please visit our YouTube Channel for SQL Interview Questions and Answers videos by the below link:





Introduction


In the database, a constraint consists of one or more columns of a table that have specific properties. So when you define a table, you specify the constraints. There are three keys also which is known as Primary Key Constraint, Unique Key Constraint, and Foreign Key Constraint




SQL Constraints


In SQL, Constraints are the rules that the database enforces to improve data integrity. They are also used to specify the business rules for data in a table.

SQL Constraints are used to restrict the type of data that can be inserted into a table. By doing so, it ensures the accuracy and reliability of the data in the table.

If there is any violation between the defined constraint and the data to be inserted, updated, or deleted, the respective action is aborted or terminated by the SQL.

A user can define constraints either at column level or table level. The column-level constraints apply only to a column, while table-level constraints apply to the whole table or set of columns.

SQL Constraints can be specified when the table is created by using CREATE TABLE statement, or after the table is created by using ALTER TABLE statement.



Types of SQL Constraints


There are the following Constraints in SQL:

  1. NOT NULL Constraint
  2. UNIQUE Constraint or UNIQUE Key Constraint
  3. PRIMARY Key Constraint
  4. FOREIGN Key Constraint
  5. CHECK Constraint
  6. DEFAULT Constraint



1. NOT NULL CONSTRAINT


This constraint ensures that a column cannot have a NULL value. By default all the columns are nullable. There can be multiple NOT NULL Constraint in any table. 

The SQL NOT NULL Constraint can be used with other constraints together as well. Primary Key Constraint uses it along with Unique Key internally. We also use the NOT NULL constraint along with UNIQUE Key Constraint to avoid NULL values.

Please watch the SQL NULL values video here and the SQL Three Valued Logic video here for more details.






2. UNIQUE KEY CONSTRAINT


The UNIQUE Constraint ensures that all values in a column or set of columns are unique. This constraint prevents duplicate values in any table. There can be multiple UNIQUE KEY Constraints in any table. A UNIQUE KEY can also be a composite key.

The UNIQUE Key constraint allows the NULL value by default (only one NULL value). So you can prevent the NULL value by combining the NOT NULL Constraint in the UNIQUE KEY Constraint.



UNIQUE KEY can be created on Temp Table as well. Not only the temp tables but Table Variable and Table-Valued Type can also contain Unique Key Constraint.

Please read more about Unique Key Constraint here and the Unique Key Interview Questions and Answers article here for more details.

Please watch the UNIQUE KEY Constraint video here for more details. 





3. PRIMARY KEY CONSTRAINT


The PRIMARY KEY Constraint is used to uniquely identify each individual record of the table. It can be either any static column or an Identity ColumnThe purpose of the PRIMARY KEY is to enforce Entity Integrity in the column.

PRIMARY KEY is a value that is unique for every record in the table and a record can be accessed by the Primary Key value directly without affecting any other record of the table.



A PRIMARY KEY can also be created on Temp Table. Not only the temp table but Table Variables and Table-Valued Type can also contain Primary Key constraints.

Please read more about Primary Key Constraint here and the Primary Key Interview Questions and Answers article here for more details.

Please watch the Primary Key Constraint video here for more details. 





4. FOREIGN KEY CONSTRAINT


A FOREIGN KEY Constraint is basically used to create a relationship between two tables. This constraint prevents actions that would destroy relational links between the tables.

FOREIGN KEY is a constraint that enforces Referential Integrity in the SQL Server Database. They always enforce and ensure that the referential integrity is preserved.

A FOREIGN KEY can be a set of one or more than one column as well. In that case, it is known as a Composite Foreign Key.

A Foreign Key Constraint prevents you from inserting or updating values that aren’t found in the related reference table's column. It also prevents deleting the records from the parent table.

A table can contain multiple Foreign Keys. In fact, a single column can have multiple Foreign Key Constraints.



A Foreign Key cannot be created on Temp Table. Not only the Temp Table but Table Variables and Table-Valued Type also cannot contain any Foreign Key Constraint.

Please read more about Foreign Key Constraint here and Foreign Key Interview Questions and Answers article here for more details.


Please watch the Foreign Key video here for more details. 





5. SQL CHECK CONSTRAINT


The CHECK Constraints are nothing but the rules or set of rules that help to validate the value being inserted or updated) into a column of table based on a certain condition. They ensure that all the data in a column contains validated values according to the check constraint rule.

If the check condition evaluates to false, the record violates the constraint and isn't inserted into the table. It can be defined while creating the table or by ALTER command if the table is already created.



  1. Check Constraints cannot be defined on Views.
  2. The definition of a Check Constraint cannot include a subquery. Please watch the subquery video here for more details.
  3. The Check Constraint defined on a table must refer to only columns in that table. It can not refer to columns in other tables.
  4. The NULL values are often evaluated as true by check constraints. So using the NOT NULL constraints is the best method to control a field contains a NULL value or not.
  5. A Check Constraint can also use a scalar-valued user-defined function for evaluation logic.
  6. You can also disable the Check Constraint. But doing so turns the table column into a 'NonTrusted' state.



6. SQL DEFAULT Constraint


In SQL, a DEFAULT Constraint provides the default value when no value is supplied into the column. Actually, there should be some value in all the columns. So this DEFAULT constraint is used to specify a default value explicitly if none is supplied.

The ANSI SQL standard defines DEFAULT as being a column attribute whereas Microsoft implemented DEFAULT as a type of constraint. So you cannot change a DEFAULT using ALTER TABLE...ALTER COLUMN syntax. Therefore, you have to drop the DEFAULT Constraint and re-add the DEFAULT constraint with the new default value.





Example


A table can contain all the constraints in it and multiple constraints are also allowed except the Primary Key Constraint.

CREATE TABLE tblSQLConstraintsDemo
(
Id INT IDENTITY(1,1) CONSTRAINT PK_ID PRIMARY KEY CLUSTERED,  
Name VARCHAR(255) NOT NULL,  
Email VARCHAR(255) CONSTRAINT UK_Email UNIQUE NONCLUSTERED,  
Age INT CHECK(Age > 18),
ManagerID INT CONSTRAINT FK_ManagerID FOREIGN KEY  
      REFERENCES tblSQLConstraintsDemo(Id),
CreatedOn DATE DEFAULT GETDATE()
)


We can also add a constraint later by ALTER Command as well:

ALTER TABLE tblSQLConstraintsDemo ALTER COLUMN Email VARCHAR(255) NOT NULL;

ALTER TABLE tblSQLConstraintsDemo ADD CONSTRAINT UK_IdEmail UNIQUE (Id, Email);


SQL-CONSTRAINTS



We can delete the constraints by DROP Command:

ALTER TABLE tblSQLConstraintsDemo DROP CONSTRAINT UK_IdEmail;





Related Articles




Recommended Articles


Most Frequently Asked SQL Interview Questions
Most Frequently Asked C# Interview Questions
Foreign Key Interview Questions
Unique Key Interview Questions
Primary Key Interview Questions
Derived Table (DT) Interview Questions
Unicode vs Non-Unicode Data Types Interview Questions
Table Variable (TV) Interview Questions
Table-Valued Type (TVT) Interview Questions
Common Table Expression(CTE) Interview Questions
Temp Table Interview Questions
Static Class Interview Questions and Answers
Sealed Class Interview Questions and Answers
Sealed Method Interview Questions and Answers
Abstract Class vs Interface
Interface Interview Questions and Answers







Thanks for visiting this page. Please follow us on Twitter, Facebook, LinkedIn, Telegram, Youtube, and Quora for regular updates.