如何在 SQL Server 表列上设置约束？

Question

我在表中有一个叫做MealType的列（VARCHAR），它有一个CHECK约束条件，限制值为{"Veg", "NonVeg", "Vegan"}

这样做可以确保插入数据时符合约束条件。

我想显示这些选项供选择，但我无法找到查询特定表中列的约束条件的SQL语句。

从SQL Server系统表的第一次扫描来看，似乎需要使用SQL Server的API获取信息。我希望可以通过SQL查询本身来实现。

- Raja

---

将约束条件保存在“餐点类型表”中，这样如果除了素食和非素食之外还有更多选项，您可以轻松扩展。 - Elias

你使用的是哪个数据库？如果需要，约束可能在目录中。 - dcaswell

你使用哪个数据库管理系统？ - user330315

系统表中包含您所需的信息。如果您正在使用MS SQL，则sysobjects和syscolumns会很有用... - radarbob

我正在使用Microsoft SQL Server。 - Raja

同意Elias的观点 - 使用独立表中的值的外键约束比使用检查约束更合适。 - undefined

Answer 1

最简单和最快的方法是使用：

sp_help 'TableName'

- Zonus

---

这是一个非常糟糕的程序化获取信息的解决方案。 - Andre Figueiredo

没问题，作者并不想通过编程获取它们；他们只是想列出限制条件。如果你需要调整数据库或解决问题，sp_help是最简单的方法。 - Zonus

为什么你觉得他们不想以编程方式获取这些选项呢？"我想要展示这些选项供选择" 意味着这是为了在某个应用程序中以列表框或类似的方式展示，而他们没有接受你的答案。 - undefined

Answer 2

这个查询应该能显示表上所有的约束：

select chk.definition
from sys.check_constraints chk
inner join sys.columns col
    on chk.parent_object_id = col.object_id
inner join sys.tables st
    on chk.parent_object_id = st.object_id
where 
st.name = 'Tablename'
and col.column_id = chk.parent_column_id

可以使用这个语句替换select语句：

select substring(chk.Definition,2,3),substring(chk.Definition,9,6),substring(chk.Definition,20,5) 

- orgtigger

---

请原谅我的无知，这里的chk是什么？ - Raja

Chk 是我正在使用的 sys.check_constraints 的别名，你需要将约束条件分开吗？ - orgtigger

是的。感谢您的帮助 - 我以前从未使用过SQL。 - Raja

如果我错了，请纠正我，但我不明白这样能行得通。用互斥条件进行两次内连接永远不会返回任何结果。如果 chk.parent_object_id = col.object_id 成立，那么 chk.parent_object_id = st.object_id 就不可能成立，除非它们有相同的对象ID，这违背了拥有ID的原则。 - undefined

我并没有设计系统表，但我相信在所有情况下，object_id都是一个外键而不是主键。因此，列、表和约束都可以属于同一个“对象”。https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-objects-transact-sql?view=sql-server-ver16 - undefined

Answer 3

SELECT obj_table.NAME      AS 'table', 
        columns.NAME        AS 'column',
        obj_Constraint.NAME AS 'constraint',
        obj_Constraint.type AS 'type'

    FROM   sys.objects obj_table 
        JOIN sys.objects obj_Constraint 
            ON obj_table.object_id = obj_Constraint.parent_object_id 
        JOIN sys.sysconstraints constraints 
             ON constraints.constid = obj_Constraint.object_id 
        JOIN sys.columns columns 
             ON columns.object_id = obj_table.object_id 
            AND columns.column_id = constraints.colid 
    WHERE obj_table.NAME='table_name'
    ORDER  BY 'table'

- Ravinder Reddy Kottabad

---

'table_name'是表名本身，不需要在表名前加上'dbo.'。 - Mohammed Osman

Answer 4

您可以使用

标签来创建段落。

sp_helpconstraint 'tableName', 'nomsg'

获取表的所有约束条件。

“sp_help”返回更多信息。

- Mitio

Answer 5

以下内容对于检查和默认约束非常有用。我将其用于隐式约束，以提供名称应该是什么的指导。如果您删除where子句之后的所有内容，则适用于任何检查/默认约束。

