-5

Is there a way to enforce referential integrity without foreign keys? Is there a way to achieve what I am trying to do below with alter table statement?

ALTER TABLE no.Man  
WITH CHECK ADD  CONSTRAINT chk_Son_Weight CHECK
    (Son_Weight IN (Select distinct Weight from no.Man))
GO

I got the below error by using the code above

Subqueries are not allowed in this context. Only scalar expressions are allowed.

Olivier Jacot-Descombes
  • 104,806
  • 13
  • 138
  • 188
LifetimeLearner4706
  • 5
  • 5
  • 5
    Can you explain _why_ you want referential integrity but you don't want to use the primary and most efficient way to maintain referential integrity? – Aaron Bertrand Jan 13 '22 at 17:30
  • 6
    This smells like an [XY Problem](//xyproblem.info). It's like saying "I have a screw and the correct shaped and sized Screwdriver for the Screw. I don't want to use the Screwdriver. Is there a different tool I can use, like a hammer?" – Thom A Jan 13 '22 at 17:30
  • Please dont dismiss without knowing the whole story. I am unable to change the primary key values because that gave me an error since those values were there in the foreign key. – LifetimeLearner4706 Jan 13 '22 at 17:37
  • 1
    Nobody's dismissing, we're asking questions. But I don't understand your explanation. And is it really referential integrity _within a single table_? – Aaron Bertrand Jan 13 '22 at 17:38
  • I also was getting an error saying that "introducing foreign key will lead to multiple cascade paths" – LifetimeLearner4706 Jan 13 '22 at 17:39
  • yes. It is a same table reference. That is a business need. We have large data. – LifetimeLearner4706 Jan 13 '22 at 17:41
  • 1
    Like Larnu suggests, sounds like XYZ. You're trying to avoid foreign keys because you got some errors implementing a foreign key, so you're asking how to do it a different way. Why not show the attempt you made with foreign keys and we can try to solve these issues instead of finding an inferior workaround? – Aaron Bertrand Jan 13 '22 at 17:44
  • Yep, this is a XY Problem. I suspected this due to your [prior question](https://stackoverflow.com/q/70697307/2029983). My point from that question stands: You've likely chosen the wrong columns for your PK (`height` and `width` don't sound like good Primary Key Candidates); it should rarely/never change. I would suggest that you look into changing what column(s) you use as your primary and foreign key constraints. – Thom A Jan 13 '22 at 17:48
  • As I said Larnu. This is a business need. Lot of tables depend on the primary key pair of columns I am talking about. – LifetimeLearner4706 Jan 13 '22 at 17:50
  • So you'll have lots of tables to change what column(s) they use as the primary and foreign keys, @ProblemSolver007 . That is your solution. – Thom A Jan 13 '22 at 17:50
  • I can simply separate the foreign key columns from the table and create a separate table but that seems like running away from the problem. – LifetimeLearner4706 Jan 13 '22 at 17:51
  • As I said before, Height and Weight are both dummy names I have assigned to avoid giving out actual names. I am avoiding to post actual names on public forums. – LifetimeLearner4706 Jan 13 '22 at 17:53
  • Column names are *very* unlikely to give information away, @ProblemSolver007 . – Thom A Jan 13 '22 at 18:00
  • I am still new to this type of environment. "It is better to be safe earlier than sorry later". I started at the new company recently and I am unaware of the rules. – LifetimeLearner4706 Jan 13 '22 at 18:04
  • 2
    You would likely be better off giving us more information about what the real problem here @ProblemSolver007 . Enforcing integrity but a different method is only going to make things slower, and harder to debug. – Thom A Jan 13 '22 at 18:05
  • 3
    So you don't like foreign keys because you got an error you can't solve, you don't like recursion because you're afraid of it, you admit your schema is not optimal but you won't change it. SQL Server does offer the things you need, you're just choosing not to use them. – Aaron Bertrand Jan 13 '22 at 18:53
  • 3
    *"wonder sql server has tough competition from various other products - doesn't do simple stuff"* this is most certainly a poor workman blaming their tools. It isn't SQL Server's fault that you have a poorly design database, that's the fault of the person that designed it. In actuality SQL Server is doing exactly what is is design to do, and what the design of your database told it to: stop someone from ever changing the height and weight of a row as it would invalidate the integrity of the database. Fix the design, and the whole problem disappears. – Thom A Jan 13 '22 at 19:30
  • Im stuck at this since three days. im sorry for talking bad about sql server. i understand its my and several other people's daily bread and butter. . I would be really grateful if someone could reply if the recursion solution I have written here is correct- https://stackoverflow.com/questions/70678215/on-update-cascade-for-a-self-referencing-key-sql-server and whether it would address my issue. If not correct then please can someone help me with correct solution. – LifetimeLearner4706 Jan 13 '22 at 19:57
  • ^ agreed, I would actually never in a million years use cascade functions (in any product, not just SQL Server). Just because a feature is there does not mean it's for everyone. – Aaron Bertrand Jan 13 '22 at 20:11
  • Invalid code doesn't tell us "what I am trying to do". PS Please clarify via edits, not comments. PS [mre] [ask] [Help] – philipxy Jan 14 '22 at 02:28

1 Answers1

4

I'm not sure I understand why you think this is better than foreign keys, but yes, you can implement referential integrity in other (inferior) ways. These will be slower than doing it right and fixing the design.

  1. Check constraint + UDF

     CREATE FUNCTION dbo.IsItAValidWeight(@Son_Weight int)
 RETURNS bit
 WITH SCHEMABINDING
 AS
 BEGIN
   RETURN 
   (
      SELECT CASE WHEN EXISTS 
      (
        SELECT 1
        FROM no.Man WHERE Weight = @Son_Weight
      ) THEN 1 ELSE 0 END
   );
 END
 GO

 ALTER TABLE no.Man WITH CHECK 
   ADD CONSTRAINT chk_Son_Weight 
   CHECK dbo.IsItAValidWeight(Son_Weight) = 1;

  2. Trigger

    Need to know a lot more about the schema I think, but you can research.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • Thank u sir. Really appreciate your help. But since u r saying its an inferior solution - I am hesitant to use it because the data is really big and if its inferior - im sure it wont work. Yet, Im open to suggestions. – LifetimeLearner4706 Jan 13 '22 at 19:59
  • 2
    @ProblemSolver007 If your car is broken and you don't have the time or money to fix it today, but you need to get to work, you may need to take the bus. That's inferior, but it's still a solution. – Aaron Bertrand Jan 13 '22 at 20:09