Intervals How can I make sure that there is only one row with a null value in the column table of a table?

Question

I have a table with a column that contains a "valid until" date, and I want to make sure that this can only be set to null in one row of 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 are my pc. I want no one to enter a row with a null value in the valid_until column if a row with a null value already exists for this PK.

thanks

Answer 1

In PostgreSQL, you have two main approaches.

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

I used both approaches. I usually think the first approach is cleaner, and it allows you to use range types and better eliminate restrictions in newer versions of PostgreSQL (so that there are no overlapping two time intervals based on a given given number), the second approach is often useful where at first it’s not may be done.

Answer 2

Depending on the database, you cannot have zero in the primary key (I do not know about all the databases, but you cannot in the sql server). The easiest way I can think of is to set the date time to a minimum value, and then add a unique constraint to it or set it as a primary key.

I believe that another way would be to set a trigger to check other values ​​in the table to see if the other record is null, and if there is one, do not allow insertion.

Answer 3

As Kevin said in his answer, you can configure the database trigger so that someone does not insert more than one row where the valid up to date is NULL .

SQL statement that validates this condition:

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

If the counter is not 1, your table has problems.

A process that adds a row to this table should do the following:

• Find the line where the value is valid until NULL
• Update validity to value before current date or any other significant date
• Insert a new line with a valid value up to NULL

Answer 4

I assume that you keep records with effective dates and also use valid ones from the date.

If so, you can use CRUD stored procedures to enforce this compliance. EG insert closes all zero valid dates before inserting a new record with a valid date of zero.

You probably need another check of the stored procedure to avoid duplicate entries and allow deletion and editing of entries. It may be more efficient (in terms of where clauses / faster queries are) to use the date far in the future rather than using zero.

Answer 5

I know only Oracle in sufficient detail, but the same could be used in other databases:

• To create another column that always contains a fixed value (for example, "0"), include this column in your unique key.

• Do not use NULL, but a very high or low value. In most cases, this is actually easier to use than a NULL value.

• Create a functional unique key for the function that converts the date, including a null value, to some other value (for example, a string representation for dates and an "x" for zero)

• make a materialized view that updates every time you change your main table and puts a restriction on that view.

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

can work as a select statement. And a control constraint limiting the cnt value to 0 and 1

Answer 6

I would suggest inserting into this table through SP and putting my restriction there, since the triggers are quite hidden and will probably be forgotten. If this is not an option, the following trigger fires:

 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 

Answer 7

Well, if you use MS SQL, you can simply add a unique index to this column. This will allow you to use only one NULL. I assume that if you use other RDBMSs, this will work anyway.