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.

472 questions
254
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
  • 11,821
  • 23
  • 73
  • 108
144
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,551
  • 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
58
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
  • 891
  • 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
  • 835
  • 1
  • 7
  • 10
52
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
44
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,059
  • 2
  • 11
  • 18
37
votes
5 answers

Why does a UNIQUE constraint allow only one NULL?

Technically, NULL = NULL is False, by that logic no NULL is equal to any NULL and all NULLs are distinct. Shouldnt this imply that all NULLs are unique and a unique index should allow any number of NULLs?
user87166
  • 513
  • 1
  • 4
  • 6
36
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
  • 911
  • 1
  • 10
  • 16
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
  • 805
  • 1
  • 10
  • 23
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,978
  • 7
  • 23
  • 31
34
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,018
  • 26
  • 384
  • 593
34
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
  • 445
  • 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
  • 37,827
  • 15
  • 97
  • 174
31
votes
3 answers

How do I enforce a write-once then read only database table in SQL?

Is it even possible? My use case is a ledger table, with a requirement that once a record is created, it should be read-only, i.e. no-one should be able to edit or delete it. This only applies to the ledger table and tables with a direct relation to…
altanqa
  • 413
  • 4
  • 6
1
2 3
…
31 32