Stack Overflow
Stack Overflow

Questions tagged [check-constraints]

CHECK constraints enforce domain integrity by limiting the values that are accepted by one or more columns.

SQL CHECK constraints enforce domain integrity by limiting the values that are accepted by one or more columns.

You can create a CHECK constraint with any logical (Boolean) expression that returns TRUE or FALSE based on the logical operators. For example, the range of values for a salary column can be limited by creating a CHECK constraint that allows for only data that ranges from $15,000 through $100,000. This prevents salaries from being entered beyond the regular salary range. The logical expression would be the following: salary >= 15000 AND salary <= 100000.

624 questions
169
votes
8 answers

WITH CHECK ADD CONSTRAINT followed by CHECK CONSTRAINT vs. ADD CONSTRAINT

I'm looking at the AdventureWorks sample database for SQL Server 2008, and I see in their creation scripts that they tend to use the following: ALTER TABLE [Production].[ProductCostHistory] WITH CHECK ADD CONSTRAINT…
Wayne Molina
  • 19,158
  • 26
  • 98
  • 163
141
votes
8 answers

CHECK constraint in MySQL is not working

First I created a table like CREATE TABLE Customer ( SD integer CHECK (SD > 0), Last_Name varchar (30), First_Name varchar(30) ); and then inserted values in that table INSERT INTO Customer values ('-2','abc','zz'); MySQL doesn't show an…
JohnRaja
  • 2,377
  • 6
  • 26
  • 47
60
votes
2 answers

SQL Sub queries in check constraint

Can I make SQL sub queries in Check constraint ? I've a post table with columns id, owner I've another table action with columns user_id, post_id Table user with columns id post_id -> post.id and user_id -> user.id also post.owner -> user.id Now I…
Dipro Sen
  • 4,350
  • 13
  • 37
  • 50
49
votes
6 answers

How to add Check Constraints for Django Model fields?

While subclassing db.models.Model, sometimes it's essential to add extra checks/constraints. For example, I have an Event model with start_date and end_date: I want to add validation into the fields or the model so that end_date > start_date. At…
Viet
  • 17,944
  • 33
  • 103
  • 135
44
votes
5 answers

How do I add a custom CHECK constraint on a MySQL table?

I am having trouble with this table CREATE TABLE `Participants` ( `meetid` int(11) NOT NULL, `pid` varchar(15) NOT NULL, `status` char(1) DEFAULT NULL, PRIMARY KEY (`meetid`,`pid`), CONSTRAINT `participants_ibfk_1` FOREIGN KEY (`meetid`)…
Mathias Bak
  • 4,687
  • 4
  • 32
  • 42
43
votes
4 answers

Sub queries in check constraint

I have table designed in SQL-Server 2008 R2. I have a column in that table which needs to be checked against another table when data is inserting. ALTER TABLE Table1 WITH CHECK ADD CONSTRAINT CK_Code CHECK (MyField in (Select Field…
Highland
  • 453
  • 1
  • 4
  • 7
36
votes
4 answers

How do I create a multiple-table check constraint?

Please imagine this small database... Diagram removed dead ImageShack link - volunteer database diagram Tables Volunteer Event Shift EventVolunteer ========= ===== ===== ============== Id Id …
Zack Peterson
  • 56,055
  • 78
  • 209
  • 280
29
votes
3 answers

Date range overlapping check constraint

I've a simple table in sql server 2005 with 3 columns: DateStart, DateEnd and Value. I tried to set a "table check constraint" to avoid inserting overlapping records. For instance if in such table there is a record with DateStart = 2012-01-01 (first…
Antonio Manello
  • 464
  • 1
  • 6
  • 12
26
votes
3 answers

SQL Server: How to make server check all its check constraints?

It seems that some scripts generated by Enterprise Manager* (or not, it doesn't matter) created check constraints WITH NOCHECK. Now when anyone modifies the table, SQL Server is stumbling across failed check constraints, and throwing errors. Can i…
Ian Boyd
  • 246,734
  • 253
  • 869
  • 1,219
23
votes
1 answer

Add constraint to existing SQLite table

I'm using SQLite, which doesn't support adding a constraint to an existing table. So I can't do something like this (just as an example): ALTER TABLE [Customer] ADD CONSTRAINT specify_either_phone_or_email CHECK (([Phone] IS NOT NULL) OR ([Email] IS…
grokky
  • 8,537
  • 20
  • 62
  • 96
23
votes
4 answers

PostgreSQL check constraint for foreign key condition

I have a table of users eg: create table "user" ( id serial primary key, name text not null, superuser boolean not null default false ); and a table with jobs: create table job ( id serial primary key, description text ); the…
microo8
  • 3,568
  • 5
  • 37
  • 67
18
votes
1 answer

How can I create a CHECK constraint on a VARCHAR column in SQL Server specifying a minimum data length?

I have a VARCHAR(30) column in a Microsoft SQL Server database. I'd like to add a CHECK constraint that does not allow a value in the column to be less than 3 characters. What is the expression I must use?
Jake Petroules
  • 23,472
  • 35
  • 144
  • 225
17
votes
4 answers

Create DB Constraint via Django

I have a Django model which looks like this: class Dummy(models.Model): ... system = models.CharField(max_length=16) I want system never to be empty or to contain whitespace. I know how to use validators in Django. But I would enforce this…
guettli
  • 25,042
  • 81
  • 346
  • 663
17
votes
5 answers

SQL can I have a "conditionally unique" constraint on a table?

I've had this come up a couple times in my career, and none of my local peers seems to be able to answer it. Say I have a table that has a "Description" field which is a candidate key, except that sometimes a user will stop halfway through the…
Michael Blackburn
  • 720
  • 1
  • 5
  • 13
15
votes
3 answers

Create custom error message in check constraints in SQL SERVER 2008

I'd like to see the ability to attach custom error messages to CONSTRAINT objects, specifically CHECK constrints. Either directly or via a custom error number in sysmessages. I've seen developers have to create triggers. I think that's not a good…
oscar.fimbres
  • 1,145
  • 1
  • 13
  • 24
1
2 3
…
41 42