8

I have a table with a column which contains a 'valid until' Date and I want to make sure that this can only be set to null in a single row within the table. Is there an easy way to do this?

My table looks like this (postgres):

CREATE TABLE 123.myTable(
some_id integer NOT NULL,
valid_from timestamp without time zone NOT NULL DEFAULT now(),
valid_until timestamp without time zone,
someString character varying)

some_id and valid_from is my PK. I want nobody to enter a line with a null value in column valid_until if there is already a line with null for this PK.

Thank you

Chris Travers
  • 25,424
  • 6
  • 65
  • 182
mojoo-de
  • 501
  • 5
  • 20
  • What do you mean "this can only be set to null in a single row for one primary key"? A PK by definition only applies to a single row. A PK cannot contain `NULL` in the key column value(s). Also what RDBMS are you using? – Martin Smith May 11 '11 at 14:16
  • We really need a better explanation here in order to help you out. Can you give us the table layout and an example as well? – IAmTimCorey May 11 '11 at 14:22
  • "some_id and valid_from is my PK". How do you know? There's no primary key in the CREATE TABLE statement you posted. – Mike Sherrill 'Cat Recall' May 17 '11 at 08:10

7 Answers7

3

In PostgreSQL, you have two basic approaches.

  1. Use 'infinity' instead of null. Then your unique constraint works as expected. Or if you cannot do that:
  2. CREATE UNIQUE INDEX null_valid_from ON mytable(someid) where valid_until IS NULL

I have used both approaches. I find usually the first approach is cleaner and it allows you to use range types and exclude constraints in newer versions of PostgreSQL better (to ensure no two time ranges overlap based on a given given someid), bt the second approach often is useful where the first cannot be done.

Chris Travers
  • 25,424
  • 6
  • 65
  • 182
0

Depending on the database, you can't have null in a primary key (I don't know about all databases, but in sql server you can't). The easiest way around this I can think of is to set the date time to the minimum value, and then add a unique constraint on it, or set it to be the primary key.

I suppose another way would be to set up a trigger to check the other values in the table to see if another entry is null, and if there is one, don't allow the insert.

kemiller2002
  • 113,795
  • 27
  • 197
  • 251
0

As Kevin said in his answer, you can set up a database trigger to stop someone from inserting more than one row where the valid until date is NULL.

The SQL statement that checks for this condition is:

SELECT COUNT(*)
FROM TABLE
WHERE valid until IS NULL;

If the count is not equal to 1, then your table has a problem.

The process that adds a row to this table has to perform the following:

  • Find the row where the valid until value is NULL
  • Update the valid until value to the current date, or some other meaningful date
  • Insert the new row with the valid until value set to NULL
Gilbert Le Blanc
  • 50,182
  • 6
  • 67
  • 111
0

I'm assuming you are Storing Effective-dated-records and are also using a valid from date.

If so, You could use CRUD stored procedures to enforce this compliance. E.G the insert closes off any null valid dates before inserting a new record with a null valid date.

You probably need other stored procedure validation to avoid overlapping records and to allow deleting and editing records. It may be more efficient (in terms of where clauses / faster queries) to use a date far in the future rather than using null.

iandayman
  • 4,357
  • 31
  • 38
  • I'd be very careful with the SP approach. It's easy to write something that looks like it works but falls appart in the presence of concurrent transactions. – Jens Schauder May 11 '11 at 14:51
  • you can mitigate concurrency issues quite well - for most scenarios it's never an issue. I use this technique quite a lot for things like tax rates and other relatively static data. it does require a bit more info from the OP to see whether it's the right solution. – iandayman May 11 '11 at 15:07
0

I know only Oracle in sufficient detail, but the same might work in other databases:

  1. create another column which always contains a fixed value (say '0') include this column in your unique key.

  2. Don't use NULL but a specific very high or low value. I many cases this is actually easier to use then a NULL value

  3. Make a function based unique key on a function converting the date including the null value to some other value (e.g. a string representation for dates and 'x' for null)

  4. make a materialized view which gets updated on every change on your main table and put a constraint on that view.

    select count(*) cnt from table where valid_until is NULL

might work as the select statement. And a check constraint limiting the cnt value to the values 0 and 1

Jens Schauder
  • 77,657
  • 34
  • 181
  • 348
0

I would suggest inserting to that table through an SP and putting your constraint in there, as triggers are quite hidden and will likely be forgotten about. If that's not an option, the following trigger will work:

CREATE TABLE dbo.TESTTRIGGER
(
    YourDate Date NULL
)


    CREATE TRIGGER DupNullDates
ON dbo.TESTTRIGGER
FOR INSERT, UPDATE
AS 
DECLARE @nullCount int
SELECT @nullCount = (SELECT COUNT(*) FROM TESTTRIGGER WHERE YourDate IS NULL)
IF(@NullCount > 1)
BEGIN
    RAISERROR('Cannot have Multiple Nulls', 16, 1)
    ROLLBACK TRAN
END

GO
Mike M.
  • 12,343
  • 1
  • 24
  • 28
0

Well if you use MS SQL you can just add a unique Index on that column. That will allow only one NULL. I guess that if you use other RDBMS, this will still function.

Dumitrescu Bogdan
  • 7,127
  • 2
  • 23
  • 31
  • Note this behaviour is contrary to the SQL Standard for `UNIQUE` constraints therefore I'd be inclined to think it tend not to function on other SQL products (or DBMS with no 'R' -- please don't suggest SQL is relational :) – onedaywhen May 11 '11 at 15:03