112

In addition to the points in other answers, here are some key differences between the two. Note: The error messages are from SQL Server 2012. Errors Violation of a unique constraint returns error 2627. Msg 2627, Level 14, State 1, Line 1 Violation of UNIQUE KEY constraint 'P1U_pk'. Cannot insert duplicate key in object 'dbo.P1U'. The duplicate key value ...


69

Firstly, find out your FOREIGN KEY constraint name in this way: SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, -- <<-- the one you want! REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME = 'My_Table'; You can also add (to the WHERE clause) if you have more than one ...


56

Why does it work this way? Because way back when, someone made a design decision without knowing or caring about what the standard says (after all, we do have all kinds of weird behaviors with NULLs, and can coerce different behavior at will). That decision dictated that, in this case, NULL = NULL. It wasn't a very smart decision. What they should have done ...


45

Check the CREATE TABLE page of the manual: There are three match types: MATCH FULL, MATCH PARTIAL, and MATCH SIMPLE (which is the default). MATCH FULL will not allow one column of a multicolumn foreign key to be null unless all foreign key columns are null; if they are all null, the row is not required to have a match in the referenced table. ...


43

I see at least two ways of accomplishing this. The first approach is to not grant DELETE and UPDATE privileges on these write-once tables, or, for that matter, any privileges apart from INSERT and SELECT, thus only allowing users to insert into or select from them. Another option is to define BEFORE UPDATE and BEFORE DELETE triggers on these tables and use ...


40

PostgreSQL will not try to insert duplicate values on its own, it is you (your application, ORM included) who does. It can be either a sequence feeding the values to the PK set to the wrong position and the table already containing the value equal to its nextval() - or simply that your application does the wrong thing. The first one is easy to fix: ...


38

You can derive this information easily by joining sys.tables.object_id = sys.objects.parent_object_id for those object types. DECLARE @sql NVARCHAR(MAX); SET @sql = N''; SELECT @sql = @sql + N' ALTER TABLE ' + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N' DROP CONSTRAINT ' + QUOTENAME(c.name) + ';' FROM sys.objects AS c INNER JOIN sys.tables AS t ...


36

The MSDN documentattion page about ALTER TABLE explains these: ALTER TABLE: modify the table's structure (and some of the possible actions/modifications are): CHECK CONSTRAINT ..: enable the constraint NOCHECK CONSTRAINT ..: disable the constraint There are also additional, optional steps to do while creating/enabling/disabling a constraint: WITH CHECK: ...


34

In other words, you want subset to be unique if type = 'true'. A partial unique index will do that: CREATE UNIQUE INDEX tbl_some_name_idx ON tbl (subset) WHERE type = 'true'; This way you can even make combinations with NULL unique, which is not possible otherwise - as detailed in this related answer: PostgreSQL multi-column unique constraint and NULL ...


33

Install the additional module btree_gist as is mentioned in the manual at the location you linked to: You can use the btree_gist extension to define exclusion constraints on plain scalar data types, which can then be combined with range exclusions for maximum flexibility. For example, after btree_gist is installed, the following constraint will ...


31

Have you tried the --disable-triggers option to pg_restore? Per the documentation: Use this if you have referential integrity checks or other triggers on the tables that you do not want to invoke during data reload. Please note that this only is valid for a --data-only restore and requires the --superuser=username option to be passed, as well.


25

I don't think you have a problem with the relationships. I think instead the problem is that by using surrogate keys (ie Ids) for each table the resulting database is unable to prevent Workers from being inserted whose Department is of one Company while the Classification is of another and vice versa. A good way to understand this is to visualize the ...


23

Nothing is free. Sometime not having something isn't free either. Both having and not having declared foreign keys come with costs and benefits. The point of a foreign key (FK) is to ensure that this column over here can only ever have values that come from that column over there1. This way we can be sure we only ever capture orders for customers that ...


22

As the answers on the other question (of which this one is considered a duplicate) mention, there is (since version 9.5) a native UPSERT functionality. For older versions, keep reading :) I have set up a test for checking the options. I'll include the code below, which can be run in psql on a linux/Unix box (simply because for the sake of clarity in the ...


22

To sum up the comments: Like @ypercube hinted, you can do it in a single command, which is cheaper and safer: ALTER TABLE distributors DROP CONSTRAINT zipchk , ADD CONSTRAINT zipchk CHECK (length(zipcode) = 6); ALTER CONSTRAINT in Postgres 9.4 or later (like you found) can only change the "deferability" of a FK constraints. So not what you are looking ...


22

You can name the constraint inline: CREATE TABLE tblTest( -- -- Gender int CONSTRAINT DF_tblTest_Gender DEFAULT 3, -- ) ; As the CREATE TABLE msdn page shows: DEFAULT ... To maintain compatibility with earlier versions of SQL Server, a constraint name can be assigned to a DEFAULT. In the same page, we can find that the only options for <...


