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
2
votes
1 answer

Oracle: Check constraint for both alphabetical and numerical characters in varchar2

So I want to check that the varchar2 is in the format of 4 alphabetical characters then 3 numerical characters e.g. AABB123 or LMNO987 So far I've tried: CONSTRAINT Code_Check check…
Chris Doogan
  • 37
  • 1
  • 4
2
votes
2 answers

SQL: Trouble in Check Constraint / Sequence

So i created a table, called "exam" with three columns "Name", "Rollno" and "Result P/F". I want it to do the following: When the name is input (via '&name' syntax), it automatically assigns the first name with the roll number (Rollno) 1501 AND then…
Anonymous Person
  • 1,437
  • 8
  • 26
  • 47
2
votes
2 answers

why is t-sql allowing me to violate a check constraint that uses a UDP?

I have a check constraint on a table in my DB. My understanding of a check is it sets a logical condition that must be true for records in a table. USE [myDB] GO ALTER TABLE [dbo].[myTable] WITH CHECK ADD CONSTRAINT [oneProgramPerTest] CHECK …
bernie2436
  • 22,841
  • 49
  • 151
  • 244
2
votes
1 answer

SQL Server Unique Combination check constraint

Possible Duplicate: Unique Constraint, excluding NULL values Lets say i have a table with addresses, and i want an owner to have only One main address. Fist do i define a index/key or a check constraint? What's the expression for…
Anestis Kivranoglou
  • 7,728
  • 5
  • 44
  • 47
2
votes
2 answers

How to create a concise SQL 'Check Constraint' that disallows certain chars for all string fields?

I have a SQL (SQL Server Express 2008) Table that contains a handful of VARCHAR fields. I would like to create a Check Constraint which will make sure that none of the VARCHAR values contain certain characters. Example: (Do not allow <, >, or ?…
Jed
  • 10,649
  • 19
  • 81
  • 125
2
votes
1 answer

Oracle auto generated check constraint

When I create table with columns not null, Oracle automatically creating check constraints to be not null: like this ( query from user_constraints view) NAME TYPE SEARCH_CONDITION ------------------------------ ----…
RustamIS
  • 697
  • 8
  • 24
2
votes
2 answers

TSQL Constraints on Temp Tables

Very quick and simple question. I am running a script to import data and have declared a temp table and applied check constraints to that table. Obviously if the script is run more than once I check whether the temp table already exists and if so, I…
StevenMcD
  • 17,262
  • 11
  • 42
  • 54
2
votes
3 answers

Date constants that are not fully specified

In Oracle what does "date constants that are not fully specified." mean? Any examples? It cannot be specified in the Check constraints and column default values according to the documentation.
ari
  • 171
  • 1
  • 5
1
vote
2 answers

Is it possible to associate Unique constraint with a Check constraint?

I have a table access whose schema is as below: create table access ( access_id int primary key identity, access_name varchar(50) not null, access_time datetime2 not null default (getdate()), access_type varchar(20) check…
Animesh
  • 4,926
  • 14
  • 68
  • 110
1
vote
1 answer

Allow bit field to be set only if another bit field is set

I have two bit fields in a table and on update I need to allow the second field to bet set (to 1) only if the first field was already set. So if a query updates the second field trying to set it to 1 the database must throw an error if the first…
net_prog
  • 9,921
  • 16
  • 55
  • 70
1
vote
2 answers

Is it possible to add a check constraint that calls a user defined function in a different database?

I'm trying to add a user defined function that actually calls the SQL# CLR function RegEx_IsMatch to a column, but I get this error: A user-defined function name cannot be prefixed with a database name in this context. But if the function is in a…
SumOfDavid
  • 281
  • 4
  • 13
1
vote
1 answer

How to constrain the number of records allowed in an SQL table?

Say I have two tables, Parent and Child. Parent has a MaxChildren (int) field and Child has an Enabled (bit) field and a ParentID (int) field linking back to the parent record. I'd like to have a constraint such that there can't be more than…
billpg
  • 3,195
  • 3
  • 30
  • 57
1
vote
1 answer

Enforcing maximum number of relations in concurrent requests

I have these entities in my business: class Team { public int Id { get; set; } public string Name { get; set; } public List Members { get; set; } } class Member { public int TeamId { get; set; } public int UserId { get;…
Daniel Jee
  • 664
  • 5
  • 10
1
vote
1 answer

CHECK constraint matching beginning of value

We are using sqlite3 for a database and ensuring values inserted into a column matching a specific string would be useful in our case. Example: CREATE TABLE people ("firstname" TEXT), CHECK(LIKE('ca%',"firstname")); The error we see is that sqlite3…
leetbacoon
  • 1,111
  • 2
  • 9
  • 32
1
vote
1 answer

Adding a named check constraint to a table

I am trying to add a named CHECK constraint to a table in MariaDB. As far as I can tell from the documentation and a few samples scattered around, the following syntax shouyld work: DROP TABLE IF EXISTS customers; CREATE TABLE customers ( id int…
Manngo
  • 14,066
  • 10
  • 88
  • 110
1 2 3
…
41 42