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

learn more… | top users | synonyms

4
votes
2answers
535 views

Foreign Keys doesn't work as I expect

Here are my tables; CREATE TABLE `borclular` ( `id` int(11) NOT NULL AUTO_INCREMENT, `tc` char(11) COLLATE utf8_turkish_ci NOT NULL, `borclu` varchar(255) COLLATE utf8_turkish_ci NOT NULL, ...
2
votes
4answers
125 views

ERROR: check constraint is violated by some row

I've got the following error: ERROR: check constraint "cc_at_least_one_mapping_needed" is violated by some row Query: ALTER TABLE integrations.tax_aggregates DROP COLUMN IF EXISTS myob_id, ADD ...
5
votes
2answers
342 views

CREATE as SELECT with CONSTRAINT?

I have a piece of SQL that creates a TABLE form a SELECT statement in PostgreSQL ; I want to add constraints to the table so that for example a column cannot be null. I cannot find valid SQL for this ...
1
vote
0answers
29 views

How do I disable constraint checking during schema migration?

How do I disable FOREIGN KEY checking for the time of database schema migration? Currently I found engine-based commands like: PostgreSQL: SET CONSTRAINTS ALL DEFERRED; MSSQL: EXEC ...
4
votes
1answer
26 views

Enforcing that a user should have one or more email addresses

I'm trying to design an application where a user can authenticate oneself with one password and one or more email addresses. I'm not sure how to enforce this constraint. The tables so far are simple: ...
3
votes
1answer
15 views

How can I exclude range overlaps if one range may be empty?

Suppose I want to ensure that, for any expected height in feet, I have a single, unambiguous classification. I create this table: CREATE TABLE heights_in_feet( size TEXT NOT NULL, min INTEGER NOT ...
0
votes
1answer
22 views

Postgres constraint function on a single column

Is it possible to create a constraint in Postgres that will run a function, but only if a specific column is changed? Right now, I have this: ALTER TABLE public.employee ADD CONSTRAINT ...
10
votes
5answers
664 views

Why does this UPDATE fail with a unique key constraint violation?

I'm an "accidental" DBA, relatively inexperienced and baffled by this problem. Running MS SQL Server 2012. The problem is with this UPDATE statement: UPDATE dbo.tAccts SET Ticket ...
0
votes
1answer
36 views

How to add a check constraint in Postgres without locking the table?

I would like to add a check constraint to a very large table. Something like: ALTER TABLE accounts ADD CONSTRAINT "nonnegative_balance" CHECK balance >= 0; Unfortunately Postgres (9.3, at least) ...
0
votes
1answer
24 views

Modify table constraint in PostgreSQL

Say I have a constraint ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5); that needs to be modified so that zipcode length is 6. Do I have the drop the constraint ...
0
votes
1answer
35 views

Adding multiple constraints

I am trying to add constraints and I think i have an error in the code as its saying i cant add mutlple constrints. What is best way to add mutlple constraints I am using MSQL Server 2008 r2 CREATE ...
2
votes
0answers
41 views

Any drawbacks to referencing multiple tables from a single column?

I'm working on a system with the following entities: Projects Sections Activities There is one important constraint that needs to be enforced: Activities are always part of one project. ...
2
votes
0answers
22 views

sqlite3 foreign keys ignored

I am working on a small sqlite3 database for a small simple mailserver. I have created a new sqlite3 database and add executed this to pürepare it for data: BEGIN TRANSACTION; CREATE TABLE ...
0
votes
1answer
46 views

is_not_trusted = 1 after Error-Free WITH CHECK CHECK CONSTRAINT

I'd like SQL Server 2005 SP4 to trust a certain foreign key constraint. Executing an ALTER TABLE statement that enables + checks the constraint returns Command(s) completed successfully yet ...
1
vote
1answer
31 views

What is the best approach to find all fields that has (Foreign Key and Primary Key) constraints in a Mysql database?

I have a mysql database that has 250 tables. I would like to run a query to retrieve: tableName ; FieldName ; ConstaintName (Primary or foreign key). I could use ...
0
votes
0answers
13 views

Unique attributes as a group [duplicate]

create table test (a int, b int); I want to make a and b be unique, but as a whole. Example: a b - - 1 1 1 2 - this is ok, because even if the previous entry was 1, the b value is different 2 1 - ...
1
vote
1answer
33 views

Duplicate check constraint on multiple tables?

I have an integer value for a data to be included into two or three tables, Say it is part no. The integer value of part no must be between 1 to 8. I want to add that constraint into the tables. ...
0
votes
0answers
14 views

How to use Domains in oracle database

I am trying to learn about domains. As far as i understand, domains are user defined "types" that describe restrictions of its value. But how would i assign and use a domain in a given table in an ...
0
votes
1answer
23 views

Assign value to a field after checking a condition

I have this query: ALTER TABLE MERCHANTS ADD COLUMN merchant_image bytea, ADD COLUMN merchant_logo bytea, ADD COLUMN merchant_address VARCHAR(100), ADD COLUMN merchant_phone VARCHAR(50), ...
1
vote
1answer
77 views

Should child comment be treated as separate table?

I'm building a commenting system, currently my table indicates which comment is child with parent id in 1 table, I would like to know what is the best way of doing this? Current Table Scheme (1 ...
8
votes
2answers
99 views

SQL Server equivalent of Oracle USING INDEX clause

Is there a SQL Server 2008 equivalent of the USING INDEX clause in Oracle? Specifically for the construct: CREATE TABLE c(c1 INT, c2 INT); CREATE INDEX ci ON c (c1, c2); ALTER TABLE c ADD CONSTRAINT ...
1
vote
1answer
45 views

Constrain/limit certain records in sql table definitions

Let's say I have posts on which users can leave a comment. And I want to limit the amount of comments to a certain post to 10, is it possible to add such constraint/restriction to the table? My guess ...
1
vote
1answer
32 views

Indirect constraints (foreign keys amongst three/four tables) [duplicate]

I have the next layout of tables: table COMPANY: - primary key pk_company table USERS: - primary key pk_users, - foreign key (fk_company) references COMPANY (pk_company) table GROUPS: <br> - ...
1
vote
1answer
59 views

Enforce a single NULL end date per foreign key value using an index

I want to implement a Check Constraint across a date range and foreign key. I have a table created as: Primary Key Foreign Key DateStart DateEnd The ForeignKey can appear many times in the table ...
5
votes
2answers
308 views

Conditional Foreign Key Relationship

I currently have a foreign key between two entities, and I would like to make that relation conditional to the entityType of one of the tables. Here's the hierachy of tables, this is done via FK ...
1
vote
1answer
36 views

Using REGEX_LIKE to Implement a Check Constraint

Background I am working on the database design for an application that tracks information about database development projects at my company. The project management office (PMO) assigns a ...
11
votes
3answers
224 views

Constraint to enforce “at least one” or “exactly one” in a database

Say we have users and each user can have multiple email addresses CREATE TABLE emails ( user_id integer, email_address text, is_active boolean ) Some sample rows user_id | ...
4
votes
1answer
294 views

How to enforce a constraint over multiple tables?

I have the following five tables: PRODUCT FEATURE PROVIDER PROVIDER_PRODUCT PROVIDER_FEATURE The logic is as follows: A generic PRODUCT can have one or more FEATUREs. There are different ...
3
votes
1answer
131 views

Enabling Constraint Silently Fails

Why would SQL Server silently fail to re-enable a foreign key constraint? I recently attempted to re-enable a number of constraints using ALTER TABLE. Most enabled successfully; however, minority of ...
2
votes
1answer
69 views

Can Indexes mitigate untrusted Constraint

I using sp_blitz and found two tables that have foreign keys that is untrusted. tried to enable the trust with: ALTER TABLE MyTableName WITH CHECK CHECK CONSTRAINT MyConstraintName; GO But got this ...
0
votes
1answer
52 views

Is unique index combined with a “not null” constraint equivalent to a PK constraint?

I have these legacy table definitions (from here): CREATE TABLE [dbo].[JobItems] ( [ItemId] UNIQUEIDENTIFIER NOT NULL, -- lots of other columns CONSTRAINT [PrimaryKey_GUID_HERE] ...
0
votes
1answer
94 views

Create an “int” column forced between 0 and 3

I would like to create a table with one column that would be of type "integer", and locked between 0 and 3. Is this possible? My goal would be to have this column so that I could just execute a ...
3
votes
3answers
132 views

Cap on number of rows matching a condition in Postgres

Say I have a db schema like this user_id text photo_id text date_created timestamp and I want to enforce a constraint that says "a user can only have 30 photos". A naive way to implement this would ...
2
votes
1answer
133 views

Foreign Key Constraint over two tables

I am trying to put the following entity-model into practice: We have a list of Payments, each Payment belongs to a certain store and is of a certain payment type (e.g. 10€ maintenance cost in ...
0
votes
0answers
64 views

MariaDB error on alter table - table removed - constraint name is blocked

Today, we experienced a strange behavior in 5.5.41-MariaDB (InnoDB): An ALTER TABLE (add constraint) statement failed with error (mysql server has gone away after that). Then the database table ...
0
votes
1answer
24 views

Verify conditions before creating a constraint

I've taken over an application database that has been grown from an Access db. As such, the database is in need of some significant refactoring. The first step that I'm planning is adding a number of ...
0
votes
2answers
35 views

Basic SQL question about setting constraints

In code such as the following, what is the need to write constraint pk_dept primary key (deptno) if just simply primary key (deptno) works? Is pk_dept something the database uses internally, since it ...
2
votes
1answer
40 views

Detect circular relations in a distribution table

I have the following two tables, that will define distribution rates from items on table A to other items on table A (I think I can say B defines a many-to-many relationship for A with itself). ...
0
votes
1answer
63 views

SQL Server transactional replication issue with database

We have on the server a database and we want to replicate a database on another server. we've set a transactional replication, but we have issues with the default constraints on the tables: On the ...
2
votes
0answers
23 views

SQLITE Foreign key has to be re-added? [closed]

Currently I have a mysql database on my computer which I convert to sqlite database. On the sqlite database, I have transaction_detail table with a foreign key to transaction_master table. The ...
0
votes
0answers
126 views

What order do foreign key constraints run in? Why does my foreign key not work after import?

I have two tables tags and tag_memberships in a PostgreSQL database. On the tag_memberships table, I have two constraints. The first is this: ALTER TABLE tag_memberships ADD CONSTRAINT ...
2
votes
1answer
55 views

Can column uniqueness be commutative?

I have a table in a Postgres DB where col1 and col2 are foreign keys referring to the same column. I want only unique combinations of (col1, col2), i.e. if (1,2) is entered then (2,1) should be ...
0
votes
1answer
79 views

How can we drop a constraint without losing the automatically created index?

How can we drop a constraint without losing the automatically created index? If it's possible using a single query.
3
votes
1answer
117 views

Multi-tenant database constraints

I have a multi-tenant app in PostgreSQL that has a repeated tenant_id column in each table to help isolate the tenants from each other. My question is, is there any sort of way that I can use ...
4
votes
1answer
1k views

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

Deferrable Foreign Key Constraints

What are the consequences of making a foreign key constraint DEFERRABLE INITIALLY IMMEDIATE instead of NONDEFERRABLE? This answer on StackOverflow mentions the performance impact that comes with no ...
2
votes
1answer
152 views

Multi-column uniqueness constraint, checking a field has one of two values

I'd like to enforce that only one row in a database matches value A in column 1, and either value B or value C in column 2. A small example create table builders ( id text serial ); create table ...
1
vote
0answers
39 views

Compound key that only allows nulls if they are null in the referenced table

I want to create a compound foreign key constraint that only allows a null value in one of the columns if there is a matching row with a null value in the referenced table. Additionally, the ...
0
votes
0answers
78 views

Cleaning up orphaned metadata in information_schema

OK - so the server (Percona 5.6 in this case) 'went away' in the middle of declaring a constraint like so: alter table mytable add constraint fk_mytable_myothertable foreign key (myothertable_id) ...
0
votes
2answers
114 views

In mysql, find all instances of a named column that are not foreign keys

This one has had me trumped for a while! I want to run a query to retrieve all instances of a column (from information_schema, I guess) that ARE or ARE NOT foreign keys. Now, let me back of a little ...