20

You don't need triggers or PL/pgSQL at all. You don't even need DEFERRABLE constraints. And you don't need to store any information redundantly. Include the ID of the active email in the users table, resulting in mutual references. One might think we need a DEFERRABLE constraint to solve the chicken-and-egg problem of inserting a user and his active email, ...


18

Foreign keys can be made conditional...sort of. You don't show the layout of each table, so here is a typical design showing your relationships: create table TransactionalStores( ID int not null auto_increment, StoreType char not null, ..., -- other data constraint CK_TransStoreType check( StoreType in( 'B', 'K', 'O' )), ...


18

It boils down to what UPDATE statement does. It's not entirely obvious but your statement is equivalent to this one: UPDATE upd SET Ticket = 'ARP.ExGE' , Method = 'smtp' , AcctOwner = 'r00417819' , DisplayName = '~AppLight HBSFax-Inactive' , Destination = 'r00417819@mail.ad.ge.com' ...


18

This is a rather simple CHECK constraint: CREATE TABLE my_table ( id serial, attribute boolean, number integer, CONSTRAINT if_attribute_then_number_is_not_null CHECK ( (NOT attribute) OR (number IS NOT NULL) ) ) ; The logic behind the code is that the logical restriction if a then b is written in boolean logic as (not a) or (b). May seem counter-...


18

Variant 1 Since all you need is a single column with standard = true, set standard to NULL in all other rows. Then a plain UNIQUE constraint works, since NULL values do not violate it: CREATE TABLE taxrate ( taxrate int PRIMARY KEY , standard bool DEFAULT true , CONSTRAINT standard_true_or_null CHECK (standard) -- yes, that's the whole constraint , ...


16

Change your constraint to CHECK (type IN ('email','post','IRL','minutes')) This will be converted by the parser into: CHECK (type = ANY( ARRAY['email','post','IRL','minutes'])) That should do what you are looking at. However I have to wonder if it wouldn't be better to do this: CREATE TABLE comlog_types ( type text ); And then add a foreign key ...


16

The regex in your question is not entirely unambiguous In most flavors that support Unicode, \d includes all digits from all scripts. Notable exceptions are Java, JavaScript, and PCRE. These Unicode flavors match only ASCII digits with \d. So in many flavours it would match ١١١.١١١.١١١١ (that character being Arabic-Indic Digit One) I'm assuming ...


16

Let's assume the structure of your table is this one: CREATE TABLE log_logins ( user_id INTEGER NOT NULL, login_time TIMESTAMP NOT NULL DEFAULT now(), ip_v4 TEXT /* or any other representation */, ip_v6 TEXT /* or any other representation */, PRIMARY KEY (user_id, login_time) ) ; You can just add a CHECK that guarantees that either ...


16

Can SQL Server create collisions in system generated constraint names? This depends on the type of constraint and version of SQL Server. CREATE TABLE T1 ( A INT PRIMARY KEY CHECK (A > 0), B INT DEFAULT -1 REFERENCES T1, C INT UNIQUE, CHECK (C > A) ) SELECT name, object_id, CAST(object_id AS binary(4)) as object_id_hex, CAST(...


16

My limited testing shows that the "IS NOT NULL" predicate can be eliminated if: the column is declared as NOT NULL in the table definition, or the column is protected from nulls by an active, trusted check constraint Here's a simple test table: CREATE TABLE dbo.Test ( Id int IDENTITY(1,1) NOT NULL, DeclareNotNull int NOT NULL, ...


15

CREATE TABLE t ( n INTEGER NOT NULL CHECK (n < 0) ); works in most RDBMS I know. Edit: A comment by @IMSop prompts me to specify why I wrote “most RDMS I know:” it is well known (and very unfortunate) that CHECK constraints aren’t honored by MySQL. In MySQL, you have to use triggers instead. Another option is to switch to MariaDB.


14

alter table ExampleTable add (new_column varchar(20) default 'value1', constraint ckExampleTable check (new_column in ('value1', 'value2', 'value3')));


14

You've forgotten about other types of constraint than primary key (also applies to unique, check, and foreign key constraints) but that's basically it. A column-level constraint can only reference the column that it is declared next to. A table-level constraint can reference multiple columns.


14

FULL vs SIMPLE vs PARTIAL While the chosen answer is correct, if this is new to you, you may want to see it with code -- I think it's easier to grok that way. -- one row with (1,1) CREATE TABLE foo ( a int, b int, PRIMARY KEY (a,b) ); INSERT INTO foo (a,b) VALUES (1,1); -- -- two child tables to reference it -- CREATE TABLE t_full ( a int, b int, ...


Only top voted, non community-wiki answers of a minimum length are eligible