Reggi Reggi - 1 year ago 40
SQL Question

ADD CONSTRAINT "CHECK ([TYPE] IN (...) Gan I get the constraint values from other table?

I'm having a problem building some constraints in my MSSQL database. This database will be used for a small electronics store. For the moment I have this to create my database:

CREATE TABLE [dbo].[PRODUCT] (
[ID] INT NOT NULL PRIMARY KEY IDENTITY(1,1),
[ID_SUBCAT] INT NOT NULL,
[KORTING] FLOAT NULL,
[PRIJS] FLOAT NULL,
[TYPE] VARCHAR(5) NOT NULL )


And now I want to add a constraint that the [TYPE] column can only hold certain values. So I came up with this constraint

GO ALTER TABLE [dbo].[PRODUCT] ADD CONSTRAINT [ProperTypeEntered] CHECK ([TYPE] IN ('Camera', 'Lens'))


But when I have a new type that I want to add I always have to edit this constraint because the values are hardcoded in the constraint. So I was hoping that I could make a second table with all the types in like so:

CREATE TABLE [dbo].[TYPES] (
[ID] INT NOT NULL PRIMARY KEY IDENTITY(1,1),
[NAAM] VARCHAR(5) NOT NULL )


And just have a constraint that will allow only that are inserted in the TYPES.NAAM column.

Is that in anyway possible or am I just overlooking something? My knowledge of SQL is limited so any help would be great! :)

Answer Source

Your not able to make a constraint that does a query. But you could do it based on a trigger for insert/update, that would query your second table, if the "type" isn't found you could throw an expection which would cause the insert/update to fail.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download