Skip to main content

Posts

Showing posts with the label not null

SQL insert query validation constraints to limit number of records

This is sample of query for  SQL insert query validation constraints to limit number of records lets you want to insert only 1000 records in table . We can restrict number of rows, but need to add a column on which we can add some constraint ... check below example.! CREATE TABLE TAB_1000_ROWS AS SELECT ROWNUM COL1 FROM DUAL CONNECT BY ROWNUM <1001; ALTER TABLE TAB_1000_ROWS ADD CONSTRAINT CHECK_CONST CHECK (COL1 BETWEEN 1 AND 1000); ALTER TABLE TAB_1000_ROWS MODIFY COL1 NUMBER NOT NULL; ALTER TABLE TAB_1000_ROWS ADD CONSTRAINT UNIQUE_COL1 UNIQUE (COL1); -- or we can also use sequence with max as 100 and no-cycle! after insertion of 100 values it wont allow any new values to get inserted! will throw check or unique constraint! 

Difference between primary key and unique key,Find Primary key,Index help,Not Null in SQL

Difference between primary key and unique key A table can have only one primary key but multiple unique keys. unique key may have null value which is not allowed in primary. simply primary key is unique + not null where as unique key Field contains any number of NULL values. Find primary key, index_name ,index key  using sp_Help command in sql How to find primary key, index_name and index_key in the same query For a particular table and also want to group by index_name use sysindexes table to get the output you want use the stored procedure sp_helpindex [object name] sp_help 'table name' Null and Not Null value in Primary and unique keys in sql Primary key should contain unique value and not null because if attribute value is null than how should maintain the uniqueness.. one more think i want to add that Primary key is a type of constraint and foreign key is also a type of constraint and pk is null then u can't use the null value as a foreign key. S

Auto Increment of column which is varchar type in sql

Set Auto Increment of column which is varchar type in sql If you want to Set Auto Increment of column which is varchar type in sql table then you can do that by using following way CREATE TABLE StudentTable ( Regid int IDENTITY(1,1) NOT NULL , StudentCol nvarchar(3) DEFAULT 'STD' , StudentId AS (StudentCol+ REPLICATE('0', 10-LEN(CONVERT(nvarchar(100),Regid )) ,OtherColumnsYouNeed nvarchar(MAX) )