Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [constraint]

A declarative mechanism such as a check or foreign key that enforces some data integrity rule in a database.

Database management systems can enforce various types of declarative constraints that assert certain invariants on the data in the database, rejecting attempts to enter data that violates those constraints. The constraints apply and maintain data integrity rules on the data contained. Some types of constraints are:

Foreign keys constrain values in database columns to values present in a reference table.

Check constraints Apply a logical condition on one or more columns and reject any rows that cause the condition to evaluate to false.

Not Null constraints Require a column to contain a value.

Unique constraints Require a column or columns to contain a unique value or combination of values, so that no two rows can have the same set of values.

Primary keys are a specific constraint intended to guarantee uniqueness of the identifying column or columns of a table.

Column defaults are values that are entered if a row is entered into the database without a value specified for that column.

479 questions
266
votes
7 answers

When should I use a unique constraint instead of a unique index?

When I want a column to have distinct values, I can either use a constraint create table t1( id int primary key, code varchar(10) unique NULL ); go or I can use a unique index create table t2( id int primary key, code varchar(10) NULL ); go create…
bernd_k
  • 12,001
  • 23
  • 74
  • 109
145
votes
3 answers

PostgreSQL multi-column unique constraint and NULL values

I have a table like the following: create table my_table ( id int8 not null, id_A int8 not null, id_B int8 not null, id_C int8 null, constraint pk_my_table primary key (id), constraint u_constrainte unique (id_A, id_B,…
Manuel Leduc
  • 1,561
  • 2
  • 10
  • 5
80
votes
4 answers

Check constraint only one of three columns is non-null

I have a (SQL Server) table that contains 3 types of results: FLOAT, NVARCHAR(30), or DATETIME (3 separate columns). I want to ensure that for any given row, only one column has a result and the other columns are NULL. What is the simplest check…
David Clarke
  • 1,157
  • 2
  • 10
  • 17
59
votes
4 answers

How to check foreign keys related to a table

How to see foreign keys related to a table in MySql? Background : I wanted to drop a table in MySql which has a foreign key constraint. When I do it I get this: Error Code: 1217. Cannot delete or update a parent row: a foreign key constraint…
Zameer Ansari
  • 901
  • 1
  • 12
  • 19
56
votes
2 answers

PostgreSQL EXCLUDE USING error: Data type integer has no default operator class

In PostgreSQL 9.2.3 I am trying to create this simplified table: CREATE TABLE test ( user_id INTEGER, startend TSTZRANGE, EXCLUDE USING gist (user_id WITH =, startend WITH &&) ); But I get this error: ERROR: data type integer has no…
Ian Timothy
  • 845
  • 1
  • 7
  • 10
55
votes
2 answers

Differences between MATCH FULL, MATCH SIMPLE, and MATCH PARTIAL?

I've noticed a MATCH SIMPLE and MATCH FULL, but I don't understand what they do. I see default is MATCH SIMPLE; but, how do the other MATCH clauses to the FOREIGN KEY constraint function?
user32234
48
votes
7 answers

Why does Postgres generate an already used PK value?

I'm using Django, and every once in a while I get this error: IntegrityError: duplicate key value violates unique constraint "myapp_mymodel_pkey" DETAIL: Key (id)=(1) already exists. My Postgres database does in fact have a myapp_mymodel object…
orokusaki
  • 1,099
  • 2
  • 11
  • 18
40
votes
5 answers

Why does a UNIQUE constraint allow only one NULL in SQL Server?

In SQL Server, technically, NULL = NULL is false. By that logic, no NULL is equal to any NULL and all NULLs are distinct. Shouldn't this imply that all NULLs are unique, and a unique index should allow any number of NULLs?
user87166
  • 543
  • 1
  • 4
  • 6
37
votes
2 answers

What is a WITH CHECK CHECK CONSTRAINT?

I have some auto-generated T-SQL, which is probably valid, but I don't really understand. ALTER TABLE [dbo].[MyTable] WITH CHECK CHECK CONSTRAINT [My_FORIEGN_KEY]; I know what a foreign key constraint is, but what's the CHECK CHECK?
BanksySan
  • 921
  • 1
  • 10
  • 16
36
votes
2 answers

How do I drop all constraints from all tables?

I want to drop all default constraints, check constraints, unique constraints, primary keys and foreign keys from all tables in a SQL Server database. I know how to get all the constraint names from sys.objects, but how do I populate the ALTER TABLE…
Aaron Bertrand
  • 177,916
  • 27
  • 387
  • 599
35
votes
1 answer

Optimal way to ignore duplicate inserts?

Background This problem relates to ignoring duplicate inserts using PostgreSQL 9.2 or greater. The reason I ask is because of this code: -- Ignores duplicates. INSERT INTO db_table (tbl_column_1, tbl_column_2) VALUES ( SELECT …
Dave Jarvis
  • 815
  • 1
  • 10
  • 24
35
votes
7 answers

Copy complete structure of a table

Using some methods, when you create a copy of a table you lose indexes, PK, FK, etc. For example in SQL Server I can say: select * into dbo.table2 from dbo.table1; This is just a simple copy of the table; all of the indexes / constraints are…
jcho360
  • 1,979
  • 7
  • 23
  • 31
35
votes
2 answers

What does 'ibfk' stand for in MySQL?

If I create a foreign key constraint for table 'photos' in phpmyadmin, I later see that the constraint is named 'photos_ibfk_1', and the next constraint is called 'photos_ibfk_2', etc. From this I have gathered that [tablename]_ibfk_constraintIndex…
Myer
  • 455
  • 1
  • 4
  • 8
33
votes
16 answers

How to implement a 'default' flag that can only be set on a single row

For example, with a table similar to this: create table foo(bar int identity, chk char(1) check (chk in('Y', 'N'))); It doesn't matter if the flag is implemented as a char(1), a bit or whatever. I just want to be able to enforce the constraint that…
Jack Douglas
  • 38,607
  • 15
  • 97
  • 174
32
votes
2 answers

Custom unique constraint, only enforced if one column has a specific value

Is it possible to have a custom unique constraint as follows? Suppose I have two cols, subset and type, both strings (though the data types probably doesn't matter). If type is 'true', then I want the combination of type and subset to be unique.…
Faheem Mitha
  • 989
  • 2
  • 11
  • 18
1
2 3
…
31 32