0

Say I have the following schema, would the default value https://sample.com be checked by the CHECK (Website LIKE "https://%")?

CREATE TABLE Company (
    AccountID varchar(25),
    Name varchar(20)        NOT NULL,
    Website varchar(255)    DEFAULT 'https://sample.com',
    Categories varchar(255) NOT NULL
                            DEFAULT '[]',
    PRIMARY KEY(AccountID),
    
    CHECK                   (CHAR_LENGTH(AccountID) BETWEEN 4 AND 25),
    CHECK                   (CHAR_LENGTH(Name) BETWEEN 2 AND 20),
    CHECK                   (Website LIKE "https://%")
);
3
  • 1
    No, default value in table definition is not checked. Only the value to be written (either assigned by default or provided explicitly) is checked. This check is performed after BEFORE triggers execution (if exists).
    – Akina
    Apr 21, 2021 at 8:13
  • See fiddle
    – Akina
    Apr 21, 2021 at 8:34
  • Se elephantdolphin.blogspot.com/2021/04/… for a quick example and how to test Apr 22, 2021 at 15:05

1 Answer 1

0

Checking default value doesn't make sense, as you have declared the static value which must be correct only. But the values which are going to be inserted manually are not checked using CHECK constrainst.

CREATE TABLE Company (
    AccountID varchar(25),
    Name varchar(20)        NOT NULL,
    Website varchar(255)    DEFAULT 'https://sample.com',
    Categories varchar(255) NOT NULL
                            DEFAULT '[]',
    PRIMARY KEY(AccountID),
    
    CHECK                   (CHAR_LENGTH(AccountID) BETWEEN 4 AND 25),
    CHECK                   (CHAR_LENGTH(Name) BETWEEN 2 AND 20),
    CHECK                   (Website LIKE "https://%")
);

See here : the value like hps://sdfs.com , asdfas://sdfs.com are not supposed to insert but they are inserted successfully.

insert into Company(ACCOUNTID , Name )  values ('12','asdf');
insert into Company(ACCOUNTID , Name , Website)  values ('13','asdf',"hps://sdfs.com");
insert into Company(ACCOUNTID , Name , Website)  values ('1','asdf4',"asdfas://sdfs.com");
select * from Company ; 

Output : 

# AccountID, Name, Website, Categories
1, asdf4, asdfas://sdfs.com, []
12, asdf, https://sample.com, []
13, asdf, hps://sdfs.com, []

Apart from using CHECK constraint I would suggest to use Triggers for validating this values or using JAVASCRIPT to valid this values.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.