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.
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.
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;
Trigger
Need to know a lot more about the schema I think, but you can research.