18. oktober 2001 - 20:12Der er
12 kommentarer og 1 løsning
CHECK constraints...
Ved eksekvering af foelgende faar jeg en MASSE constraint fejl. Er der nogen der kan hjaelpe mig? Jeg skal have samme funktionalitet som beskrevet i CHECK constraintsne, men de virker ikke. Jeg har ellers kigget i SQL online books, og de siger at det jeg har skrevet er tilladt. Derfor ved jeg ikke lige hvorfor den fejler.
create table AcervoDocumental( IDacervodocumental int identity(1,1) primary key not null, IDfondo tinyint not null FOREIGN KEY REFERENCES fondos(IDfondo), seccion nvarchar(85) not null, serie nvarchar(85) not null, subserie nvarchar(85) not null, lugar nvarchar(85) not null, anosalida smallint not null CONSTRAINT anosalida_chk CHECK(anosalida >= 1850), anoterminada smallint CONSTRAINT anoterminada_chk CHECK(anoterminada <= 2030 AND anoterminada>anosalida OR anoterminada IS NULL), caja nvarchar(5) not null CONSTRAINT caja_chk CHECK(caja like \"[a-z][0-9][0-9][0-9][0-9]\"), expediente smallint not null CONSTRAINT expediente_chk CHECK(expediente <= 999 AND expediente >= 100), legajo tinyint not null CONSTRAINT legajo_chk CHECK(legajo <= 9 AND expediente >= 1), foliosexpediente smallint not null CONSTRAINT foliosexpediente_chk CHECK(foliosexpediente <= 9999 AND expediente >= 1000), foliosdigitalizados smallint not null CONSTRAINT foliosdigitalizados_chk CHECK(foliosdigitalizados <= 999 AND expediente >= 100), observaciones nvarchar(255) not null, );
Server: Msg 8141, Level 16, State 1, Line 1 Column CHECK constraint for column \'anoterminada\' references another column, table \'AcervoDocumental\'. Server: Msg 8141, Level 16, State 1, Line 1 Column CHECK constraint for column \'caja\' references another column, table \'AcervoDocumental\'. Server: Msg 8141, Level 16, State 1, Line 1 Column CHECK constraint for column \'legajo\' references another column, table \'AcervoDocumental\'. Server: Msg 8141, Level 16, State 1, Line 1 Column CHECK constraint for column \'foliosexpediente\' references another column, table \'AcervoDocumental\'. Server: Msg 8141, Level 16, State 1, Line 1 Column CHECK constraint for column \'foliosdigitalizados\' references another column, table \'AcervoDocumental\'. Server: Msg 1759, Level 16, State 1, Line 1 Invalid column \'[a-z][0-9][0-9][0-9][0-9]\' is specified in a constraint or computed-column definition. Server: Msg 1750, Level 16, State 1, Line 1 Could not create constraint. See previous errors.
Ok, dit problem er, at når du angiver en CHECK constraint ud for en column, som du gør (det kaldes column-level), så må den IKKE referere en anden column. Istedet skal den oprettes for sig selv (såkaldt table-level).
Som der står i Books Online:
A column-level CHECK constraint can reference only the constrained column, and a table-level CHECK constraint can reference only columns in the same table.
create table AcervoDocumental( IDacervodocumental int identity(1,1) primary key not null, IDfondo tinyint not null FOREIGN KEY REFERENCES fondos(IDfondo), seccion nvarchar(85) not null, serie nvarchar(85) not null, subserie nvarchar(85) not null, lugar nvarchar(85) not null, anosalida smallint not null CONSTRAINT anosalida_chk CHECK(anosalida >= 1850), anoterminada smallint CONSTRAINT anoterminada_chk CHECK(anoterminada <= 2030 AND anoterminada>anosalida OR anoterminada IS NULL), caja nvarchar(5) not null CONSTRAINT caja_chk CHECK(caja like \"[a-z][0-9][0-9][0-9][0-9]\"), expediente smallint not null CONSTRAINT expediente_chk CHECK(expediente <= 999), legajo tinyint not null CONSTRAINT legajo_chk CHECK(legajo <= 9), foliosexpediente smallint not null CONSTRAINT foliosexpediente_chk CHECK(foliosexpediente <= 9999), foliosdigitalizados smallint not null CONSTRAINT foliosdigitalizados_chk CHECK(foliosdigitalizados <= 999), observaciones nvarchar(255) not null, );
FEJL:
Server: Msg 8141, Level 16, State 1, Line 1 Column CHECK constraint for column \'anoterminada\' references another column, table \'AcervoDocumental\'. Server: Msg 8141, Level 16, State 1, Line 1 Column CHECK constraint for column \'caja\' references another column, table \'AcervoDocumental\'. Server: Msg 1759, Level 16, State 1, Line 1 Invalid column \'[a-z][0-9][0-9][0-9][0-9]\' is specified in a constraint or computed-column definition. Server: Msg 1750, Level 16, State 1, Line 1 Could not create constraint. See previous errors.
Server: Msg 1759, Level 16, State 1, Line 1 Invalid column \'[a-z][0-9][0-9][0-9][0-9]\' is specified in a constraint or computed-column definition. Server: Msg 1750, Level 16, State 1, Line 1 Could not create constraint. See previous errors.
det skal lige siges at det i online books er skrevet med anfoerselstegn :(
Synes godt om
Ny brugerNybegynder
Din løsning...
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.
