CODEWITHSUNDEEP
sqlsqlitenvarchar
Tirafesi
Tirafesi

Reputation: 1479

NVARCHAR with variable length?

I want to create a table in SQL which has a string attr for Users of a website. How should I declare this attr, since names don't have a max length? Can I just leave the length empty and do it like this?

CREATE TABLE User(
id NUMBER PRIMARY KEY NOT NULL
name NVARCHAR
)

Also, do I need to say NOT NULL for PRIMARY KEYS, or are primary keys already never null?

Thanks in advance :)

Upvotes: 0

Views: 1991

Answers (2)

dan04
dan04

Reputation: 91015

How should I declare this attr, since names don't have a max length? Can I just leave the length empty and do it like this?

Yes, in SQLite you can just declare the column with any “text affinity” type name (CHAR, TEXT, CLOB, etc.) and it will store strings (or blobs) without length restriction (except for the global string length limit, which is 1 GB by default).

If you do want to constrain the length of strings, you can do it with an explicit CHECK constraint, like CHECK(LENGTH(name) <= 100).

Also, do I need to say NOT NULL for PRIMARY KEYS, or are primary keys already never null?

Per the SQL standard, PRIMARY KEY implies NOT NULL. However, SQLite has a long-standing bug that makes the explicit NOT NULL constraint required.

According to the SQL standard, PRIMARY KEY should always imply NOT NULL. Unfortunately, due to a bug in some early versions, this is not the case in SQLite. Unless the column is an INTEGER PRIMARY KEY or the table is a WITHOUT ROWID table or the column is declared NOT NULL, SQLite allows NULL values in a PRIMARY KEY column. SQLite could be fixed to conform to the standard, but doing so might break legacy applications. Hence, it has been decided to merely document the fact that SQLite allowing NULLs in most PRIMARY KEY columns.

Upvotes: 1

Mudassir Hasan
Mudassir Hasan

Reputation: 28751

You should declare length as MAX and primary key are automatically NOT NULL

CREATE TABLE User(
id NUMBER PRIMARY KEY 
name NVARCHAR(MAX)
)

Since you have later specified dbms as SQL Lite , the documentation says

What is the maximum size of a VARCHAR in SQLite?

SQLite does not enforce the length of a VARCHAR. You can declare a VARCHAR(10) and SQLite will be happy to let you put 500 characters in it. And it will keep all 500 characters intact - it never truncates.

Upvotes: 1

Related Questions