Sub queries in check constraint

by

Note, what you really want is a foreign key constraint. That said, to get a “query” into a check you can write a function that contains the query and outputs a scalar value, then use that function in the check constraint.

CREATE FUNCTION myFunction (
    @field DATATYPE(?)
)
RETURNS VARCHAR(5)
AS
BEGIN
    IF EXISTS (SELECT* FROM Table2 WHERE MYFIELD = @field)
        return 'True'
    return 'False'
END

Something like that. Not tested.

Then you can add it to your check like so

ALTER TABLE Table1
    WITH CHECK ADD CONSTRAINT CK_Code
    CHECK (myFunction(MYFIELD) = 'True')

More Related Contents:

  1. How to delete 0 values but not blanks from a column in SQL Server 2008 programming?
  2. to select all the employees who have joined most recently under king
  3. Unpivot with column name
  4. How can I delete using INNER JOIN with SQL Server?
  5. What this query does to create comma delimited list SQL Server?
  6. SQL Server – Return value after INSERT
  7. Convert varchar into datetime in SQL Server
  8. sql query to return differences between two tables
  9. SQL Joins Vs SQL Subqueries (Performance)?
  10. Check if a row exists, otherwise insert
  11. How to do pagination in SQL Server 2008
  12. Convert Xml to Table SQL Server
  13. How to select the last record of a table in SQL?
  14. How to create table using select query in SQL Server?
  15. How do I remove redundant namespace in nested query when using FOR XML PATH
  16. Group DateTime into 5,15,30 and 60 minute intervals
  17. concatenate two database columns into one resultset column
  18. Is there a SELECT … INTO OUTFILE equivalent in SQL Server Management Studio?
  19. SQL SERVER: Get total days between two dates
  20. SQL Server 2008 – Help writing simple INSERT Trigger
  21. Store multiple bit values in a single table column
  22. SQL Server equivalent to MySQL enum data type?
  23. Grant SELECT permission on a view, but not on underlying objects
  24. Oracle 11g – Check constraint with RegEx
  25. Improve SQL Server query performance on large tables
  26. Using IF ELSE statement based on Count to execute different Insert statements
  27. Average of multiple columns
  28. Show all rows that have certain columns duplicated
  29. Restrict varchar() column to specific values?
  30. How to execute a stored procedure inside a select query

Leave a Comment