Geek Answers Handbook

SQL - How to limit data entry to one attribute depending on another attribute?

Below is the DDL for the table I want to create. However, I want the attribute "Expertise_breed" to be obtained from "Expertise_animal". For example, if "Dog" is entered in "Expertise_animal", I do not want to enter the cat breed. How can i do this? I am working with SQL Server Management Studio 2012

CREATE TABLE tExpertise
(
Expertise_ID        int         NOT NULL  PRIMARY KEY, --E.G Data '001'
Expertise_type      varchar(8)  NOT NULL,              --E.G Data 'Domestic'
Expertise_animal    varchar(30) NOT NULL,              --E.G Data 'Dog'
Expertise_breed     varchar(30) NOT NULL               --E.G Data 'Poodle'
)
+4
source share
3 answers

This is a data relationship situation, you must use relational tables.

AnimalClassification - (, , ) AnimalSpecies (, , ) AnimalBreed (, ) , .. - , .. Beagle -

+2

/ . "" .

, (, dog x poodle), - , .

-1

, , , , ( ):

CREATE FUNCTION dbo.validateExpertise(
    @expertise_type varchar(8),
    @Expertise_animal varchar(30),
    @Expertise_breed varchar(30)   
)
RETURNS BIT
AS
BEGIN
    IF (@Expertise_animal == 'dog' AND @Expertise_breed != 'dog') 
        RETURN 0;

    -- other validations can come here

    RETURN 1;
END
GO

-- add a table level constraint
-- WITH NOCHECK can be used to not check existing data
ALTER TABLE detailTable ADD CONSTRAINT chkExpertise
        CHECK (dbo.validateExpertise(expertise_type, Expertise_animal, Expertise_breed) = 1)

, ​​ . ( ) - , ( ASP.NET MVC, WCF, - ..) ( , .

. , , FK s, unique , .., .

Also, keep in mind that restrictions like the ones mentioned above will be triggered for each INSERTor UPDATEin the table and can seriously degrade the performance of queries involving a large number of records.

-1
source

More articles:


All Articles