0

I want to ask a silly question which I didn’t found online. Question is if I create a SQL Server database without any relational key linked physically then is it good practice? I want to name the related table id as only ‘Required’ mode so to insert data this firld must need to be filled but I don’t want to link it with its real table id.

The reason I wanted to do it because my database will be too large and I wanted to make it salable if i use real linked id to each table then it will be very very messed and its hard for me to continue grow tables even sometimes it will be hard to delete data since it will have lot of relational tables.

So is it best practice? What’s the benefits to use linked id between tables? Looking for expert suggestion. Please note I am using Microsoft SQL Server.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
John Doe
  • 317
  • 1
  • 3
  • 8

1 Answers1

3

The reason I wanted to do it bcoz my database will be too large and I wanted to make it salable if i use real linked id to each table then it will be very very messed

This is not true. Relational constraints do become an issue at scale, in huge (distributed) databases - but for 99% of applications that can run in a single database instance then they're a very good idea, they're not computationally expensive, and they don't make a database "messed" - and they don't take up any space either, so they won't make your database too large.

its hard for me to continue grow tables even sometimes it will be hard to delete data since it will have lot of relational tables.

That's intentional: it's to stop you from deleting data that other data depends on, but if you want "cascading delete" behaviour (where deleting a child record will delete its parent) then use it: How do I use cascade delete with SQL Server?

So is it best practice?

I believe current best-practice is to use relational constraints until you come across a situation where you know you can't use them.

What’s the benefits to use linked id between tables?

To enforce data integrity rules: to ensure that programs and their users cannot corrupt the database by updating data to point to incorrect - or absent - data.

Looking for expert suggestion.

No comment.

Please note I am using Microsoft SQL Server.

Relational constraints are generally the same across all major RDBMS implementations.

Dai
  • 141,631
  • 28
  • 261
  • 374