Understanding SQL Server: Databases, Keys, Constraints & More – A Beginner’s Guide

Working with SQL Server? Whether you’re just starting out or brushing up on your fundamentals, understanding how databases, keys, and constraints work is essential for managing your data efficiently.

In this blog, we’ll break down key concepts like database creation, object dependencies, foreign keys, and constraints in a clear and practical way—perfect for students, beginners, and professionals preparing for interviews.

Let’s get started!

 

 1️⃣ How to Create a Database with Special Characters

Ever wondered if a database name like SQLSCHOOL.COM is valid in SQL Server? The answer is yes—you just need to use square brackets or quotes.

✅ Example:
sql
CopyEdit
CREATE DATABASE [SQLSCHOOL.COM]
or
sql
CopyEdit
CREATE DATABASE “SQLSCHOOL2.COM”

Note: Brackets ([ ]) or double quotes (” “) allow special characters like periods (.) in object names.

2️⃣ How to Check Which Database You’re Connected To

Need to verify the current database? Use the built-in function DB_NAME().

✅ Example:
sql
CopyEdit
SELECT DB_NAME()
You can also switch databases using the USE command:
sql
CopyEdit
USE [SQLSCHOOL.COM]
SELECT DB_NAME()

This will confirm you’re connected to the right database.

3️⃣ How Many Objects Are Auto-Created When You Create a Table with Constraints?

Take a look at this CREATE TABLE statement:
sql
CopyEdit
CREATE TABLE EMPLOYEE_DETAILS

(
EmpID int PRIMARY KEY,
EmpName varchar(30) UNIQUE,
EmpManagerID int REFERENCES EMPLOYEE_DETAILS (EmpID),
EmpSal float CHECK (EmpSal >= 100000)
)

✅ Level 1 Answer: 7 Objects Are Auto-Created

1. Table
2. Primary Key
3. Clustered Index
4. Unique Constraint
5. Non-clustered Index
6. Foreign Key
7. Check Constraint

✅ Level 2 Answer: 9 Objects (More Advanced)

1. Statistics for Primary Key
2. Statistics for Unique Key

These automatic objects help SQL Server optimize performance, enforce data rules, and maintain relationships.

4️⃣ How to Find Objects Related to a Table (Dependencies)

To list all database objects:
sql
CopyEdit
SELECT * FROM SYS.OBJECTS

To check objects related to a specific table like EMPLOYEE_DETAILS:
sql
CopyEdit
SELECT * FROM SYS.OBJECTS WHERE NAME = ‘EMPLOYEE_DETAILS’

You can also use this combination to find related objects by ID:
sql
CopyEdit
SELECT name FROM SYS.OBJECTS WHERE OBJECT_ID = 901578250
UNION
SELECT name FROM SYS.OBJECTS WHERE parent_object_id = 901578250
UNION ALL
SELECT name FROM SYS.INDEXES WHERE OBJECT_ID = 901578250

This helps you analyze dependencies, indexes, and references tied to a particular table.

5️⃣ Can a Foreign Key Allow NULL Values?

Yes! A Foreign Key (FK) can accept NULL values. Let’s look at an example:
sql
CopyEdit
INSERT INTO EMPLOYEE_DETAILS VALUES (1001, ‘SAI’, NULL, 999999)
INSERT INTO EMPLOYEE_DETAILS VALUES (1002, ‘SAI2’, NULL, 6666)
INSERT INTO EMPLOYEE_DETAILS VALUES (1003, ‘SAI3’, NULL, NULL)

✅ Rule:

• A Foreign Key can accept:
  • Values matching the parent key
  • Or NULL
• A Check Constraint can accept:
  • Values that match the condition
  • Or NULL

So yes—NULL is valid in both Foreign Keys and Check Constraints unless explicitly restricted.

6️⃣ Which is More Powerful: NULL/NOT NULL or Primary Key?

Interesting question! Let’s break it down.

• A Primary Key (PK) does not allow NULLs and ensures uniqueness.
• A NOT NULL column allows duplicates but forces every row to have a value.
• A NULL field offers flexibility—FKs and CKs allow NULL when applicable.

✅ Conclusion:

• NOT NULL and NULL give you more flexibility depending on your data rules.
• A Primary Key is strict—ideal for unique record identification, but not always the most flexible.

💬 Want to Learn More or Practice Live?

📞 For clarifications or doubts, feel free to reach out:

• Instructor Help: +91 9030040801
• Training & Projects: +91 9666640801

🎓 Learn SQL Server the Right Way — Join SQL School Training Institute!

Whether you’re starting from scratch or leveling up your skills, SQL School’s industry-ready training can help you become a pro in SQL, Data Engineering, Power BI, Azure, and Microsoft Fabric.

✅ Why Choose SQL School?

• 100% Hands-on Labs
• Real-Time Projects
• Expert Trainers
• Online & Classroom Options
• Certification + Placement Support
• 📅 Enroll Today – Limited Seats!
  📞 Call/WhatsApp: +91 9666640801
  🌐 Visit: www.sqlschool.com

Final Thoughts

SQL Server has powerful tools and built-in features to help you manage your data better. From understanding how to create databases with special characters to knowing the inner workings of keys and constraints, these basics form the foundation of every data engineer and developer’s journey.

🚀 Ready to master SQL? Start today with SQL School!