SELECT /* obj_table.NAME      AS 'table', 
        columns.NAME        AS 'column',
        obj_Constraint.NAME AS 'constraint',
        obj_Constraint.type AS 'type',
        sss.name as 'schema',*/
        'ALTER TABLE [' + ltrim(rtrim(sss.name))+'].['+ltrim(rtrim(obj_table.name)) + '] DROP CONSTRAINT [' + obj_Constraint.NAME + '];' As 'Wrong_Implicit_Constraint',
        'ALTER TABLE [' + ltrim(rtrim(sss.name))+'].['+ltrim(rtrim(obj_table.name)) + '] ADD CONSTRAINT [' + CASE obj_Constraint.type 
        WHEN 'D' THEN 'DF' WHEN 'F' THEN 'FK' 
        WHEN 'U' THEN 'UX' WHEN 'PK' THEN 'PK' WHEN 'N' THEN 'NN' WHEN 'C' THEN 'CK' 
        END + '_' + ltrim(rtrim(obj_table.name)) + '_' + columns.NAME + ']' +
        CASE obj_Constraint.type WHEN 'D' THEN ' DEFAULT (' + dc.definition +') FOR [' + columns.NAME + ']'
        WHEN 'C' THEN ' CHECK (' + cc.definition +')'
        ELSE '' END +
        ';' As 'Right_Explicit_Constraint'
    FROM   sys.objects obj_table 
        JOIN sys.objects obj_Constraint ON obj_table.object_id = obj_Constraint.parent_object_id 
        JOIN sys.sysconstraints constraints ON constraints.constid = obj_Constraint.object_id 
        JOIN sys.columns columns ON columns.object_id = obj_table.object_id 
            AND columns.column_id = constraints.colid 
        left join sys.schemas sss on obj_Constraint.schema_id=sss.schema_id 
        left join sys.default_constraints dc on dc.object_id = obj_Constraint.object_id
        left join sys.check_constraints cc on cc.object_id = obj_Constraint.object_id
    WHERE obj_Constraint.type_desc LIKE '%CONSTRAINT'
    AND RIGHT(obj_Constraint.name,10) LIKE '[_][_]________' --match double underscore + 8 chars of anything
    AND RIGHT(obj_Constraint.name,8) LIKE '%[A-Z]%'          --Ensure alpha in last 8
    AND RIGHT(obj_Constraint.name,8) LIKE '%[0-9]%'                 --Ensure numeric in last 8
    AND RIGHT(obj_Constraint.name,8) not LIKE '%[^0-9A-Z]%' --Ensure no special chars

- Nicholas McQuillen

Answer 6

感谢orgtrigger提供的示例！我对其进行了改进，使其能够删除不必要的约束（然后创建它们的修改版本，如果需要的话）。也许这段代码对任何人都有用。

  -- removing old constraints
DECLARE @ConstraintNames TABLE (Name VARCHAR(MAX), RowNum INT)
DECLARE @TableName VARCHAR(100) = 'HubSpot'

INSERT @ConstraintNames
  SELECT [constraint].name,
         ROW_NUMBER() OVER (ORDER BY [constraint].[name]) AS RowNum
  FROM sys.default_constraints [constraint]
  INNER JOIN sys.columns col
      ON [constraint].parent_object_id = col.object_id
  INNER JOIN sys.tables st
      ON [constraint].parent_object_id = st.object_id
  WHERE 
  st.name = @TableName 
  AND col.name IN ('ForceUpdateOnImport', 'ForceUpdateOnExport')
  AND col.column_id = [constraint].parent_column_id
  
SELECT * FROM @ConstraintNames

DECLARE @i INT = 1,
        @count INT,
        @constraintName VARCHAR(MAX),
        @sql VARCHAR(MAX)
SELECT @count = COUNT(1) FROM @ConstraintNames

WHILE @i <= @count 
  BEGIN 
    SELECT @constraintName = cn.Name FROM @ConstraintNames cn WHERE cn.RowNum = @i
    SET @sql = 'ALTER TABLE ' + @TableName + ' DROP CONSTRAINT ' + @constraintName
      
    EXEC (@sql)
    SET @i = @i + 1
  END

- Gennady Maltsev

Answer 7

查询获取约束信息的语句：

SELECT t1.TABLE_NAME, t1.COLUMN_NAME, STRING_AGG(COALESCE(t1.CONSTRAINT_TYPE,''),','), t1.ORDINAL_POSITION, STRING_AGG(COALESCE(t1.reference_table,''),'') AS reference_table, STRING_AGG(COALESCE(t1.reference_column,''),'') AS reference_column
FROM
(SELECT c.TABLE_NAME, c.COLUMN_NAME, tc.CONSTRAINT_TYPE, ORDINAL_POSITION, t2.TABLE_NAME AS reference_table, t2.COLUMN_NAME AS reference_column
FROM INFORMATION_SCHEMA.COLUMNS c
LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu ON c.COLUMN_NAME = ccu.COLUMN_NAME AND c.TABLE_NAME = ccu.TABLE_NAME
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc ON ccu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
LEFT JOIN (SELECT  ccu1.TABLE_NAME, ccu1.COLUMN_NAME, rc.UNIQUE_CONSTRAINT_NAME, rc.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc 
LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu1 ON ccu1.CONSTRAINT_NAME = rc.UNIQUE_CONSTRAINT_NAME)t2
ON ccu.CONSTRAINT_NAME = t2.CONSTRAINT_NAME
GROUP BY c.TABLE_NAME, c.COLUMN_NAME, tc.CONSTRAINT_TYPE,ORDINAL_POSITION, t2.TABLE_NAME, t2.COLUMN_NAME)t1
GROUP BY t1.TABLE_NAME, t1.COLUMN_NAME,t1.ORDINAL_POSITION
ORDER BY t1.TABLE_NAME, t1.ORDINAL_POSITION

- Aayush Nigam

---

请记住，Stack Overflow 不仅仅是为了解决眼前的问题，还要帮助未来的读者找到类似问题的解决方案，这需要理解底层代码。这对于我们社区中的初学者尤其重要，因为他们可能不熟悉语法。鉴于此，你能否[编辑]你的回答，包括解释你正在做什么以及为什么你认为这是最佳方法？ - undefined