Sql-server – Check constraint references another column in same table

check-constraintsconstraintddlsql server

I have this table but check constraint is throwing me an error:

Column CHECK constraint for column 'MedicamentRegulated' references another column, table 'Medicaments'.

I've seen this as allowed in SQL Server but I don't know why it doesn't work :/

CREATE TABLE Medicaments (
MedicamentID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
Name VARCHAR(50),
DateValidity DATE,
MedicamentRegulated BIT CHECK((MedicamentRegulated = 1 AND DateValidity IS NOT NULL) 
  OR MedicamentRegulated = 0)
)

Best Answer

The syntax you are using is for a column-level constraint, so it can only apply to the column it is attached to... try naming your constraints at the table level instead (this also ~~allows~~ encourages you to give your constraints meaningful names instead of the terrible system defaults):

CREATE TABLE dbo.Medicaments -- always use schema*
(
  MedicamentID INT IDENTITY(1,1) NOT NULL,
  Name VARCHAR(50),
  DateValidity DATE,
  MedicamentRegulated BIT,
  CONSTRAINT pk_Med PRIMARY KEY (MedicamentID),
  CONSTRAINT ck_Med_reg CHECK
  (
    (MedicamentRegulated = 1 AND DateValidity IS NOT NULL) 
    OR MedicamentRegulated = 0
  )
);

*see this post.

Related Solutions

H2 SELECT NULLABLE or other equivalent to check a column has NOT NULL constraint or not

If you have a definition like this:

create table foo 
(
   id integer not null
)

The following query will show "NO" in the is_nullable column:

select table_name, 
       column_name,
       is_nullable
from information_schema.columns

If the column has a check constraint, like this:

create table foo
(
  id integer,
  constraint id_not_null check (id is not null)
);

then this will be shown as "nullable" with the first query. You would need to check information_schema.constraints and analyze the content of the CHECK_EXPRESSION column to detect this.

Postgresql – check key if exists in other table without fk constraint

I do agree with Aaron, that not creating a PK (or unique constraint) is a very strange requirement

But you could do something like this:

insert into b (id_b, id_a)
select 1, 2 
where exists (select 1 from a where id_a = 2);

or alternatively:

insert into b (id_b, id_a)
select 1, id_a 
from a where id_a = 2

If you want to insert multiple rows, you can use a values clause:

insert into b (id_b, id_a)
select * 
from (
   values 
      (1,2), (2,3), (3,4)
) as t(id_b, id_a) 
where exists (select 1  
              from a 
              where a.id_a = t.id_a);

This will only make sure that this specific insert will insert correct data. It will not prevent others that don't follow this pattern to insert invalid data. And it is not safe in a multi-user environment where different transactions run the inserts concurrently.

Best Answer

Related Solutions

H2 SELECT NULLABLE or other equivalent to check a column has NOT NULL constraint or not

Postgresql – check key if exists in other table without fk constraint

Related Question