Is It possible to add same constraint to multiple tables?

Question

I have generated some code to do this in multiple tables. Error I get doing this is:

Variables are not allowed in the ALTER TABLE statement.

I understand that. The code I've generated :

DECLARE
@tableName VARCHAR(50) = 'myTable',
@sql NVARCHAR(100),
@parameter1 CHAR(1) = 'A',
@parameter2 CHAR(2) = 'I'

SELECT @sql = N'ALTER TABLE '+@tableName+'
               ADD CONSTRAINT CK_Status
               CHECK (Status in (@parameter1, @parameter2))'
EXEC sp_executesql @sql,N'@parameter1CHAR(1), @parameter2 CHAR(1)',@parameter1,@parameter2

I know it doesn't work. But I'd like if It is possible in some way, because I have a lot of tables to apply this.

NOTE: - I have a code to get tables and names and everything, I just need some idea to add the char values in the string.

given that you're hardcoding your check values into the variables, why not just put them directly into the exec call? no point in using a variable if the value never changes. `exec sp_executesql @sql, N''A' char1(1), 'I' char(1) etc...` — Marc B, Sep 25 '15 at 20:23

score 1 · Accepted Answer · answered Sep 25 '15 at 20:22

You can add the same constraint, but not with the same name. A simple way is just to leave the name out. And, put the parameters directly in the query:

DECLARE
    @tableName VARCHAR(50) = 'myTable',
    @sql NVARCHAR(100),
    @parameter1 CHAR(1) = 'A',
    @parameter2 CHAR(2) = 'I';

SELECT @sql = N'ALTER TABLE @tableName
               ADD CONSTRAINT CHECK (Status in (''@parameter1'', ''@parameter2''))';

SET @sql = REPLACE(@sql, '@tableName', @tableName);
SET @sql = REPLACE(@sql, '@parameter1', @parameter1);
SET @sql = REPLACE(@sql, '@parameter2', @parameter2);

EXEC sp_executesql @sql;

If you have to deal with this constraint in many tables, you might consider having a table with valid values of status and using a foreign key constraint instead.

I've considered that. But thats not my problem. The result of the @sql u put there is : ALTER TABLE myTable ADD CONSTRAINT CHECK (Status in (A,I)) Note the state of CHAR values. The correct would be: ALTER TABLE myTable ADD CONSTRAINT CHECK (Status in ('A','I')) — Carlos Andres, Sep 25 '15 at 20:52
@CarlosAndres . . . This query puts single quotes around the parameters. — Gordon Linoff, Sep 25 '15 at 22:36

score 0 · Answer 2 · answered Sep 28 '15 at 05:16

Yes you can do this from this example i didn't test it, but it should work.

ALTER TABLE first_Table
Add Constraint first_Table_FK1 FOREIGN KEY (table1_id)
References second_Table (table2_id),
Add Constraint first_Table_UQ1 Unique (table1_id)

Is It possible to add same constraint to multiple tables?

2 Answers2