There are no such devices in C#. A few options are available, though: in C# 4.0 and .NET 4.0 (or above), use dynamic, which supports + but offers no compile time checking in .NET 3.5 (or above), MiscUtil offers an Operator class which makes operators available as methods – again, without any compile-time checking So … Read more
If your database allows expressions in an index you can do something like this (ANSI SQL): CREATE UNIQUE INDEX on your_table (least(column1, column2) , greatest(column1, column2)); Note this is a unique index not a unique constraint. The only difference for most DBMS is that you can’t have a unique index as the target of a … Read more
UPDATE: This question was the basis of my blog article on the 3rd of February 2011. Thanks for the great question! This is legal, it is not circular, and it is fairly common. I personally dislike it. The reasons I dislike it are: It is excessively clever; as you’ve discovered, clever code is hard for … Read more
I’m not familiar with SQLite but a little Google’ing turned up this. The documentation says If the database schema contains foreign key errors that require looking at more than one table definition to identify, then those errors are not detected when the tables are created. Instead, such errors prevent the application from preparing SQL statements … Read more
You can keep your separate timestamp columns and still use an exclusion constraint on an expression: CREATE TABLE tbl ( tbl_id serial PRIMARY KEY , starts_at timestamp , ends_at timestamp , EXCLUDE USING gist (tsrange(starts_at, ends_at) WITH &&) — no overlap ); Constructing a tsrange value without explicit bounds as tsrange(starts_at, ends_at) assumes default bounds: … Read more
Quassnoi is right; a trigger would be the best way to achieve this. Here’s the code: CREATE OR REPLACE FUNCTION enforce_photo_count() RETURNS trigger AS $$ DECLARE max_photo_count INTEGER := 10; photo_count INTEGER := 0; must_check BOOLEAN := false; BEGIN IF TG_OP = ‘INSERT’ THEN must_check := true; END IF; IF TG_OP = ‘UPDATE’ THEN IF … Read more
You can also use SAVEPOINTs in a transaction. Pythonish pseudocode is illustrate from the application side: database.execute(“BEGIN”) foreach data_row in input_data_dictionary: database.execute(“SAVEPOINT bulk_savepoint”) try: database.execute(“INSERT”, table, data_row) except: database.execute(“ROLLBACK TO SAVEPOINT bulk_savepoint”) log_error(data_row) error_count = error_count + 1 else: database.execute(“RELEASE SAVEPOINT bulk_savepoint”) if error_count > error_threshold: database.execute(“ROLLBACK”) else: database.execute(“COMMIT”) Edit: Here’s an actual example of … Read more
OT: There are IMHO quite a few things SQL Server does not support, but would make sense in an enterprise environment: Deferrable constraints as mentioned here MARS: Just why do you need to set an option for something entirely natural? CASCADE DELETE constraints: SQL Server does only allow one single cascadation path for a given … Read more
SQLite does not (as of this answer) support the alter table drop constraint command. The allowed syntax can be seen here. You will need to create a new table without a constraint, transfer the data, then delete the old table. I think something like the following should work: CREATE TABLE child2 ( id INTEGER PRIMARY … Read more
Yes. UNIQUE constraints are implemented using a unique B-tree index in Postgres. See: How does PostgreSQL enforce the UNIQUE constraint / what type of index does it use?