Setting Constraint between two fields in SQL

Question

I have a table, with two columns, Enter_Time and Leave_Time, how to write a CREATE TABLE script that enforces the constraint Leave_Time > Enter_Time?

Edit: Microsoft Access is used

Answer 1

CREATE TABLE foo (
    Leave_Time DATETIME NOT NULL, 
    Enter_Time DATETIME NOT NULL, 
    CONSTRAINT CHK_TIMES CHECK (Leave_Time > Enter_Time));

Answer 2

That depends on your SQL database engine. Assuming Microsoft SQL Server:

CREATE TABLE [dbo].[Timecard](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [EnterTime] [datetime] NULL,
    [LeaveTime] [datetime] NULL,
 CONSTRAINT [PK_Timecard] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[Timecard]  WITH CHECK ADD  CONSTRAINT [CK_Timecard] CHECK  (([EnterTime]<[LeaveTime]))
GO
ALTER TABLE [dbo].[Timecard] CHECK CONSTRAINT [CK_Timecard]

But your question was how to write it... I only just wrote it for you. The how I did it is more generally useful: I used Microsoft SQL Server Management Studio Express (free program) and created a table and constraint using the GUI. Then I asked it for the CREATE script. That way I didn't have to be a SQL wizard to write up the above code.

Answer 3

Using PostgreSQL:

CREATE TABLE foo (
  -- other columns...

  enter_time timestamptz NOT NULL,
  leave_time timestamptz NOT NULL,
  CONSTRAINT ck_interval CHECK (leave_time > enter_time)
);