Ygthkb

“I had a flat in the centre of town, but I didn’t like living there, so …”

School performs periodic password audits. Is my password compromised?

Wardrobe above a wall with fuse boxes

Has Wakanda ever accepted refugees?

Difference between 'stomach' and 'uterus'

How to fix my table, centering of columns

When to use mean vs median

PTIJ: Should I stay away from my computer?

Why is my Contribution Detail Report (native CiviCRM Core report) not accurate?

How can I handle a player who pre-plans arguments about my rulings on RAW?

Deal the cards to the players

Correct physics behind the colors on CD (compact disc)?

Draw bounding region by list of points

1970s scifi/horror novel where protagonist is used by a crablike creature to feed its larvae, goes mad, and is defeated by retraumatising him

Is every open circuit a capacitor?

Reason why dimensional travelling would be restricted

Is there a way to find out the age of climbing ropes?

PTIJ: Aharon, King of Egypt

Convergence to a fixed point

What is better: yes / no radio, or simple checkbox?

What is a term for a function that when called repeatedly, has the same effect as calling once?

How do we objectively assess if a dialogue sounds unnatural or cringy?

Should we avoid writing fiction about historical events without extensive research?

Giving a talk in my old university, how prominently should I tell students my salary?

User defined table type with a check constraint

Check constraint only one of three columns is non-nullsp_executesql with user defined table type not behaving correctlyH2 SELECT NULLABLE or other equivalent to check a column has NOT NULL constraint or notOperand type clash: my first User Defined Type with list of Integers, how to test via SSMScheck key if exists in other table without fk constraintUsing REGEX_LIKE to Implement a Check ConstraintDon't know why MySQL is not creating table with primary key constraintCHECK constraint to limit certain rowsDoes any kind of constraint in a database have a unique constraint name?Partially-Unique Check Constraints

3

I have a user defined table type where I want to make sure that a certain column is within a given range. This range however is defined in another table. So I thought I can use a check constraint on this column with a scalar valued function.

    CREATE TYPE [dbo].[Accounts] AS TABLE(

    [AccountName] [varchar](25) NOT NULL 

         CONSTRAINT CHK_AccountName CHECK (dbo.validateAccountName(AccountName) = 1),

    [Sales] [int] NOT NULL,

    [Returns] [int] NOT NULL

);

However I get a syntax error, when I do the same on a regular table it works. Based on MSFT documentation check constraints should be ok on table types. What am I missing here ? Any better approach to validate input on a table type ?

sql-server-2012 constraint usertypes

share|improve this question

asked Feb 6 '14 at 9:38

nojetlagnojetlag

1,30772333

bumped to the homepage by Community♦ 17 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  create store procedure for insert and perform the check before the insert statement, and if check is ok do the insert, if its not return message for invalid AccountName or something similar.
  
  – dimitar
  Feb 6 '14 at 10:10
  

  
  
  
  


• 
  
  
  

  
  3
  

  
  
  
  
  
  

  
  
  The CONSTRAINT CHK_AccountName is wrong. Table types can't have named constraints. Even removing it gives Invalid column name 'dbo'. though. I presume this isn't allowed.
  
  – Martin Smith
  Feb 6 '14 at 10:21
  

  
  
  
  


• 
  
  
  

  
  3
  

  
  
  
  
  
  

  
  
  @MartinSmith, I get at SQL-Fiddle: Schema Creation Failed: User-defined functions, user-defined aggregates, CLR types, and methods on CLR types are not allowed in expressions in this context.:
  
  – ypercubeᵀᴹ
  Feb 6 '14 at 10:24
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @ypercube - Ah I hadn't bothered creating a dummy scalar UDF. Now I have I see the same thing.
  
  – Martin Smith
  Feb 6 '14 at 10:26
  

  
  
  
  

add a comment | 

3

I have a user defined table type where I want to make sure that a certain column is within a given range. This range however is defined in another table. So I thought I can use a check constraint on this column with a scalar valued function.

    CREATE TYPE [dbo].[Accounts] AS TABLE(

    [AccountName] [varchar](25) NOT NULL 

         CONSTRAINT CHK_AccountName CHECK (dbo.validateAccountName(AccountName) = 1),

    [Sales] [int] NOT NULL,

    [Returns] [int] NOT NULL

);

However I get a syntax error, when I do the same on a regular table it works. Based on MSFT documentation check constraints should be ok on table types. What am I missing here ? Any better approach to validate input on a table type ?

sql-server-2012 constraint usertypes

share|improve this question

asked Feb 6 '14 at 9:38

nojetlagnojetlag

1,30772333

bumped to the homepage by Community♦ 17 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  create store procedure for insert and perform the check before the insert statement, and if check is ok do the insert, if its not return message for invalid AccountName or something similar.
  
  – dimitar
  Feb 6 '14 at 10:10
  

  
  
  
  


