- Remove From My Forums
UNABLE to ADD or DROP a Constraint — SQL Complains!
-
Question
-
Hi all, I am trying to create a CONSTRAINT but for some reason T-SQL does not allow me to do that.
When I try to DROP a CONSTRAINT it says:
Msg 3728, Level 16, State 1, Line 13
‘DF_TBL_SyncTable_DEVUK‘ is not a constraint.
Msg 3727, Level 16, State 0, Line 13
Could not drop constraint. See previous errors.When I try to ADD a CONSTRAINT it says:
Msg 1781, Level 16, State 1, Line 14
Column already has a DEFAULT bound to it.
Msg 1750, Level 16, State 0, Line 14
Could not create constraint. See previous errors.For some reason I can’t win here. Can’t drop it nor can I create one. Any solution?
Code Snippet
ALTER TABLE TBL_SyncTable DROP CONSTRAINT DF_TBL_SyncTable_DEVUK
ALTER TABLE TBL_SyncTable ADD CONSTRAINT GOD_TBL_SyncTable_DEVUK DEFAULT 2 FOR DEVUK
Thanks for comments + suggestions.*UPDATE*
I am trying to use the following code to check if the SCHEMA exists but still no luck. For some reason when I create it and wrap an IF statement around it, it doesn’t detect the SCHEMA. Is something wrong with my code?
Code Snippet
IF EXISTS(
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_SCHEMA = ‘dbo’
AND CONSTRAINT_NAME = ‘DF_TBL_SyncTable_DEVUK’
AND TABLE_NAME = ‘TBL_SyncTable’
)
SELECT * FROM TABLE_1
ELSE
ALTER TABLE TBL_SyncTable ADD CONSTRAINT DF_TBL_SyncTable_DEVUK DEFAULT 2 FOR DEVUK
Error Message:
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
Severity level:
16.
Description:
This error message appears when as a result of a previous error a constraint cannot be created.
Consequences:
The T-SQL statement can be parsed, but causes the error at runtime.
Resolution:
Error of the Severity Level 16 are generated by the user and can be fixed by the SQL Server user. The statement cannot be executed this way. The root cause for the previous error must be corrected so that the constraint can be created successfully.
Versions:
All versions of SQL Server.
Example(s):
CREATE TABLE #t
(
t1 TIMESTAMP NOT NULL DEFAULT 0
)
Remarks:
In the above example we try to create a DEFAULT constraint for a column of the TIMESTAMP data type. Because you cannot create such constraints for columns of that type, the error 1755 is raised. And because the constraint wasn’t created successfully, this error is also raised.
]]>
The error message is actually pretty clear in describing exactly what the problem is.
You’re trying to add a foreign key that says «For every Team Name and Location in the Player table, there must be a matching Team Name and Location in the Team table».
The error message is explicitly telling you it can’t add the constraint because this is not the case: there is a Team Name and Location in the Player table that DOES NOT exist in the Team table.
A query like this should list out the rows that are the problem… and you’ll need to add rows with these Team Names and Locations to your Team table (or delete these rows from the Player table) before you can successfully add the constraint:
SELECT *
FROM dbo.Player p
WHERE NOT EXISTS (SELECT *
FROM dbo.Team t
WHERE t.Team_Name = p.Team_Name AND t.Team_Location = p.Team_Location)
Additionally, a Foreign key reference must reference a primary key index. Since the primary key in this case is on the first and last names, rather than the team name and location, it can’t apply the FK constraint. It needs to do this so it can do efficient look-ups and have uniqueness guarantees in order to validate the constraint.