Create table sales order using primary key

Question

s_order_no varchar2(6)PRIMARY KEY CHECKS(s_order_no LIKE('0%')
s_order_no DATE,
client_no varchar2(25),
Dely_add varchar2(6),
salesman_no varchar2(6)REFERENCES sale_master(sales_no),
Dely_type varchar2(1)CHECK(dely_type 1N('P','F')),
billed_yn varchar2(1),
dely_date DATE,
order_status varchar2(10)CHECK(order_status IN('IN PROCESS','BACK ORDER','CANCELLED));

Answer 1

Hi adeeba,

PRIMARY KEY's main objective is its uniqueness, a sales order has serialization by fiscal year, or fiscal period, so the way in which you create the table would not be adequate.

you can try:

CREATE TABLE GENERAL.dbo.salesOrder
(
id bigint identity(1,1) not null PRIMARY KEY,
s_order_no varchar(20) not null,
s_order_date date not null,
client_no varchar (25) not null,
Dely_add varchar(6) not null,
salesman_no varchar(6) not null,
Dely_type varchar(1) not null,
billed_yn varchar(1) not null,
dely_date date not null,
order_status varchar(10) not null,
[sys_created] datetime not null,
[sys_createdby] varchar(50) not null,
[sys_isactive] tinyint not null,
[sys_isblock] tinyint not null
)
GO
ALTER TABLE GENERAL.dbo.salesOrder ADD DEFAULT ('') FOR Dely_add
GO
ALTER TABLE GENERAL.dbo.salesOrder ADD CONSTRAINT chk_Dely_type CHECK (Dely_type in ('P','F'))
GO
ALTER TABLE GENERAL.dbo.salesOrder ADD CONSTRAINT chk_order_status CHECK (order_status in ('IN PROCESS','BACK ORDER','CANCELLED'))
GO
ALTER TABLE GENERAL.dbo.salesOrder ADD DEFAULT (getdate()) FOR [sys_created]
GO
ALTER TABLE GENERAL.dbo.salesOrder ADD DEFAULT HOST_NAME() FOR [sys_createdby]
GO
ALTER TABLE GENERAL.dbo.salesOrder ADD DEFAULT (1) FOR [sys_isactive]
GO
ALTER TABLE GENERAL.dbo.salesOrder ADD DEFAULT (0) FOR [sys_isblock]
GO