Intervals: How can I make sure there is just one row with a null value in a timstamp column in table?

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

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.

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.

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

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.

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

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

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.