• 
  
  
  

  
  3
  

  
  
  
  
  
  

  
  
  The CONSTRAINT CHK_AccountName is wrong. Table types can't have named constraints. Even removing it gives Invalid column name 'dbo'. though. I presume this isn't allowed.
  
  – Martin Smith
  Feb 6 '14 at 10:21
  

  
  
  
  


• 
  
  
  

  
  3
  

  
  
  
  
  
  

  
  
  @MartinSmith, I get at SQL-Fiddle: Schema Creation Failed: User-defined functions, user-defined aggregates, CLR types, and methods on CLR types are not allowed in expressions in this context.:
  
  – ypercubeᵀᴹ
  Feb 6 '14 at 10:24
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @ypercube - Ah I hadn't bothered creating a dummy scalar UDF. Now I have I see the same thing.
  
  – Martin Smith
  Feb 6 '14 at 10:26
  

  
  
  
  

add a comment | 

I have a user defined table type where I want to make sure that a certain column is within a given range. This range however is defined in another table. So I thought I can use a check constraint on this column with a scalar valued function.

    CREATE TYPE [dbo].[Accounts] AS TABLE(

    [AccountName] [varchar](25) NOT NULL 

         CONSTRAINT CHK_AccountName CHECK (dbo.validateAccountName(AccountName) = 1),

    [Sales] [int] NOT NULL,

    [Returns] [int] NOT NULL

);

However I get a syntax error, when I do the same on a regular table it works. Based on MSFT documentation check constraints should be ok on table types. What am I missing here ? Any better approach to validate input on a table type ?

sql-server-2012 constraint usertypes

share|improve this question

asked Feb 6 '14 at 9:38

nojetlagnojetlag

1,30772333

    CREATE TYPE [dbo].[Accounts] AS TABLE(

    [AccountName] [varchar](25) NOT NULL 

         CONSTRAINT CHK_AccountName CHECK (dbo.validateAccountName(AccountName) = 1),

    [Sales] [int] NOT NULL,

    [Returns] [int] NOT NULL

);

share|improve this question

asked Feb 6 '14 at 9:38

nojetlagnojetlag

1,30772333

share|improve this question

asked Feb 6 '14 at 9:38

nojetlagnojetlag

1,30772333

asked Feb 6 '14 at 9:38

nojetlagnojetlag

1,30772333

asked Feb 6 '14 at 9:38

nojetlagnojetlag

1,30772333

1 Answer
1

active

oldest

votes

0

Try this solution from https://stackoverflow.com/questions/53330114/user-defined-table-type-with-check-constraint

CREATE TYPE [dbo].[Accounts] AS TABLE(

    [AccountName] [varchar](25) NOT NULL 

         CHECK (dbo.validateAccountName(AccountName) = 1),

    [Sales] [int] NOT NULL,

    [Returns] [int] NOT NULL

);

share|improve this answer

answered Feb 3 at 21:24

MuflixMuflix

3493517

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f58348%2fuser-defined-table-type-with-a-check-constraint%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

0

Try this solution from https://stackoverflow.com/questions/53330114/user-defined-table-type-with-check-constraint

CREATE TYPE [dbo].[Accounts] AS TABLE(

    [AccountName] [varchar](25) NOT NULL 

         CHECK (dbo.validateAccountName(AccountName) = 1),

    [Sales] [int] NOT NULL,

    [Returns] [int] NOT NULL

);

share|improve this answer

answered Feb 3 at 21:24

MuflixMuflix

3493517

add a comment | 

0

Try this solution from https://stackoverflow.com/questions/53330114/user-defined-table-type-with-check-constraint

CREATE TYPE [dbo].[Accounts] AS TABLE(

    [AccountName] [varchar](25) NOT NULL 

         CHECK (dbo.validateAccountName(AccountName) = 1),

    [Sales] [int] NOT NULL,

    [Returns] [int] NOT NULL

);

share|improve this answer

answered Feb 3 at 21:24

MuflixMuflix

3493517

add a comment | 

Try this solution from https://stackoverflow.com/questions/53330114/user-defined-table-type-with-check-constraint

CREATE TYPE [dbo].[Accounts] AS TABLE(

    [AccountName] [varchar](25) NOT NULL 

         CHECK (dbo.validateAccountName(AccountName) = 1),

    [Sales] [int] NOT NULL,

    [Returns] [int] NOT NULL

);

share|improve this answer

answered Feb 3 at 21:24

MuflixMuflix

3493517

CREATE TYPE [dbo].[Accounts] AS TABLE(

    [AccountName] [varchar](25) NOT NULL 

         CHECK (dbo.validateAccountName(AccountName) = 1),

    [Sales] [int] NOT NULL,

    [Returns] [int] NOT NULL

);

share|improve this answer

answered Feb 3 at 21:24

MuflixMuflix

3493517

answered Feb 3 at 21:24

MuflixMuflix

3493517

answered Feb 3 at 21:24

MuflixMuflix

3493517